34  SQL SET Operations

set-operations-union-intersect-except

34.1 UNION

  • Combines different (DISTINCT) results from multiple SELECT commands.
  • UNION remove duplicate records from the query result, so performance is affected.

See below example:

SELECT c.FirstName FROM Customer c

59 rows

We have 59 rows in our Customer table.

SELECT e.FirstName FROM Employee e

8 rows

We have 8 rows in our Employee table.

SELECT c.FirstName FROM Customer c
UNION
SELECT e.FirstName FROM Employee e

63 rows

But when we combine all of the first names using UNION we get 63 rows not 59+8 = 67 rows. UNION set operator remove duplicates from the result. Here we have 4 duplicate first names.

  • UNION ALL works faster because it does not deal with duplicate records.
  • UNION ALL should be used wherever possible for performance reasons.
SELECT c.FirstName FROM Customer c
UNION ALL
SELECT e.FirstName FROM Employee e

67 rows

When we use UNION ALL, we get 59+8 = 67 rows. If we order the result set using a subquery we could see duplicates more clearly. In the below query, we are using from subquery.

SELECT * FROM
(
SELECT c.FirstName FROM Customer c
UNION ALL
SELECT e.FirstName FROM Employee e
)
ORDER BY FirstName

union all duplicate results

  • Column aliases should be written in the first query.

  • Column numbers must be the same in all queries that are combined. If we run below query, we get an error.

SELECT c.FirstName,c.LastName FROM Customer c
UNION ALL
SELECT e.FirstName FROM Employee e
SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (SELECTs to the left and right of UNION ALL do not have the same number of result columns)
  • Column data structures should be either implicitly translated or explicitly translated (Explicit Conversion).
  • For example SELECT cast( GETDATE() as varchar(12))

This is less a problem in sqlite due to its dynamic data type handling.

But in oracle and sqlserver, your queries may return error if data could not be implicitly converted.

34.2 EXCEPT

Second result set is removed from first result set. EXCEPT key word is SQL standard.

SELECT c.FirstName FROM Customer c
EXCEPT
SELECT e.FirstName FROM Employee e

55 rows

SELECT e.FirstName FROM Employee e
EXCEPT
SELECT c.FirstName FROM Customer c

6 rows.

34.3 INTERSECT

Intersection of the both result sets are the output.

SELECT c.FirstName FROM Customer c
INTERSECT
SELECT e.FirstName FROM Employee e

2 rows

That is only 2 first names are same in Customer and Employee tables.

34.3.1 Example questions for Intersect

34.3.1.1 Common column names in two different tables

Consider following two tables, Employee and Customer. Write a query that will find which names are common in Employee and Customer tables.

erDiagram
    Employee {
        int employee_id
        text employee_name
    }
    Customer {
        int customer_id
        text customer_name
    }

This question could also be solved by using inner join but set operation intersect is more easier to understand. Following query will give use

    SELECT employee_name FROM Employee;
    INTERSECT
    SELECT customer_name FROM Customer;

chinook example

If we run similar example for chinook database, we would run following query.

SELECT c.FirstName from Customer c
INTERSECT
SELECT e.FirstName from Employee e

results will be two names below.

Robert
Steve

34.4 Oracle MINUS

Except is SQL standard but oracle also accept MINUS keyword. It is functionality is the same. Oracle also accepts ALL to EXCEPT ALL and MINUS ALL so that duplicated will not be removed, same as UNION ALL. See following Oracle tutorial Set operations

34.5 Other tutorials