[@more@]

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as mr
========建立测试表WT1，WT2
SQL> drop TABLE wt1;

Table dropped

SQL> drop TABLE wt2;

Table dropped

SQL> create TABLE wt1(x NUMBER, y NUMBER);

Table created

SQL>
SQL> INSERT INTO wt1 SELECT 1,11 from dual;

1 row inserted
SQL> INSERT INTO wt1 SELECT 2,22 from dual;

1 row inserted

SQL> commit;

Commit complete

SQL> create TABLE wt2(x NUMber,y NUMBER ,z NUMBER);

Table created

SQL>
SQL> INSERT INTO wt2 SELECT 1,NULL,3 from dual;

1 row inserted
SQL> INSERT INTO wt2 SELECT NULL,2,4 from dual;

1 row inserted
SQL> COMMIT;

Commit complete

SQL> SELECT * FROM wt1;

X Y
---------- ----------
1 11
2 22
SQL> SELECT * FROM wt2;

X Y Z
---------- ---------- ----------
1 3
2 4
1
======================================================================
---WT1左连接 wt2,wt1 是preserved table
SQL> SELECT wt1.*,wt2.*
2 from wt1,wt2
3 WHERE wt1.x = wt2.x(+);

X Y X Y Z
---------- ---------- ---------- ---------- ----------
1 11 1 3
2 22

--用sql新标准写的对应作用
SQL> SELECT wt1.*,wt2.*
2 FROM wt1 LEFT JOIN wt2
3 ON wt1.x = wt2.x;

X Y X Y Z
---------- ---------- ---------- ---------- ----------
1 11 1 3
2 22

================================================================

--1 下面的该语句的意思是wt1左连接(通过过滤wt1.y is not null后的结果集)

-- wt1.y is not null 过滤后的结果集如下
X Y Z
---------- ---------- ----------
2 4



SQL> SELECT wt1.*,wt2.*
2 from wt1,wt2
3 WHERE wt1.x = wt2.x(+)
4 AND wt2.y(+) IS NOT NULL;

X Y X Y Z
---------- ---------- ---------- ---------- ----------
1 11
2 22

SQL> explain plan for
2
2 SELECT wt1.*,wt2.*
3 from wt1,wt2
4 WHERE wt1.x = wt2.x(+)
5 AND wt2.y(+) IS NOT NULL;

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1634425899
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 130 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 130 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| WT1 | 2 | 52 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| WT2 | 1 | 39 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WT1"."X"="WT2"."X"(+))
3 - filter("WT2"."Y"(+) IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)

20 rows selected



===============================================================================

--2 新sql语法的相同写法，可以看出新语法明确的表示了连接条件以及过滤条件的位置
SQL> SELECT wt1.*,wt2.*
2 from wt1 LEFT JOIN wt2
3 ON (wt1.x=wt2.x AND wt2.y is NOT NULL);

X Y X Y Z
---------- ---------- ---------- ---------- ----------
1 11
2 22

SQL> explain plan for
2
2 SELECT wt1.*,wt2.*
3 from wt1 LEFT JOIN wt2
4 ON (wt1.x=wt2.x AND wt2.y is NOT NULL);

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1634425899
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 130 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 130 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| WT1 | 2 | 52 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| WT2 | 1 | 39 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WT1"."X"="WT2"."X"(+))
3 - filter("WT2"."Y"(+) IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)

20 rows selected

=====================================================================

---3 实际也可以这么写
SQL> SELECT wt1.*,wt2.*
2 from wt1,(SELECT * from wt2 WHERE wt2.y IS NOT NULL) wt2
3 WHERE wt1.x= wt2.x(+)
4 /

X Y X Y Z
---------- ---------- ---------- ---------- ----------
1 11
2 22


SQL> explain plan for
2
2 SELECT wt1.*,wt2.*
3 from wt1,(SELECT * from wt2 WHERE wt2.y IS NOT NULL) wt2
4 WHERE wt1.x= wt2.x(+)
5 /

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1634425899
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 130 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 2 | 130 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| WT1 | 2 | 52 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| WT2 | 1 | 39 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WT1"."X"="WT2"."X"(+))
3 - filter("WT2"."Y"(+) IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)

20 rows selected

--该语句的意思是(wt1左连接wt2后得出结果集)再通过wt2.y is not null 过滤，结果发现数据都被过滤掉了(对比上面的左连接结果集)
--顺序是先连接后过滤，与上面的sql有点区别 上面的是先过滤后连接；
SQL> SELECT wt1.*,wt2.*
2 from wt1,wt2
3 WHERE wt1.x = wt2.x(+)
4 AND wt2.y IS NOT NULL;

X Y X Y Z
---------- ---------- ---------- ---------- ----------

SQL> explain plan for
2
2 SELECT wt1.*,wt2.*
3 from wt1,wt2
4 WHERE wt1.x = wt2.x(+)
5 AND wt2.y IS NOT NULL;

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2052411046
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 9 (12)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| WT2 | 1 | 39 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| WT1 | 2 | 52 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WT1"."X"="WT2"."X")
2 - filter("WT2"."Y" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)

20 rows selected

==================================================

--新sql语法的相同写法
SQL> SELECT wt1.*,wt2.*
2 from wt1 LEFT JOIN wt2
3 ON (wt1.x=wt2.x)
4 WHERE wt2.y IS NOT NULL;

X Y X Y Z
---------- ---------- ---------- ---------- ----------

SQL> explain plan for
2
2 SELECT wt1.*,wt2.*
3 from wt1 LEFT JOIN wt2
4 ON (wt1.x=wt2.x)
5 WHERE wt2.y IS NOT NULL;

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2052411046
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 9 (12)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| WT2 | 1 | 39 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| WT1 | 2 | 52 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("WT1"."X"="WT2"."X")
2 - filter("WT2"."Y" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)

20 rows selected