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_users

That'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

Easy

Available tables and columns:
newsletter: email, active
premium: email, plan

Return the emails that appear as active newsletter subscribers (active = 1) and as premium users on the 'pro' plan. Order alphabetically.

Try it yourself

-- filter each side before INTERSECT

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Beyond the Basics

1Conditional Logic

CASE WHEN CASE in ORDER BY COALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALLINTERSECTEXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWER SUBSTR INSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a Hierarchy Recap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASE Pivoting Rows to Columns Recap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers

Practice on your own: SQL playground