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

Question

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.

Answer 1

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 )

Answer 2

I may be completely off base, but I dont think you are going to achieve that in just SQL alone.

Now, if you loaded your list into a table, call it table_list, then left join it to your table_alphabet and exclude all rows where alphabet is null then you could get your list of exclusions.

Answer 3

Sorry, I completely misread the question the first time through. You will need a subquery that can pull all of the letters in your list as rows. The only way I can think of doing this is by UNIONing them all together:

select let from (select 'a' as let union select 'b' as let union select 'lol' as let union select 'cats' as let union select 'z' as let union select 'foo' as let) as innerletters where let not in (select letter from table_alphabet);

So in other words, your application will need to take the list "('a', 'b', 'lol', 'cats', 'z', 'foo')" and rewrite it in a UNION:

(select 'a' as let union select 'b' as let union select 'lol' as let union select 'cats' as let union select 'z' as let union select 'foo' as let)

...and insert that into the query. This should return the letters from the list that are NOT present in the letter column of the table_alphabet table:

+------+
| let  |
+------+
| lol  |
| cats |
| z    |
| foo  |
+------+