Skip to content

Instantly share code, notes, and snippets.

@sethcall
Created September 23, 2014 21:53
Show Gist options
  • Save sethcall/15308ccde298bff74584 to your computer and use it in GitHub Desktop.
Save sethcall/15308ccde298bff74584 to your computer and use it in GitHub Desktop.
Find rows that do not exist in a table by supplying literal values and without using a temporary table
-- here's my table with some data in it
CREATE TABLE sample ( client_id INT );
INSERT INTO sample VALUES (1);
-- here's the challenge; if my app has values 1, 2, and 3, I want a query that returns '2' and '3'... i.e., all values not currently in the 'sample' table. CTE to the rescue.
WITH app_client_ids(client_id) AS (VALUES(1), (2), (3))
SELECT client_ID from app_client_ids WHERE client_id NOT IN (SELECT client_id FROM sample);
-- result from psql:
client_id
-----------
2
3
(2 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment