⏬Set Operations

UNION and UNION ALL keywords

UNION

The UNION keyword combines the result sets of two or more SELECT statements, removing duplicate rows.

SELECT columns FROM table1 
UNION 
SELECT columns FROM table2;

• The column names and data types must match in each SELECT statement.

• Duplicate rows are removed.

• Default ORDER BY sorting is applied unless overridden.

UNION ALL

The UNION ALL keyword also combines multiple SELECT statements but does NOT remove duplicate rows.

SELECT columns FROM table1
UNION ALL 
SELECT columns FROM table2; 

• Duplicate rows are included in the result set.

• Useful when you want to retain duplicates, like combining totals from multiple tables.

Key Differences

• UNION removes duplicates, UNION ALL keeps duplicates.

• UNION sorts by default, UNION ALL keeps original ordering.

• UNION is slower as it performs additional processing to filter duplicates.

Uses

• Combine similar data from related tables.

• Append totals or aggregates from various tables.

• Merge results from stored procedures or queries into one.

So in summary, UNION and UNION ALL combine multiple SELECT statements but control duplicate rows and sorting differently. They allow you to unite results into one output.

INTERSECT

The INTERSECT operator returns only the common rows between two SELECT statements.

SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2; 

This returns only rows found in both result sets.

EXCEPT

The EXCEPT operator returns distinct rows from the first SELECT statement that are not in the second.

SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2;

This returns only rows from table1 and not table2.