SQL LEFT JOIN where clause with IN comparison

Question

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 1

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