MySQL 'WHERE'排除子查询结果的子句 [英] MySQL 'WHERE' clause which excludes results in subquery

问题描述

我有以下 SQL 查询:

I have the following SQL query:

SELECT members.member_ID, members.nick_name 
          FROM orgs
    INNER JOIN assets ON assets.org_ID = orgs.org_ID
    INNER JOIN orgs_to_members ON orgs_to_members.org_ID = orgs.org_ID
    INNER JOIN members ON members.member_ID = orgs_to_members.member_ID
    where orgs.org_ID = '7' 
    AND NOT EXISTS (select shares.member_ID from shares where shares.asset_ID = '224')

组织 7 中有 3 个成员:

There are 3 members in org 7:

     - member_ID 1
     - member_ID 4
     - member_ID 6

在子查询中，成员 ID 1 和 4 结果.我正在尝试编写 1 个仅返回成员 ID #6 的查询.当我运行上述查询时，我没有得到任何结果.当我将它们分开时，我得到了预期的结果.请帮忙.

In the subquery, member IDs 1 and 4 result. I am trying to write 1 query which will return only member ID #6. When i run the above query, I get no results. When I separate them, I get the expected results. Please help.

谢谢！

推荐答案

AND NOT EXISTS (select ...) 用于确保子查询不返回任何行.通常只有在子查询是相关的(即，如果它引用来自外部查询的值)时才有意义，否则它对于每个结果行都为真(并且实际上不会影响查询)，或者为假每个结果行(并且会导致查询根本不返回任何结果，就像您的情况一样).我认为你想要的是:

AND NOT EXISTS (select ...) is used to make sure that the subquery doesn't return any rows. It usually only makes sense if the subquery is correlated (i.e., if it refers to values from the outer query), since otherwise it will either be true for every result row (and won't actually affect the query), or be false for every result row (and will cause the query to return no results at all, as in your case). I think what you want is:

    AND members.member_ID NOT IN (select shares.member_ID from shares where shares.asset_ID = '224')

这篇关于MySQL 'WHERE'排除子查询结果的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！