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

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  |
+------+