用JOIN替换子查询的SQL

huangapple go评论59阅读模式
英文:

Replace SubQuery with JOIN sql

问题

可以通过使用连接来替换下面的查询以提高性能:

查询:

select 
    t1.col1, t1.col2 
from 
    tbl t1
left join 
    tbl2 t2 
on 
    t1.col1 = t2.col1 and t1.col2 = t2.col2
where 
    t2.col1 is null

用连接替换子查询。

英文:

Is there any way we can replace below query with joins, this hitting the performance while using subquery

Tbl

| Col1 |  Col2 |
| ---- | ----- |
| 1    | 100   |
| 2    | 101   |
| 2    | 200   |
| 3    | 205   |
| 4    | 210   |

Tbl2

| Col1 |  Col2 |
| ---- | ----- |
| 1    | 100   |
| 2    | 101   |
| 2    | 200   |
| 3    | 300   |
| 4    | 210   |
| 5    | 211   |
| 6    | 212   |

Expected output

| Col1 |  Col2 |
| ---- | ----- |
| 3    | 205   |

Query:

select 
    t1.col1, t1.col2 
from 
    tb1 t1
where 
    t1.col2 not in (select t2.col2 
                    from tbl2 t2 
                    where t2.col1 = t1.col1)

Replace subquery with join

答案1

得分: 1

你可以使用反连接。例如:

select t1.*
from tb1 t1
left join tbl2 t2 on t2.col2 = t1.col2
where t2.col2 is null;

以下索引可以提升查询性能:

create index ix1 on tbl2 (col2);
英文:

You can use an anti-join. For example:

select t1.*
from tb1 t1
left join from tbl2 t2 on t2.col2 = t1.col2
where t2.col2 is null;

The following index can improve the query performance:

create index ix1 on tbl2 (col2);

huangapple
  • 本文由 发表于 2023年7月13日 00:49:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/76672856.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定