Ernestas Stankevičius
Ernestas Stankevičius

Reputation: 2493

MySQL multiple IN conditions to subquery with same table

I have multiple IN conditions with subqueries.

SELECT
    S.name,
    S.email
FROM something S
WHERE 
    1 NOT IN (SELECT id FROM tags WHERE somethingId = S.id)
    AND 2 NOT IN (SELECT id FROM tags WHERE somethingId = S.id)
    AND 3 NOT IN (SELECT id FROM tags WHERE somethingId = S.id)

Maybe there are better solutions? Something like:

    (1, 2, 3) NOT IN (SELECT id FROM tags WHERE somethingId = S.id)

Upvotes: 2

Views: 752

Answers (1)

jarlh
jarlh

Reputation: 44696

Re-write to use NOT EXISTS instead. I.e return from S when there is no row in tags with somethingId equals to s.id and id is 1, 2 or 3.

SELECT
    S.name,
    S.email
FROM something S
WHERE NOT EXISTS (SELECT 1 FROM tags WHERE id in (1, 2, 3) and somethingId = S.id)

Also NOT EXISTS is "null-safe". (NOT IN (select returning a null) will return no rows at all.)

Upvotes: 4

Related Questions