表A和B的数据如下面所示：
inner join : 就是表A和B的交集
left join ：左边表全部显示，右边表只显示匹配上的，没有匹配上的显示为null
right join ：右边表全部显示，左边表只显示匹配上的，没有匹配上的显示为null
full outer join: 两个表的并集
natural join :自然连接，连接2个表中字段名相同及字段类型兼容（隐式转换）的列，默认允许再增加条件
union ,union all ：合并2个结果集，2个结果集的查询列数目必须相同，并且，列的数据类型相同或兼容。
当显示单个列时，union会对结果集去重，union all则不去重
单列结果集时，union会对结果去重，union all 则不去重
点击(此处)折叠或打开
-
SQL> select * from a;
-
-
CUSTNO CUSTNAME CITY
-
---------- -------------------- --------------------
-
1 KING SEATTLE
-
2 GREEN BOSTON
-
3 KOCHAR SEATTLE
-
4 SMITH NEW YORK
-
-
SQL>
-
SQL> select * from b;
-
-
B_CUSTNO B_CUSTNAME B_CITY
-
---------- -------------------- --------------------
-
1 GREEN TOKYO
-
2 KING BOSTON
- 3 COMMIT NEW YORK
点击(此处)折叠或打开
-
SQL> select * from a inner join b on a.city = b.b_city;
-
-
CUSTNO CUSTNAME CITY B_CUSTNO B_CUSTNAME B_CITY
-
---------- -------------------- -------------------- ----------
-
2 GREEN BOSTON 2 KING BOSTON
- 4 SMITH NEW YORK 3 COMMIT NEW YORK
right join ：右边表全部显示，左边表只显示匹配上的，没有匹配上的显示为null
点击(此处)折叠或打开
-
SQL> select * from a left join b on a.city = b.b_city order by 1 ;
-
-
CUSTNO CUSTNAME CITY B_CUSTNO B_CUSTNAME B_CITY
-
---------- -------------------- -------------------- ---------- -------------------- --------------------
-
1 KING SEATTLE
-
2 GREEN BOSTON 2 KING BOSTON
-
3 KOCHAR SEATTLE
-
4 SMITH NEW YORK 3 COMMIT NEW YORK
-
-
SQL> select * from a right join b on a.city = b.b_city order by 4 ;
-
-
CUSTNO CUSTNAME CITY B_CUSTNO B_CUSTNAME B_CITY
-
---------- -------------------- -------------------- ---------- -------------------- --------------------
-
1 GREEN TOKYO
-
2 GREEN BOSTON 2 KING BOSTON
- 4 SMITH NEW YORK 3 COMMIT NEW YORK
full outer join: 两个表的并集
点击(此处)折叠或打开
-
SQL> select * from a full outer join b on a.city = b.b_city ;
-
-
CUSTNO CUSTNAME CITY B_CUSTNO B_CUSTNAME B_CITY
-
---------- -------------------- -------------------- ---------- -------------------- --------------------
-
1 KING SEATTLE
-
2 GREEN BOSTON 2 KING BOSTON
-
3 KOCHAR SEATTLE
-
4 SMITH NEW YORK 3 COMMIT NEW YORK
- 1 GREEN TOKYO
natural join :自然连接，连接2个表中字段名相同及字段类型兼容（隐式转换）的列，默认允许再增加条件
点击(此处)折叠或打开
-
SQL> select * from a;
-
-
CUSTNO CUSTNAME CITY
-
---------- -------------------- --------------------
-
1 KING SEATTLE
-
2 GREEN BOSTON
-
3 KOCHAR SEATTLE
-
4 SMITH NEW YORK
-
-
SQL> select * from c;
-
-
CUSTNO CUSTNAME
-
---------- --------------------
-
1 KING
-
2 SMITH
-
3 TEST
-
-
SQL> select * from a natural join c ;
-
-
CUSTNO CUSTNAME CITY
-
---------- -------------------- --------------------
-
1 KING SEATTLE
用join using() 可以得到与natural join一样的结果：
-
SQL> select * from a join c using (custno,custname);
-
-
CUSTNO CUSTNAME CITY
-
---------- -------------------- --------------------
- 1 KING SEATTLE
当显示单个列时，union会对结果集去重，union all则不去重
点击(此处)折叠或打开
-
SQL> select custname from a union select b_custname from b;
-
-
CUSTNAME
-
--------------------
-
COMMIT
-
GREEN
-
KING
-
KOCHAR
-
SMITH
-
-
SQL> select custname from a union all select b_custname from b;
-
-
CUSTNAME
-
--------------------
-
KING
-
GREEN
-
KOCHAR
-
SMITH
-
GREEN
-
KING
-
COMMIT
-
- 7 rows selected.
单列结果集时，union会对结果去重，union all 则不去重
-
SQL> select custno,custname from a union select b_custno,b_custname from b;
-
-
CUSTNO CUSTNAME
-
---------- --------------------
-
1 GREEN
-
1 KING
-
2 GREEN
-
2 KING
-
3 COMMIT
-
3 KOCHAR
-
4 SMITH
-
-
7 rows selected.
-
-
SQL> select custno,custname from a union all select b_custno,b_custname from b;
-
-
CUSTNO CUSTNAME
-
---------- --------------------
-
1 KING
-
2 GREEN
-
3 KOCHAR
-
4 SMITH
-
1 GREEN
-
2 KING
-
3 COMMIT
-
- 7 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28551528/viewspace-2123870/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
ORA-12537 dblink
请登录后发表评论
登录
全部评论
