I have an array ('abc','def','ghi','jkl'). I want to find the values which are not in mysql table.
That is, if 'def'is not in the table, then it should show 'def' and so on.
My query was:
SELECT column FROM table WHERE column not in ('abc','def','ghi','jkl')
But its wrong. How can I get the values which are not there in the column?
Here is how you can do it:
select x.col
from (values row('def'),row('abc'),row('ghi')) x(col)
left join t on t.col = x.col
Where t.col is null
in version 5.x you can do this instead :
select x.col
from (select ('def') as col union select ('abc') union select ('ghi')) x
left join t on t.col = x.col
Where t.col is null
You should put these values first in some table and then do "Not in" like :
SELECT column FROM table WHERE column not in (select distinct col1 from table1)
SHOW CREATE TABLE table_name;to get table structure.