MySQL 多个 IN 条件对同一个表进行子查询 [英] MySQL multiple IN conditions to subquery with same table

问题描述

我有多个带有子查询的 IN 条件.

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)

推荐答案

重新编写以使用 NOT EXISTS 代替.即，当标签中没有带有 somethingId 等于 s.id 且 id 为 1、2 或 3 的行时，从 S 返回.

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)

还有 NOT EXISTS 是空安全的".(NOT IN(选择返回空值) 将根本不返回任何行.)

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

这篇关于MySQL 多个 IN 条件对同一个表进行子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！