SQL Topic - Unions, Intersections & Exceptions

#SQL #Edu

When working with multiple tables, the UNION and UNION ALL operator allows you to append the results of one query to another assuming that they have the same column count, order and data type. If you use the UNION without the ALL, duplicate rows between the tables will be removed from the result.

Select query with set operators

SELECT column, another_column FROM mytable **UNION / UNION ALL / INTERSECT / EXCEPT SELECT other_column, yet_another_column FROM another_table** ORDER BY column DESC LIMIT _n_;

In the order of operations as defined in Lesson 12: Order of execution, the UNION happens before the ORDER BY and LIMIT. It's not common to use UNIONs, but if you have data in different tables that can't be joined and processed, it can be an alternative to making multiple queries on the database.

Similar to the UNION, the INTERSECT operator will ensure that only rows that are identical in both result sets are returned, and the EXCEPT operator will ensure that only rows in the first result set that aren't in the second are returned. This means that the EXCEPT operator is query order-sensitive, like the LEFT JOIN and RIGHT JOIN.

Both INTERSECT and EXCEPT also discard duplicate rows after their respective operations, though some databases also support INTERSECT ALL and EXCEPT ALL to allow duplicates to be retained and returned. ALL command retains the duplicates.

The current SQLBolt page explains set operations in SQL, which let you combine results from multiple queries. The main operators covered are:

UNION / UNION ALL: Combine results from two queries if their columns match. UNION removes duplicates; UNION ALL keeps all rows, even duplicates.
INTERSECT: Returns only rows present in both queries’ results and removes duplicates.
EXCEPT: Returns rows from the first query that aren’t in the second. It’s sensitive to the order of queries, similar to LEFT and RIGHT JOIN.
Both INTERSECT and EXCEPT can discard duplicates, but some databases support INTERSECT ALL and EXCEPT ALL to retain duplicates.

These operations happen before ORDER BY and LIMIT when executing SQL queries. They are useful when you can't join data and need combined outputs from separate tables.

is intersect similar to inner join?

INTERSECT and INNER JOIN are similar in that both give you results that are common between tables, but they work differently and have different use cases:

Feature	INTERSECT	INNER JOIN
What it does	Returns rows that are exactly present in both queries or tables (the overlapping set)	Combines columns from two tables based on a matching condition (like a shared ID)
How it matches	Looks for rows that are identical in every column specified	Joins rows where specified columns from both tables match
Columns output	Must have the same number and type of columns in both queries	Can select any columns from both joined tables
Syntax example	`SELECT column FROM A INTERSECT SELECT column FROM B;`	`SELECT * FROM A INNER JOIN B ON A.id = B.id;`
Use case	Find rows that exist in both result sets, with the exact same values	Combine related data from two tables, usually to show columns from both

Summary:

INTERSECT is about finding identical full rows in two queries.
INNER JOIN is about combining information from two tables where there’s a match on a specific column (not necessarily an identical full row).

They both relate to “commonality,” but INNER JOIN is more flexible and widely used to link related data, while INTERSECT is for finding completely matching rows in two sets.