SQL SET keyword

Set operations

Set operations combine rows from the result sets of multiple SELECT statements. There are numerous set operators, and all provide different ways to do it. In SQL, these are UNION, UNION ALL, INTERSECT, and EXCEPT or MINUS.

These set operators can only be used if all SELECT statements have the same number of columns, all the corresponding columns have similar data types, and all columns are selected in the same order.

UNION

The UNION operator merges the results of two or more SELECT statements and eliminates all duplicate rows.

Let's consider two tables: teachers and administrative_staff.

Table teachers has columns name VARCHAR(40) and subject VARCHAR(30), and looks as follows:

Table administrative_staff has columns position VARCHAR(40) and name VARCHAR(40):

To select all school employees from these two tables with two queries combined with the UNION operator: 

SSELECT name FROM teachers 
UNION 
SELECT name FROM administrative_staff

The result of this query looks like this:

UNION ALL

The UNION ALL operator does almost the same thing as UNION but keeps all the duplicates.

The query below will select all teachers and administrative staff names, including all duplicates if there are any.

SELECT name FROM teachers 
UNION ALL 
SELECT name FROM administrative_staff

In the present case, the query result looks as follows:

‍

INTERSECT

The INTERSECT operator returns the common records for all component queries without the duplicates.

Use INTERSECT to select all teachers who have an administrative position:

SELECT name FROM teachers 
INTERSECT 
SELECT name FROM administrative_staff

The result set of the query above will have only one row:

Some SQL dialects, such as MySQL, do not support the INTERSECT operator.

MINUS or EXCEPT

The EXCEPT operator returns only those rows that belong to the first result set but not to the second one. It omits all the duplicates.

To select all the teachers who don't have an additional administrative position:

SELECT name FROM teachers 
EXCEPT 
SELECT name FROM administrative_staff

At present, the EXCEPT is used as an operator, but in some SQL dialects, the EXCEPT operator is called the MINUS operator, so it is possible to make the same query by replacing EXCEPT with MINUS.

The result is this:

In queries using UNION, UNION ALL, and INTERSECT, the order of query components does not affect the result.

If we change the order of the component queries in a query with EXCEPT or MINUS, we also change the meaning of the query and the result set. In case of more than two queries, parentheses can change the order in which operators must be executed.

The query below will return all the administrative workers who don't work as teachers:

SELECT name FROM administrative_staff 
EXCEPT 
SELECT name FROM teachers

The result of the query above will have only two rows:

General template for a query with set operators

SELECT 
    column_1_1, 
    column_1_2,
    ..., 
    column_1_n 
FROM 
     table_1 
[WHERE logical_expression]
SET_OPERATOR
SELECT 
    column_2_1,
    column_2_2,
    ...,
    column_2_n  
FROM table_2 
[WHERE logical_expression]
[SET_OPERATOR
...]

All SQL dialect support UNION and UNION ALL operators, but INTERSECT, MINUS, or EXCEPT may not be available.