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.



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