SQL SET Operator

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns

• The columns must also have similar data types

• The columns in each SELECT statement must also be in the same order

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION.

Below is a selection from the "Customers" table:

And a selection from the "Suppliers" table:

SQL UNION Example

The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL UNION ALL Example

The following SQL statement returns the cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION With WHERE

The following SQL statement returns the German cities (only distinct values) from both the "Customers" and the "Suppliers" table:

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement returns the German cities (duplicate values also) from both the "Customers" and the "Suppliers" table:

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Another UNION Example

The following SQL statement lists all customers and suppliers:

SELECT 'Customer' As Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

INTERSECT

The SQL Server (Transact-SQL) INTERSECT operator is used to return the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. It is the intersection of the two SELECT statements.

Syntax

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Explanation: The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2. Each SELECT statement within the SQL Server INTERSECT must have the same number of columns in the result sets with similar data types.

INTERSECT Example

SELECT product_id
FROM products
INTERSECT
SELECT product_id
FROM inventory;

INTERSECT Example with WHERE

SELECT product_id
FROM products
WHERE product_id >= 50
INTERSECT
SELECT product_id
FROM inventory
WHERE quantity > 0;

EXCEPT

The SQL Server (Transact-SQL) EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

Syntax

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
EXCEPT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Explanation: The EXCEPT query will return the records in the blue shaded area. These are the records that exist in Dataset1 and not in Dataset2. Each SELECT statement within the EXCEPT query must have the same number of fields in the result sets with similar data types.

EXCEPT Example

SELECT product_id
FROM products
EXCEPT
SELECT product_id
FROM inventory;

EXCEPT Example with WHERE

SELECT contact_id, last_name, first_name
FROM contacts
WHERE last_name = 'Anderson'
EXCEPT
SELECT employee_id, last_name, first_name
FROM employees;