I have a list of ids:
(1, 2, 3, 6, 7)
And a table:
id | anothercolumn
------------------
1 | NULL
2 | foo
4 | bar
5 | NULL
6 | NULL
I want to retrieve the values from my list which are not ids of my table.
Expected result:
3, 7
I tried something like this:
SELECT i
WHERE i IN (1, 2, 3, 6, 7)
AND i NOT IN (SELECT id FROM mytable);
But this is not a valid MySQL query (a FROM
is required).
There is also this possibility:
SELECT i
FROM (
SELECT 1 AS i
UNION SELECT 2
UNION SELECT 3
UNION SELECT 6
UNION SELECT 7 ) AS mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;
This works, but if my list of ids becomes bigger, the query will soon be huge ...
I can also create a temporary table for my list of ids:
CREATE TEMPORARY TABLE mylistofids (
i INT
);
INSERT INTO mylistofids (i) VALUES (1), (2), (3), (6), (7);
Then use it in a LEFT JOIN
:
SELECT i
FROM mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;
This works too, but in my case, I don't have the rights to create a table (temporary or not).
Do you see a way to solve this problem in the nicest way possible?
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.
select without from , on the other hand, works on four of them. By now you might wonder why stand-alone values might be useful at all. As I implied above, it is more powerful than select without from because it is not limited to produce a single row. With select without from , you'd need to use union .
A temp table is the best solution, but if not possible then you can fudge a temp table by selecting constants and unioning them together.
Using this solution you can do the following:-
SELECT i
FROM
(
SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 6 UNION SELECT 7
) AS mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mytable.id IS NULL;
You can also generate a massive range of numbers (assuming that you are dealing with integer ids) by cross joining ranges of numbers. Then just pick the ones that are in the IN clause:-
SELECT i
FROM
(
SELECT units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 + tensthousands.i * 10000 AS i
FROM (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS units
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS tens
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS hundreds
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS thousands
CROSS JOIN (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0 ) AS tensthousands
) AS mylistofids
LEFT JOIN mytable
ON mytable.id = i
WHERE mylistofids.i IN (1, 2, 3, 6, 7)
AND mytable.id IS NULL;
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