Manuel Gentile

Back to SQL Course - Back to Operators

Set Operators

Combines similar type of data from tables mixing the result of queries and returning a single result.

Its operators are UNION, UNION ALL, MINUS, INTERSECT.

Union

The UNION operator combines the result of two or more tables in a single result.

The number of columns and their data types must be the same and in the same order for each SELECT statement.

It doesn’t show duplicated rows.

SELECT COLUMN1, …, COLUMNn FROM TABLE1
UNION
SELECT COLUMN1, …, COLUMNn FROM TABLE2;

Union Example

-- It will uniquely extract the country descriptions
SELECT birth_country FROM employee
UNION
SELECT description FROM countries;

Union All

The UNION ALL operator acts like the UNION operator with the only difference that shows duplicated rows.

SELECT COLUMN1, …, COLUMNn FROM TABLE1
UNION ALL
SELECT COLUMN1, …, COLUMNn FROM TABLE2;

Union All Example

-- It will extract the country descriptions providing duplicates
SELECT birth_country FROM employee
UNION ALL
SELECT description FROM countries;

Minus / Except

The MINUS (or EXCEPT) operator combines the result of two SELECT statements, simply subtracting the second statement from the first one showing unique records.

SELECT COLUMN1, …, COLUMNn FROM TABLE1
MINUS
SELECT COLUMN1, …, COLUMNn FROM TABLE2;

Minus Example

-- It will extract the employees' country descriptions that aren't present in the countries table
SELECT birth_country FROM employee
MINUS
SELECT description FROM countries;

Intersect

The INTERSECT operator combines the result of two SELECT statements in a single result matching the common records.

The number of columns and their data types must be the same and in the same order for each SELECT statement.

It doesn’t show duplicated rows.

SELECT COLUMN1, …, COLUMNn FROM TABLE1
INTERSECT
SELECT COLUMN1, …, COLUMNn FROM TABLE2;

Intersect Example

-- It will extract the employees' country descriptions that are present in the countries table
SELECT birth_country FROM employee
INTERSECT
SELECT description FROM countries;

Back to SQL Course - Back to Operators

Let’s connect

If you want to learn more about the topic, connect or send me a DM.

Website