Duplicate Rows in a Combined SELECT

If you use the UNION operator alone, the duplicate rows are removed from the complete set of rows. That is, if multiple rows contain identical values in each column, only one row is retained. If you use the UNION ALL operator, all the selected rows are returned (the duplicates are not removed).

The next example uses UNION ALL to join two SELECT statements without removing duplicates. The query returns a list of all the calls that were received during the first quarter of 1997 and the first quarter of 1998.

SELECT customer_num, call_code FROM cust_calls
   WHERE call_dtime BETWEEN 
         DATETIME (1997-1-1) YEAR TO DAY
      AND DATETIME (1997-3-31) YEAR TO DAY
UNION ALL
SELECT customer_num, call_code FROM cust_calls
   WHERE call_dtime BETWEEN 
         DATETIME (1998-1-1)YEAR TO DAY
      AND DATETIME (1998-3-31) YEAR TO DAY

If you want to remove duplicates, use the UNION operator without the keyword ALL in the query. In the preceding example, if the combination 101 B were returned in both SELECT statements, a UNION operator would cause the combination to be listed once. (If you want to remove duplicates within each SELECT statement, use the DISTINCT keyword in the Projection clause, as described in Projection Clause.)