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.

intersect_overlap.sql

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 ()) 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 ()) 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 ()) SELECT email FROM campaign WHERE score >= (SELECT min_score FROM params) INTERSECT SELECT email FROM logins WHERE success = 1 ORDER BY email;

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.