Created
September 23, 2014 21:53
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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