Combining with INTERSECT






Combining with INTERSECT

This section describes another set operator, the INTERSECT. If two table expressions are combined with the INTERSECT operator, the end result consists of those rows that appear in the results of both table expressions. INTERSECT is the equivalent of the intersection operator from set theory. Just as with the UNION operator, duplicate rows are automatically removed from the result.

Portability

Several SQL products, including MySQL, do not support the INTERSECT operator.


6. Get the player number and the date of birth of each player who is living in Stratford and who was born after 1960.

SELECT   PLAYERNO, BIRTH_DATE
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
INTERSECT
SELECT   PLAYERNO, BIRTH_DATE
FROM     PLAYERS
WHERE    BIRTH_DATE > '1960-12-31'
ORDER BY 1

The result is:

PLAYERNO  BIRTH_DATE
--------  ----------
       6  1964-06-25
       7  1963-05-11
      57  1971-08-17
     100  1963-02-28

Explanation: Both table expressions produce a table with two columns and zero or more rows. The INTERSECT operator looks for the rows that appear in the results of both table expressions. The end result of the entire statement is one table.

For the use of the INTERSECT operator, the same rules hold as for the UNION operator; see Section 13.3. The SELECT clauses have to be union compatible, an ORDER BY can be specified only behind the last table expression, and DISTINCT is superfluous.

The previous statement could, of course, have been formulated with the AND operator:

SELECT   PLAYERNO, BIRTH_DATE
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
AND      BIRTH_DATE > '1960-12-31'
ORDER BY 1

However, it is not always possible to substitute the INTERSECT operator for the AND operator; see the following example.

7. Get the player number of each player who is a captain and who has incurred at least one penalty.

SELECT   PLAYERNO
FROM     TEAMS
INTERSECT
SELECT   PLAYERNO
FROM     PENALTIES

The result is:

PLAYERNO
--------
       6
      27

All set operators, including the INTERSECT operator, can be used within subqueries.

8. Get the player number and name of each player who is a captain and who incurred at least one penalty.

SELECT   PLAYERNO, NAME
FROM     PLAYERS
WHERE    PLAYERNO IN
        (SELECT  PLAYERNO
         FROM     TEAMS
         INTERSECT
         SELECT   PLAYERNO
         FROM     PENALTIES)

The result is:

PLAYERNO  NAME
--------  ---------
       6  Parmenter
      27  Collins

9. Get the numbers less than 5000 that represent an integer to the power of two and three.

SELECT   POWER(NUMBER,2) AS POWERS
FROM    (SELECT    CAST(DIGIT1. DIGIT || DIGIT2. DIGIT
                   AS UNSIGNED INTEGER) AS NUMBER
         FROM     (SELECT '0' DIGIT UNION SELECT '1' UNION
                   SELECT '2' UNION SELECT '3' UNION
                   SELECT '4' UNION SELECT '5' UNION
                   SELECT '6' UNION SELECT '7' UNION
                   SELECT '8' UNION SELECT '9') AS DIGIT1,
                  (SELECT '0' DIGIT UNION SELECT '1' UNION
                   SELECT '2' UNION SELECT '3' UNION
                   SELECT '4' UNION SELECT '5' UNION
                   SELECT '6' UNION SELECT '7' UNION
                   SELECT '8' UNION SELECT '9') AS DIGIT2)
                   AS NUMBERS
WHERE    POWER(NUMBER,2) < 5000
INTERSECT
SELECT   POWER(NUMBER,3) AS POWERS
FROM    (SELECT    CAST(DIGIT1.DIGIT || DIGIT2.DIGIT
                   AS UNSIGNED INTEGER) AS NUMBER
         FROM     (SELECT '0' DIGIT UNION SELECT '1' UNION
                   SELECT '2' UNION SELECT '3' UNION
                   SELECT '4' UNION SELECT '5' UNION
                   SELECT '6' UNION SELECT '7' UNION
                   SELECT '8' UNION SELECT '9') AS DIGIT1,
                  (SELECT '0' DIGIT UNION SELECT '1' UNION
                   SELECT '2' UNION SELECT '3' UNION
                   SELECT '4' UNION SELECT '5' UNION
                   SELECT '6' UNION SELECT '7' UNION
                   SELECT '8' UNION SELECT '9') AS DIGIT2)
                   AS NUMBERS
WHERE    POWER(NUMBER,3) < 5000

The result is:

POWERS
------
     0
     1
    64
   729
  4096

Explanation This result is based upon the SELECT statement in Figure, in Chapter 7, "SELECTStatement: TheFROMClause."

Exercise 13.5:

Get the numbers of the players who have once been committee members and who have incurred at least two penalties.

Exercise 13.6:

Get the number of players who have ever been committee members and for whom at least two penalties have been incurred.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows