MySQL multiple IN conditions to subquery with same table

Question

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)

You should prefere JOIN or EXISTS() as they are faster and more flexible in many situations. — Daniel W.
– Daniel W., Commented Jan 5, 2017 at 9:01

jarlh · Accepted Answer · 2017-01-05 09:01:26Z

4

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.)

answered Jan 5, 2017 at 9:01

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ernestas Stankevičius Over a year ago

I'm idiot... I could thought about that my self... :( Thank you. I will accept your answer as soon as I can.

Collectives™ on Stack Overflow

MySQL multiple IN conditions to subquery with same table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related