sql-unions-clause

SQL - Set Operators (UNION, INTERSECT, EXCEPT)

SQL set operators are used to combine the result-sets of two or more SELECT statements. Unlike JOINs, which combine columns from different tables horizontally, set operators combine rows vertically. They are used to merge, compare, or subtract result-sets.

To use set operators, the SELECT statements must have:

• The same number of columns.
• The columns must have compatible data types in the same order.

Example Setup

Let’s consider two tables: WebsiteA_Users and WebsiteB_Users.

WebsiteA_Users:

+--------------------+
| Email              |
+--------------------+
| alice@example.com  |
| bob@example.com    |
| charlie@example.com|
+--------------------+

WebsiteB_Users:

+--------------------+
| Email              |
+--------------------+
| charlie@example.com|
| david@example.com  |
| eve@example.com    |
+--------------------+

The UNION Operator

The UNION operator combines the result-sets of two or more SELECT statements and removes duplicate rows.

Syntax

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

Example

To get a single list of all unique user emails from both websites:

SELECT Email FROM WebsiteA_Users
UNION
SELECT Email FROM WebsiteB_Users;

Result (notice ‘charlie@example.com’ appears only once):

+--------------------+
| Email              |
+--------------------+
| alice@example.com  |
| bob@example.com    |
| charlie@example.com|
| david@example.com  |
| eve@example.com    |
+--------------------+

The UNION ALL Operator

The UNION ALL operator combines the result-sets but includes all duplicate rows.

It is faster than UNION because it doesn’t need to check for and remove duplicates. Use it when you know there are no duplicates or when you explicitly want to include them.

SELECT Email FROM WebsiteA_Users
UNION ALL
SELECT Email FROM WebsiteB_Users;

Result (notice ‘charlie@example.com’ now appears twice):

+--------------------+
| Email              |
+--------------------+
| alice@example.com  |
| bob@example.com    |
| charlie@example.com|
| charlie@example.com|
| david@example.com  |
| eve@example.com    |
+--------------------+

The INTERSECT Operator

The INTERSECT operator returns only the rows that appear in both result-sets.

SELECT Email FROM WebsiteA_Users
INTERSECT
SELECT Email FROM WebsiteB_Users;

Result (only ‘charlie’ is in both tables):

+--------------------+
| Email              |
+--------------------+
| charlie@example.com|
+--------------------+

The EXCEPT Operator

The EXCEPT operator returns rows from the first SELECT statement that do not appear in the second SELECT statement. (Note: Oracle uses the keyword MINUS for this).

SELECT Email FROM WebsiteA_Users
EXCEPT
SELECT Email FROM WebsiteB_Users;

Result (users who are in Website A but not in Website B):

+-------------------+
| Email             |
+-------------------+
| alice@example.com |
| bob@example.com   |
+-------------------+