eromlige eromlige - 2 months ago 7
SQL Question

SQL LEFT JOIN where clause with IN comparison

May have inadvertently stumbled onto a good SQL test question.


  • I have a table with let's say ~100 usernames.

  • I have a set of 10 possible usernames { aname, bname, cname, dname, ... }



If I do:

SELECT user.username FROM user WHERE user.username IN ('aname', 'bname', 'cname',...);


I get a list of usernames selected for, minus the ones not found in the table, grand.

What I actually want, is a list of the ones NOT found in the table.

If the
WHERE...IN
clause list was a table, I'd just
LEFT JOIN
it onto the user table and filter for
NULLs
.

Is there a way to do this without making a temp table and left joining that to the user table? I guess, sort of a left join of the user table to the
WHERE...IN
clause?

I've never done or seen it, but perhaps it exists.

Answer

You can do this with a derived table and a left join:

SELECT l.name
FROM (SELECT 'aname' as name UNION ALL
      SELECT 'bname' UNION ALL
      SELECT 'cname' UNION ALL
      . . .
     ) l LEFT JOIN
     user u
     ON u.username = l.name
WHERE u.username IS NULL
Comments