将具有相关子查询的查询简化为简单连接

发布于 2024-10-17 09:42:27 字数 394 浏览 2 评论 0原文

我需要帮助来简化以下查询。

我能够在下面的查询中不使用 Group By/having Count 子句但使用相关子查询来检查“0”计数。

现在，我被要求将以下查询简化为简单连接！。

我尝试将查询合并为一个。但输出不同。

您能否提出任何其他简化查询的想法，即检查“0”计数。

select distinct tab1.col1
  from tab1
  where tab1.col2 = 'A'
  And 0 = (select count(tab2.col1)
            from tab2
            where tab2.col2 = 'B'
            and tab2.col1 = tab1.col1)

原文

I need help in simplifying the below query.

I was able to check for '0' count without using Group By/having Count clauses in the below query but with correlated subquery.

Now, I've been asked to simplify the below query as simple joins!.

I tried merging the query into one. But the output differs.

Could you please suggest any other idea of simplifying the query, which is checking for '0' count.

select distinct tab1.col1
  from tab1
  where tab1.col2 = 'A'
  And 0 = (select count(tab2.col1)
            from tab2
            where tab2.col2 = 'B'
            and tab2.col1 = tab1.col1)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花落人断肠 2024-10-24 09:42:27

这类事情通常会写成 NOT EXISTS

SELECT distinct tab1.col1
  FROM tab1
 WHERE tab1.col2 = 'A'
   AND NOT EXISTS( 
      SELECT 1
        FROM tab2
       WHERE tab2.col2 = 'B'
         AND tab2.col1 = tab1.col1 )

但是你也可以写

SELECT tab1.col1, count(tab2.col1)
  FROM (SELECT * FROM tab1 WHERE col2 = 'A') tab1,
       (SELECT * FROM tab2 WHERE col2 = 'B') tab2 
 WHERE tab1.col1 = tab2.col2(+)
 GROUP BY tab1.col1
HAVING count(tab2.col1) = 0

This sort of thing would normally be written as a NOT EXISTS

SELECT distinct tab1.col1
  FROM tab1
 WHERE tab1.col2 = 'A'
   AND NOT EXISTS( 
      SELECT 1
        FROM tab2
       WHERE tab2.col2 = 'B'
         AND tab2.col1 = tab1.col1 )

However you could also write

SELECT tab1.col1, count(tab2.col1)
  FROM (SELECT * FROM tab1 WHERE col2 = 'A') tab1,
       (SELECT * FROM tab2 WHERE col2 = 'B') tab2 
 WHERE tab1.col1 = tab2.col2(+)
 GROUP BY tab1.col1
HAVING count(tab2.col1) = 0

回复收藏 0 原文

孤芳又自赏 2024-10-24 09:42:27

尝试其中一些。
如果 col1 声明为非 null，则前两个查询具有相同的执行计划（反连接）。第二种选择是我个人的建议，因为它最符合您的要求。

-- Non-correlated subquery
select distinct col1
  from tab1
 where col2 = 'A'
   and col1 not in(select col1 
                     from tab2 
                    where col2 = 'B');

-- Correlated subquery
select distinct col1
  from tab1
 where col2 = 'A'
   and not exists(select 'x'
                    from tab2 
                   where tab2.col2 = 'B'
                     and tab2.col1 = tab1.col1);

-- Using join
select distinct tab1.col1
  from tab1 
  left join tab2 on(tab2.col2 = 'B' and tab2.col1 = tab1.col1)
 where tab1.col2 = 'A'
   and tab2.col1 is null;

-- Using aggregation   
select tab1.col1
  from tab1 
  left join tab2 on(tab2.col2 = 'B' and tab2.col1 = tab1.col1)
 where tab1.col2 = 'A'
 group 
    by tab1.col1
having count(tab2.col2) = 0;

Try some of these.
If col1 is declared as not null, the first two queries have the same execution plan (anti-joins). The second alternative is my personal advice, since it matches your requirements the best.

-- Non-correlated subquery
select distinct col1
  from tab1
 where col2 = 'A'
   and col1 not in(select col1 
                     from tab2 
                    where col2 = 'B');

-- Correlated subquery
select distinct col1
  from tab1
 where col2 = 'A'
   and not exists(select 'x'
                    from tab2 
                   where tab2.col2 = 'B'
                     and tab2.col1 = tab1.col1);

-- Using join
select distinct tab1.col1
  from tab1 
  left join tab2 on(tab2.col2 = 'B' and tab2.col1 = tab1.col1)
 where tab1.col2 = 'A'
   and tab2.col1 is null;

-- Using aggregation   
select tab1.col1
  from tab1 
  left join tab2 on(tab2.col2 = 'B' and tab2.col1 = tab1.col1)
 where tab1.col2 = 'A'
 group 
    by tab1.col1
having count(tab2.col2) = 0;

回复收藏 0 原文

~没有更多了~