Union Operators in MySQL and PostgreSQL 🔔

Joins and Union operators are used heavily in Structured Query Language (SQL) for querying the data in relational databases such MySQL and PostgreSQL.

In this article, I will discuss Union Operators such as UNION, UNION ALL, EXCEPT, INTERSECT.

🔔 Agenda

1. Using UNION
2. Using UNION ALL
3. Using EXCEPT
4. Using INTERSECT Operator
5. Understanding Order of Evaluation

Let’s understand each of them.

🔔Using UNION

UNION is typically used to combine results from multiple tables (2 or more).

In MySQL and PostgreSQL, the UNION keyword to combine the results of two or more SELECT statements.

It is an intelligent operator as the output of the UNION doesn’t contain any Duplicate rows. It identifies duplicate rows and then removes them before returning the final output of the query.

Can UNION be used anytime? NO.

UNION can be used only if the following conditions are met in each SELECT statements —

• Each SELECT statement should have the same number of columns
• Each SELECT statement should have the same number of column expressions (same order and same applied functions such as aggregation)
• Each column should have the same data type and should have the same order

Note: Column names don’t have to be same.

UNION adds more rows to existing rows. That’s why the column number, order, expression and data type must “match”. It can also be used to combine two or more SELECT statements querying the same table or different tables.

However, if one is combining results from a single table, it is better to write a single query using the appropriate WHERE clause or CASE statement, etc.

Syntax of Join and Union

UNION Syntax in MySQL (Image by Author)

Other clauses such as WHERE, JOIN, etc.. can also be used with UNION .

However, only one ORDER BY clause is allowed for the whole query, and it should be at the very end.

What is the difference in Join and Union? The difference between a JOIN and a UNION is that JOIN links together multiple tables within a single query, whereas UNION stacks the results of multiple queries.

JOIN vs. UNION. (Reference)

🔔Using UNION ALL

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

If you know with certainty that no duplicate rows are possible, use UNION ALL to improve performance as it is faster than UNION .

Why UNION is slower? Because, UNION does an additional sort behind the scenes to identify the duplicates while UNION ALL doesn’t perform this sorting in the backend.

UNION ALL Syntax in MySQL (Image by Author)

Other clauses such as WHERE, JOIN, etc.. can also be used with UNION ALL.

However, only one ORDER BY clause is allowed for the whole query, and it should be at the very end.

🔔Using ‘EXCEPT’

In addition to using a UNION to combine the rows of multiple tables, one can use EXCEPT and INTERSECT to combine the rows in different ways.

Use EXCEPT to “subtract” the results of one query from another query.

Select all the company from Asia except company from China. Syntax in MySQL (Image by Author).

MySQL does not support EXCEPT. Instead, one can use the NOT IN keywords as a workaround.

PostgreSQL also supports EXCEPT ALL, which does not remove duplicates. EXCEPT removes all occurrences of a value, while EXCEPT ALL removes specific instances.

🔔Using INTERSECT

Use INTERSECT to find the rows in common between two queries.

MySQL does not support INTERSECT. Instead, you can use an INNER JOIN as a workaround.

INTERSECT is used to find common rows in between Asia and China. Syntax in MySQL (Image By Author).

PostgreSQL also supports INTERSECT ALL, which preserves duplicate values.

🔔Understanding Order of Evaluation

Unless otherwise specified, union operators are performed in top-down order, except that INTERSECT takes precedence over UNION and EXCEPT.

When writing a statement with multiple union operators (UNION, EXCEPT, INTERSECT), use parentheses to specify the order in which the operations should occur.

SELECT * FROM staff
EXCEPT
(SELECT * FROM residents
UNION
SELECT * FROM pets);

Thank you for reading this article.