SET Operations

 

UNION (Removes Duplicates)

Combines results from both queries but removes duplicates.

Sorts results by default

Use UNION if you want distinct results.

SELECT employee_id, department FROM employees

UNION

SELECT manager_id, department FROM managers;

UNION ALL (Keeps Duplicates)

Similar to UNION but does NOT remove duplicates.

Faster than UNION

SELECT employee_id, department FROM employees

UNION ALL

SELECT manager_id, department FROM managers;

INTERSECT (Common Rows)

Returns only the common rows between both queries.

Returns only matching records
Removes duplicates

SELECT employee_id FROM employees

INTERSECT

SELECT employee_id FROM project_team;

MINUS (Difference of Two Queries)

Returns rows from the first query that are NOT in the second.

e.g. Returns employees NOT in the project team

SELECT employee_id FROM employees

MINUS

SELECT employee_id FROM project_team;

 

 

 

 

Symmentric Difference :

All rows that are unique to either of the two queries

It  excludes common rows from both queries.

Union minus  intersection

(

    SELECT employee_id FROM employees

    UNION ALL

    SELECT employee_id FROM project_team

)

MINUS

(

    SELECT employee_id FROM employees

    INTERSECT

    SELECT employee_id FROM project_team

);