Set operations combine the results of two or more queries into a single result Set. The queries must have compatible column structures (same number of columns with compatible data types).

Union

The UNION operator combines the results of two queries, returning all unique rows from both result sets. This corresponds to the Union operation in set theory.

SELECT first_name, last_name FROM person_table_1
UNION
SELECT first_name, last_name FROM person_table_2;

Returns all unique names from both tables.

Use UNION ALL to include duplicate rows:

SELECT first_name, last_name FROM person_table_1
UNION ALL
SELECT first_name, last_name FROM person_table_2;

Returns all names from both tables, including duplicates.

Intersection

The INTERSECT operator returns only the rows that appear in both query results. This corresponds to the Intersection operation in set theory.

SELECT first_name, last_name FROM person_table_1
INTERSECT
SELECT first_name, last_name FROM person_table_2;

Returns names that appear in both tables.

Difference

The EXCEPT operator (or MINUS in some databases) returns rows from the first query that do not appear in the second query. This corresponds to the Set Difference operation in set theory.

SELECT first_name, last_name FROM person_table_1
EXCEPT
SELECT first_name, last_name FROM person_table_2;

Returns names that are in the first table but not in the second.