Search code examples
mysqlselectwhere-in

MySQL, Given a list, selecting the missing rows from a Table

This has been driving me crazy for the past few minutes

I have a table, lets say table_alphabet with three columns. 

letter(pri)  col1   col2   
a            24     55
b            45     45
c            23     44
...
y            33     55
z            45     22

Now in my application I have a list ('a', 'b', 'lol', 'cats', 'z', 'foo').

If I do

SELECT letter FROM table_alphabet WHERE letter IN ('a', 'b', 'lol', 'cats', 'z', 'foo')

I only get the rows, (a, b,z)

What I want though is to get 'lol', 'cats', 'foo'. Or in english, what items in my list are missing from the table.

Any help would be appreciated, I seem to be having a brain malfunction today.

Solution

  • The NOT IN command will work even with a non static list you are filtering against. You can use a sub-query like:

    select letter from table_alphabet where letter NOT IN ( select letter from exclude_table )