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