Menu
Query:
select id from users where id in (1,2,3,4,5) If the users table contains ids 1, 2, 3, this would return 1, 2, and 3. I want a query that would return 4 and 5. In other words, I don't want the query to return any rows that exist in the table, I want to give it a list of numbers and get the values from that list that don't appear in the table.
(updated to clarify question following several inapplicable answers)
653
How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.
The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
Outer joins return all rows from one table and matching rows from the second table.
If you don't want to (explicitly) use temporary tables, this will work:
SELECT id FROM ( (SELECT 1 AS id) UNION ALL (SELECT 2 AS id) UNION ALL (SELECT 3 AS id) UNION ALL (SELECT 4 AS id) UNION ALL (SELECT 5 AS id) ) AS list LEFT JOIN users USING (id) WHERE users.id IS NULL However, it is quite ugly, quite long, and I am dubious about how it would perform if the list of IDs is long.
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
