Union Operators in MySQL and PostgreSQL 🔔

🔔 Agenda

🔔Using UNION

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

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.

UNION Syntax in MySQL (Image by Author)
JOIN vs. UNION. (Reference)

🔔Using UNION ALL

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

UNION ALL Syntax in MySQL (Image by Author)

🔔Using ‘EXCEPT’

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

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.

SELECT * FROM staffEXCEPT(SELECT * FROM residents
UNION
SELECT * FROM pets);

In fact, in the next 3-months, I will be adding 15+ new articles covering multiple use-cases from data analytics and ML/AI. 🍾 🙌 🎉

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store