INTERSECT
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 17 of 27.
INTERSECT returns only rows that appear in both result sets. Like UNION, it removes duplicates and requires matching columns on each side.
SELECT email FROM newsletter_subs
INTERSECT
SELECT email FROM premium_usersThat's every email that's both subscribed to the newsletter and a premium user, without writing a join.
Why bother, when a JOIN can do this too? INTERSECT is shorter when both sides are simple SELECTs and you're matching on the entire row, not just an id.
Challenge
EasyAvailable tables and columns:
<strong>newsletter</strong>:<strong>email</strong>,<strong>active</strong><strong>premium</strong>:<strong>email</strong>,<strong>plan</strong>
Return the emails that appear as active newsletter subscribers (active = 1) and as premium users on the 'pro' plan. Order alphabetically.
Cheat sheet
INTERSECT returns only rows that appear in both result sets (duplicates removed, columns must match):
SELECT email FROM newsletter_subs
INTERSECT
SELECT email FROM premium_usersUse INTERSECT instead of a JOIN when matching on entire rows across two simple SELECTs.
Try it yourself
-- filter each side before INTERSECT
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.