Back to SQL Course - Back to 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.
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;
-- It will uniquely extract the country descriptions
SELECT birth_country FROM employee
UNION
SELECT description FROM countries;
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;
-- It will extract the country descriptions providing duplicates
SELECT birth_country FROM employee
UNION ALL
SELECT description FROM countries;
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;
-- 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;
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;
-- 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.