# 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.

• Интернет магазин купить беговую дорожку www.plazasport.ru.