eromlige eromlige - 1 year ago 76
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
clause list was a table, I'd just
it onto the user table and filter for

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

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

Answer Source

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

FROM (SELECT 'aname' as name UNION ALL
      SELECT 'bname' UNION ALL
      SELECT 'cname' UNION ALL
      . . .
     ) l LEFT JOIN
     user u
     ON u.username =
WHERE u.username IS NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download