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
- Starting a blog? I use WordPress for the Digital Owl’s Prose blog. Let’s both save money on the plans offered. 💸
- Get your brand, product, or service the attention it deserves with affordable classified ad placement in the OpenLampTech newsletter.
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none and they provide free SSL.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
Disclosure: Some of this blog post’s services and product links 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 deserves with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support!