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 > '19601231'
ORDER BY 1
The result is:
PLAYERNO BIRTH_DATE
 
6 19640625
7 19630511
57 19710817
100 19630228

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 > '19601231'
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:
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. 
