CODEWITHSUNDEEP
mysqlsqljoinleft-joinwhere-in
eromlige
eromlige

Reputation: 13

SQL LEFT JOIN where clause with IN comparison

May have inadvertently stumbled onto a good SQL test question.

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.

Upvotes: 0

Views: 452

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

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

Upvotes: 1

Related Questions