How to find which values are not in a column from the list?(SQL)

Question

I have a list of values:

('WEQ7EW', 'QWE7YB', 'FRERH4', 'FEY4B', .....)

and the dist table with a dist_name column.

and I need to create SQL query which would return values from the list which don't exist in the dist_name column.

Answer 1

Yo need to use left join. This requires creating a derived table with the values you care about. Here is typical syntax:

select v.val
from (values ('WEQ7EW'), ('QWE7YB'), ('FRERH4'), ('FEY4B')
     ) v(val) left join
     t
     on t.col = v.val
where t.col is null;

Not all databases support the values() table constructor but allow allow some method for creating a derived table. In MySQL, this looks like:

select v.val
from (select'WEQ7EW' as val union all
      select 'QWE7YB' as val union all
      select 'FRERH4' as val union all
      select 'FEY4B' as val
     ) v(val) left join
     t
     on t.col = v.val
where t.col is null;

Answer 2

You would typically put this list of values in a derived table, and then use not exists. In MySQL:

select v.dist_name 
from (
    select 'WEQ7EW' as dist_name
    union all select 'QWE7YB'
    union all ...
) v
where not exists (select 1 from dist d where d.dist_name = v.dist_name)

Or if you are running a very recent version (8.0.19 or higher), you can use the VALUES ROW() syntax:

select v.dist_name 
from (values row('WEQ7EW'), row('QWE7YB'), ...) v(dist_name)
where not exists (select 1 from dist d where d.dist_name = v.dist_name)

Answer 3

SELECT TRIM(TRAILING ',' FROM result) result
FROM ( SELECT @tmp:=REPLACE(@tmp, CONCAT(words.word, ','), '') result
       FROM words, (SELECT @tmp:='WEQ7EW,QWE7YB,FRERH4,FEY4B,') arg
     ) perform
ORDER BY LENGTH(result) LIMIT 1;

fiddle

The list of values to be cleared from existing values is provided as CSV string with final comma and without spaces before/after commas ('WEQ7EW,QWE7YB,FRERH4,FEY4B,' in shown code).

If CSV contains duplicate values all of them will be removed whereas non-removed duplicates won't be compacted. The relative arrangement of the values will stay unchanged.

Remember that this query performs full table scan, so it is not applicable to huge tables because it will be slow.