Intersect - SQL Programming

INTERSECT returns rows that appear in both compatible result sets.
Program

Play the script to choose a campaign score threshold and find engaged subscribers who also logged in.
                            min_score
                            min_score = 80 (default)min_score = 70min_score = 90
                        

                            intersect_overlap.sql
                        
Replay: real traced execution (multi-file project)

                            CREATE TABLE campaign (email TEXT, score INTEGER);
INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);
CREATE TABLE logins (email TEXT, success INTEGER);
INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);
WITH params(min_score) AS (VALUES (80)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;
CREATE TABLE campaign (email TEXT, score INTEGER);
INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);
CREATE TABLE logins (email TEXT, success INTEGER);
INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);
WITH params(min_score) AS (VALUES (70)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;
CREATE TABLE campaign (email TEXT, score INTEGER);
INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);
CREATE TABLE logins (email TEXT, success INTEGER);
INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);
WITH params(min_score) AS (VALUES (90)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;

                        
1tables ← 1 row1CREATE TABLE campaign (email TEXT, score INTEGER);2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);⋯values this step1 rowtables
2campaign ← 3 rows1CREATE TABLE campaign (email TEXT, score INTEGER);2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);3CREATE TABLE logins (email TEXT, success INTEGER);⋯values this step3 rowscampaign
3tables ← 2 rows⋯2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);3CREATE TABLE logins (email TEXT, success INTEGER);4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);⋯values this step2 rowstables
4logins ← 4 rows⋯3CREATE TABLE logins (email TEXT, success INTEGER);4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);5WITH params(min_score) AS (VALUES (80)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;values this step4 rowslogins
5result ← 1 row⋯4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);5WITH params(min_score) AS (VALUES (80)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;values this step1 rowresult
1tables ← 1 row1CREATE TABLE campaign (email TEXT, score INTEGER);2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);⋯values this step1 rowtables
2campaign ← 3 rows1CREATE TABLE campaign (email TEXT, score INTEGER);2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);3CREATE TABLE logins (email TEXT, success INTEGER);⋯values this step3 rowscampaign
3tables ← 2 rows⋯2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);3CREATE TABLE logins (email TEXT, success INTEGER);4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);⋯values this step2 rowstables
4logins ← 4 rows⋯3CREATE TABLE logins (email TEXT, success INTEGER);4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);5WITH params(min_score) AS (VALUES (70)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;values this step4 rowslogins
5result ← 2 rows⋯4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);5WITH params(min_score) AS (VALUES (70)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE campaign (email TEXT, score INTEGER);2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);⋯values this step1 rowtables
2campaign ← 3 rows1CREATE TABLE campaign (email TEXT, score INTEGER);2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);3CREATE TABLE logins (email TEXT, success INTEGER);⋯values this step3 rowscampaign
3tables ← 2 rows⋯2INSERT INTO campaign VALUES ('ada@example.com', 92), ('lin@example.com', 75), ('nia@example.com', 88);3CREATE TABLE logins (email TEXT, success INTEGER);4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);⋯values this step2 rowstables
4logins ← 4 rows⋯3CREATE TABLE logins (email TEXT, success INTEGER);4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);5WITH params(min_score) AS (VALUES (90)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;values this step4 rowslogins
5result ← 1 row⋯4INSERT INTO logins VALUES ('ada@example.com', 1), ('lin@example.com', 1), ('mira@example.com', 1), ('nia@example.com', 0);5WITH params(min_score) AS (VALUES (90)) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;values this step1 rowresult
INTERSECT `INTERSECT` keeps values present in both selected sets.
set shape Each side returns one `email` column, so the sets are compatible.
threshold The selectable score threshold changes the first set before the intersection.