Set Operators in PostgreSQL

Set Operators:

PostgreSQL supports three primary set operators to combine the results of two or more queries into a single result set: UNION, INTERSECT, and EXCEPT.

Rules for All Set Operators:

• They must return the same number of columns.
• Corresponding columns must have compatible data types (PostgreSQL will not implicitly convert mismatched types like INT and TEXT).
• Column names in the final output are determined by the first query.

Table 1 Teams

Table 2 Player

1. UNION:

The UNION operator merges rows from both queries and removes duplicate rows.

select player_id from teams
union
select player_id from player;

Output:

2. UNION ALL:

• The UNION ALL operator merges rows from both queries but keeps all duplicates.
• It performs faster than UNION because PostgreSQL doesn't need to sort the data to filter out duplicates.

select player_id from teams
union all
select player_id from player;

Output:

3. INTERSECT:

• The INTERSECT operator returns only the rows that are present in both query results.
• It automatically filters out duplicate values unless INTERSECT ALL is specified.

select player_id from teams
intersect
select player_id from player;

Output:

4. EXCEPT:

• The EXCEPT operator returns rows from the first query that do not exist in the second query.
• It removes duplicates from the first result set before checking against the second unless EXCEPT ALL is used.

select player_id from player
except 
select player_id from teams;

Output: