I am writing a SQL query that uses the IN operator in the WHERE clause on a table that contains millions of records.
I would like to know for the specific values I am checking in my WHERE clause, which ones are missing from the table.
How do I write a query to check for the values that do not result in a match according to the values in my WHERE clause for those values specifically?
For example -
SELECT *
FROM employees
WHERE emp_id IN (123, 456, 789)
Let's say that this query returns 2 rows for emp_id = 123 and emp_id = 456.
I want to write a query that shows me what was missing, illustrating that there was no row in the table specifically for emp_id = 789.
CodePudding user response:
To find rows missing, you must have a canonical set of rows to compare with. Here's an example using the VALUES statement (which is new in MySQL 8.0).
Suppose we have a list 1, 2, 19, 64 and we want to find which ones don't have matching rows in the table mytable
.
SELECT t.column_0
FROM (VALUES ROW(1), ROW(2), ROW(19), ROW(64)) AS t
LEFT OUTER JOIN mytable ON t.column_0 = mytable.id
WHERE mytable.id IS NULL;
If you use a version older than MySQL 8.0, you can replace the derived table with this more verbose syntax:
...
FROM (SELECT 1 AS column_0 UNION SELECT 2 UNION SELECT 19 UNION SELECT 64) AS t
...
Or you could also create a temporary table and fill it with the values you want to search for, one value per row.
CodePudding user response:
You can use the opposite of IN which is NOT IN(). Keep the bit inside the brackets the same and you'll get every record that doesn't have that