左连接和右连接

表t1:
SQL> select * from folore.t1;

        ID NAME
---------- ------------------------------
      1003 ccc
      1004 ddd
      1007 gggg
      1005 eee
      1006 ffff
      1001 aaa
      1002 bbb
      
表t2:
SQL> select * from t2;

        ID NAME
---------- ------------------------------
      1001 aaa
      1002 bbb      

左连接:
以t1表为基础表，t2表为匹配表，结果集显示为基础表所有数据，另外显示匹配表与基础表条件相匹配的结果，其余的显示为空值：

SQL> select a.id,b.id from t1 a left join t2 b on a.id=b.id;

        ID         ID
---------- ----------
      1001       1001
      1002       1002
      1006
      1005
      1007
      1004
      1003
SQL> select * from t1 a left join t2 b on a.id=b.id;

        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
      1001 aaa                                  1001 aaa
      1002 bbb                                  1002 bbb
      1006 ffff
      1005 eee
      1007 gggg
      1004 ddd
      1003 ccc
如果使用(+),(+)所在的一边为匹配表，另一边为基础表，(+)必须使用where条件语句：
SQL> select * from t1 a,t2 b where a.id=b.id(+);

        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
      1001 aaa                                  1001 aaa
      1002 bbb                                  1002 bbb
      1006 ffff
      1005 eee
      1007 gggg
      1004 ddd
      1003 ccc
      
以t2表为基础表，t1表为匹配表：
SQL> select * from t2 a left join t1 b on a.id=b.id;

        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
      1001 aaa                                  1001 aaa
      1002 bbb                                  1002 bbb

      
SQL>select * from t1 a,t2 b where a.id(+)=b.id

        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
      1001 aaa                                  1001 aaa
      1002 bbb                                  1002 bbb
      
右连接，刚好与左连接相反:
以t1为基础表，t2为匹配表：  
SQL> select a.id,b.id from t1 a right join t2 b on a.id=b.id;

        ID         ID
---------- ----------
      1001       1001
      1002       1002  
以t2为基础表，t1为匹配表，结果集显示为基础表所有数据，另外显示匹配表与基础表条件相匹配的结果，其余的显示为空值：      
SQL> select a.id,b.id from t2 a right join t1 b on b.id=a.id;

        ID         ID
---------- ----------
      1001       1001
      1002       1002
                 1006
                 1005
                 1007
                 1004
                 1003      

表关联条件前后顺序不影响结果：
SQL> select a.id,b.id from t1 a right join t2 b on a.id=b.id;

        ID         ID
---------- ----------
      1001       1001
      1002       1002

SQL> select a.id,b.id from t1 a right join t2 b on b.id=a.id;

        ID         ID
---------- ----------
      1001       1001
      1002       1002
      
全连结：
SQL>select * from t1 full join t2 on t1.id=t2.id;

        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
      1003 ccc
      1004 ddd
      1007 gggg
      1005 eee
      1006 ffff
      1001 aaa                                  1001 aaa
      1002 bbb                                  1002 bbb

7 rows selected.

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

        ID NAME                                   ID NAME
---------- ------------------------------ ---------- ------------------------------
                                                1003 ccc
                                                1004 ddd
                                                1007 gggg
                                                1005 eee
                                                1006 ffff
      1001 aaa                                  1001 aaa
      1002 bbb                                  1002 bbb                                  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24668589/viewspace-2125220/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24668589/viewspace-2125220/