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

问题描述

我有以下SQL查询:

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个成员:

     - 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 ...)用于确保子查询不返回任何行.通常只有在子查询相关时(即，如果它引用外部查询中的值)才有意义，因为否则它将对每个结果行都为true(并且实际上不会影响查询)，或者为false每个结果行(并会导致查询完全不返回任何结果，就像您的情况一样).我认为您想要的是:

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屋！