inner、left/right、full outer、natural、union

表A和B的数据如下面所示：

点击(此处)折叠或打开

1. SQL> select * from a;
   
2. 
   
3.     CUSTNO CUSTNAME CITY
   
4. ---------- -------------------- --------------------
   
5.          1 KING    SEATTLE
   
6.          2 GREEN   BOSTON
   
7.          3 KOCHAR  SEATTLE
   
8.          4 SMITH   NEW YORK
   
9. 
   
10. SQL>
    
11. SQL> select * from b;
    
12. 
    
13.   B_CUSTNO B_CUSTNAME B_CITY
    
14. ---------- -------------------- --------------------
    
15.          1 GREEN      TOKYO
    
16.          2 KING       BOSTON
    
17.          3 COMMIT     NEW YORK

inner join : 就是表A和B的交集

点击(此处)折叠或打开

1. SQL> select * from a inner join b on a.city = b.b_city;
   
2. 
   
3.     CUSTNO CUSTNAME CITY    B_CUSTNO B_CUSTNAME B_CITY
   
4. ---------- -------------------- -------------------- ---------- 
   
5.          2 GREEN    BOSTON     2      KING     BOSTON
   
6.          4 SMITH    NEW YORK   3      COMMIT   NEW YORK

left join ：左边表全部显示，右边表只显示匹配上的，没有匹配上的显示为null
right join ：右边表全部显示，左边表只显示匹配上的，没有匹配上的显示为null

点击(此处)折叠或打开

1. SQL> select * from a left join b on a.city = b.b_city order by 1 ;
   
2. 
   
3.     CUSTNO CUSTNAME CITY    B_CUSTNO B_CUSTNAME B_CITY
   
4. ---------- -------------------- -------------------- ---------- -------------------- --------------------
   
5.          1 KING    SEATTLE
   
6.          2 GREEN   BOSTON   2        KING        BOSTON
   
7.          3 KOCHAR  SEATTLE
   
8.          4 SMITH   NEW YORK 3        COMMIT      NEW YORK
   
9. 
   
10. SQL> select * from a right join b on a.city = b.b_city order by 4 ;
    
11. 
    
12.     CUSTNO CUSTNAME CITY B_CUSTNO B_CUSTNAME B_CITY
    
13. ---------- -------------------- -------------------- ---------- -------------------- --------------------
    
14.                               1   GREEN    TOKYO
    
15.          2 GREEN    BOSTON    2   KING     BOSTON
    
16.          4 SMITH    NEW YORK  3   COMMIT   NEW YORK

full outer join: 两个表的并集

点击(此处)折叠或打开

1. SQL> select * from a full outer join b on a.city = b.b_city ;
   
2. 
   
3.     CUSTNO CUSTNAME CITY B_CUSTNO B_CUSTNAME B_CITY
   
4. ---------- -------------------- -------------------- ---------- -------------------- --------------------
   
5.          1 KING     SEATTLE
   
6.          2 GREEN    BOSTON    2    KING     BOSTON
   
7.          3 KOCHAR   SEATTLE
   
8.          4 SMITH    NEW YORK  3    COMMIT   NEW YORK
   
9.                               1    GREEN    TOKYO

natural join :自然连接，连接2个表中字段名相同及字段类型兼容（隐式转换）的列，默认允许再增加条件

点击(此处)折叠或打开

1. SQL> select * from a;
   
2. 
   
3.     CUSTNO CUSTNAME CITY
   
4. ---------- -------------------- --------------------
   
5.          1 KING     SEATTLE
   
6.          2 GREEN    BOSTON
   
7.          3 KOCHAR   SEATTLE
   
8.          4 SMITH    NEW YORK
   
9. 
   
10. SQL> select * from c;
    
11. 
    
12.     CUSTNO CUSTNAME
    
13. ---------- --------------------
    
14.          1 KING
    
15.          2 SMITH
    
16.          3 TEST
    
17. 
    
18. SQL> select * from a natural join c ;
    
19. 
    
20.     CUSTNO CUSTNAME CITY
    
21. ---------- -------------------- --------------------
    
22.          1 KING SEATTLE
    

  用join using() 可以得到与natural join一样的结果：

1. SQL> select * from a join c using (custno,custname);
   
2. 
   
3.     CUSTNO CUSTNAME CITY
   
4. ---------- -------------------- --------------------
   
5.          1 KING SEATTLE

union ,union all ：合并2个结果集，2个结果集的查询列数目必须相同，并且，列的数据类型相同或兼容。
当显示单个列时，union会对结果集去重，union all则不去重

点击(此处)折叠或打开

1. SQL> select custname from a union select b_custname from b;
   
2. 
   
3. CUSTNAME
   
4. --------------------
   
5. COMMIT
   
6. GREEN
   
7. KING
   
8. KOCHAR
   
9. SMITH
   
10. 
    
11. SQL> select custname from a union all select b_custname from b;
    
12. 
    
13. CUSTNAME
    
14. --------------------
    
15. KING
    
16. GREEN
    
17. KOCHAR
    
18. SMITH
    
19. GREEN
    
20. KING
    
21. COMMIT
    
22. 
    
23. 7 rows selected.

单列结果集时，union会对结果去重，union all 则不去重

1. SQL> select custno,custname from a union select b_custno,b_custname from b;
   
2. 
   
3.     CUSTNO CUSTNAME
   
4. ---------- --------------------
   
5.          1 GREEN
   
6.          1 KING
   
7.          2 GREEN
   
8.          2 KING
   
9.          3 COMMIT
   
10.          3 KOCHAR
    
11.          4 SMITH
    
12. 
    
13. 7 rows selected.
    
14. 
    
15. SQL> select custno,custname from a union all select b_custno,b_custname from b;
    
16. 
    
17.     CUSTNO CUSTNAME
    
18. ---------- --------------------
    
19.          1 KING
    
20.          2 GREEN
    
21.          3 KOCHAR
    
22.          4 SMITH
    
23.          1 GREEN
    
24.          2 KING
    
25.          3 COMMIT
    
26. 
    
27. 7 rows selected.

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

转载于:http://blog.itpub.net/28551528/viewspace-2123870/