INTERSECT and EXCEPT commands in MySQL

MySQL now supports the INTERSECT and EXCEPT set operators. Set operators work on the results of multiple SELECT statements. In this post, we will see example queries using INTERSECT and EXCEPT commands for a better understanding

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

While MySQL has supported UNION and UNION ALL commands, there were no INTERSECT or EXCEPT commands in the language. This often involved work-around queries. However, MySQL now does support the INTERCEPT and EXCEPT set operators.

For the example queries, I am using 2 simple tables with arbitrary data.

The names table:

And the more_names table:

MySQL INTERSECT

INTERSECT returns those rows that are common across all of the involved SELECT statements.

SELECT * FROM more_names
INTERSECT
SELECT * FROM names;

MySQL EXCEPT

EXCEPT returns only those rows from the first SELECT statement that are not found in the 2nd SELECT statement:

SELECT * FROM more_names
EXCEPT
SELECT * FROM names;

Therefore, it is entirely possible that you are returned different results based on what SELECT query is first in the EXCEPT statement:

SELECT * FROM names
EXCEPT
SELECT * FROM more_names;

Visit the official MySQL INTERSECT and EXCEPT documentation for more information.

Thank you for reading this post. Please share it with someone else who would enjoy it as well.




Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.

Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.

More ways I can help

Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.


πŸ“° Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!


Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.