关于外连接的一个实验(先过滤后连接，先连接后过滤)

[@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

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