join,left join and where的测试

最新推荐文章于 2022-10-15 02:17:52 发布

aoerqileng

最新推荐文章于 2022-10-15 02:17:52 发布

阅读量647

点赞数

分类专栏： sql优化文章标签： left join on where

本文链接：https://blog.csdn.net/aoerqileng/article/details/45842873

版权

sql优化专栏收录该内容

33 篇文章 0 订阅

订阅专栏

SQL> select * from t2;

ID NAME
---------- -------------
2 c
3 d
e
2 f

SQL> select * from t1;

ID NAME
---------- --------------------
1 a

2 b

SQL> select * from t1 left join t2 on t1.id=t2.id;

ID NAME ID NAME
---------- -------------------- ---------- ------------
2 b 2 c
2 b 2 f
1 a

join后的and是先过滤表t2，然后再去和t1连接

SQL> select * from t1 left join t2 on t1.id=t2.id and t2.name<>'c';

ID NAME ID NAME
---------- -------------------- ---------- --------------------
2 b 2 f
1 a

执行计划中也是能看出来的额

--------------------------------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 80 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 3 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ID"="T2"."ID"(+))
3 - filter("T2"."NAME"(+)<>'c')

在where条件后面添加t2的过滤条件，后，先过滤t2,然后t1与t2的关联就变成了等值连接，不是外连接了

SQL> explain plan for select * from t1 left join t2 on t1.id=t2.id where t2.name<>'c';

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 80 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 40 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 3 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ID"="T2"."ID")
3 - filter("T2"."NAME"<>'c')

select * from t1 left join t2 on t1.id=t2.id where t1.name <>'a';

ID NAME ID NAME
---- -------------------- ---------- --------------------
2 b 2 c
2 b 2 f

SQL> explain plan for select * from t1 left join t2 on t1.id=t2.id where t1.name <>'a'

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 80 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 20 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ID"="T2"."ID"(+))
2 - filter("T1"."NAME"<>'a')

在where后面添加t1的过滤条件后，先过滤t1然后再外连接

在on后面添加t1的过滤条件

SQL> explain plan for select * from t1 left join t2 on t1.id=t2.id and t1.name <>'a';

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1823443478

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 80 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 80 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 2 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 4 | 80 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."ID"="T2"."ID"(+))
filter("T1"."NAME"<>CASE WHEN ("T2"."ID"(+) IS NOT NULL) THEN
'a' ELSE 'a' END )

Note
-----
- dynamic sampling used for this statement (level=2)

已选择21行。