Home > Software design >  Query to Check for Values Not Matching List in Where Clause
Query to Check for Values Not Matching List in Where Clause

Time:05-15

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

  • Related