# Combining with UNION

### Combining with UNION

If two table expressions are combined with the UNION operator, the end result consists of every row that appears in the result of one of the two table expressions or in both. UNION is the equivalent of the operator union from set theory.

##### 1. Get the player number and the town of each player from Inglewood and Plymouth.

 ```SELECT PLAYERNO, TOWN FROM PLAYERS WHERE TOWN = 'Inglewood' UNION SELECT PLAYERNO, TOWN FROM PLAYERS WHERE TOWN = 'Plymouth' ``` The result is: ```PLAYERNO TOWN -------- --------- 8 Inglewood 44 Inglewood 112 Plymouth ```

Explanation: Each of the two table expressions returns a table consisting of two columns and zero or more rows. As mentioned, the UNION operator puts the two tables underneath each other. The end result of the entire statement is one table.

Note

The previous statement could, of course, also have been formulated using an OR operator:

```SELECT  PLAYERNO, TOWN
FROM    PLAYERS
WHERE   TOWN = 'Inglewood'
OR      TOWN = 'Plymouth'
```

However, it is not always possible to replace the UNION operator with an OR operator. Here is an example.

##### Figure. Get a list of all the dates that appear in the PLAYERS and the PENALTIES table.

 ```SELECT BIRTH_DATE AS DATES FROM PLAYERS UNION SELECT PAYMENT_DATE FROM PENALTIES ``` The result is: ```DATES ---------- 1948-09-01 1956-10-29 1956-11-11 1962-07-08 1963-01-09 1963-02-28 1963-05-11 1963-05-14 1963-06-22 1963-10-01 1964-06-25 1964-12-28 1970-05-10 1971-08-17 1980-12-08 1981-05-05 1982-12-30 1983-09-10 1984-11-12 1984-12-08 ``` This statement cannot be formulated with OR because rows from different tables are combined and are not, as in the previous example, from the same table. A special property of the UNION operator is that all duplicate (or equal) rows are removed automatically from the end result. Section 9.5, in Chapter 9, "SELECTStatement:SELECTClause and Aggregation Functions," describes the rule for the equality of two rows with regard to DISTINCT in the SELECT clause. The same rule also applies, of course, to the UNION operator.

##### 3. Get the number of each player who has incurred at least one penalty, or who is a captain, or for whom both conditions apply.

 ```SELECT PLAYERNO FROM PENALTIES UNION SELECT PLAYERNO FROM TEAMS ``` The result is: ```PLAYERNO -------- 6 8 27 44 104 ```

Explanation: The result obviously shows that all the duplicate rows have been deleted.

You can combine more than two table expressions into one table expression. The following is an example.

##### 4. Get the player number of each player who has incurred at least one penalty, who is a captain, who lives in Stratford, or for whom two or three of these conditions apply.

 ```SELECT PLAYERNO FROM PENALTIES UNION SELECT PLAYERNO FROM TEAMS UNION SELECT PLAYERNO FROM PLAYERS WHERE TOWN = 'Stratford' ``` The result is: ```PLAYERNO -------- 2 6 7 8 27 39 44 57 83 100 104 ```

 Exercise 13.1: Get a list of numbers of the players who have ever been committee members, plus the numbers of the players who have incurred at least two penalties. Exercise 13.2: Determine what the most recent date is: the most recent date of birth or the most recent date on which a penalty has been paid.