SQL关键字---JOIN，UNION

一.join用法说明

inner join

SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

Left JOIN

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Right JOIN

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer JOIN

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

cross join

cross join即A和B的笛卡儿积。

SELECT <select_list>
FROM Table_A A
cross JOIN Table_B B
ON A.Key = B.Key

二.简单使用

create table english_cert(name character varying, cert character varying);
--此表为英语证书表，插入以下数据
 name |      cert
------+-----------------
 A    | LEVEL 4
 B    | ENGLISH LEVEL 6
 C    | ENGLISH LEVEL 8
 D    | ENGLISH LEVEL 6


create table computer_cert(name character varying, cert character varying);
--此表为英语证书表，插入以下数据
 name |       cert
------+------------------
 A    | computer LEVEL 2
 F    | computer LEVEL 2
 C    | computer LEVEL 3
 E    | computer LEVEL 3

inner join

 SELECT * FROM english_cert join computer_cert 
 	on english_cert.name = computer_cert.name;
 ----执行结果
 name |      cert       | name |       cert
------+-----------------+------+------------------
 A    | ENGLISH LEVEL 4 | A    | computer LEVEL 2
 C    | ENGLISH LEVEL 8 | C    | computer LEVEL 3
(2 rows)

----既有英语证书又有计算机证书的人
 SELECT english_cert.name FROM english_cert join computer_cert 
 	on english_cert.name = computer_cert.name;
----执行结果
 name
------
 A
 C

left join

SELECT * FROM english_cert left join computer_cert 
 on english_cert.name = computer_cert.name;
 name |      cert       | name |       cert
------+-----------------+------+------------------
 A    | ENGLISH LEVEL 4 | A    | computer LEVEL 2
 B    | ENGLISH LEVEL 6 |      |
 C    | ENGLISH LEVEL 8 | C    | computer LEVEL 3
 D    | ENGLISH LEVEL 6 |      |

rigth join

 SELECT * FROM english_cert right join computer_cert 
	on english_cert.name = computer_cert.name;
----执行结果
 name |      cert       | name |       cert
------+-----------------+------+------------------
 A    | ENGLISH LEVEL 4 | A    | computer LEVEL 2
      |                 | F    | computer LEVEL 2
 C    | ENGLISH LEVEL 8 | C    | computer LEVEL 3
      |                 | E    | computer LEVEL 3
(4 rows)

full join

 SELECT * FROM english_cert full join computer_cert 
	on english_cert.name = computer_cert.name;
----执行结果
 name |      cert       | name |       cert
------+-----------------+------+------------------
 A    | ENGLISH LEVEL 4 | A    | computer LEVEL 2
 B    | ENGLISH LEVEL 6 |      |
 C    | ENGLISH LEVEL 8 | C    | computer LEVEL 3
 D    | ENGLISH LEVEL 6 |      |
      |                 | F    | computer LEVEL 2
      |                 | E    | computer LEVEL 3

cross join

交叉连接不需要“on”关键字。

SELECT * FROM english_cert cross join computer_cert;
----执行结果
  name |      cert       | name |       cert
------+-----------------+------+------------------
 A    | ENGLISH LEVEL 4 | A    | computer LEVEL 2
 A    | ENGLISH LEVEL 4 | F    | computer LEVEL 2
 A    | ENGLISH LEVEL 4 | C    | computer LEVEL 3
 A    | ENGLISH LEVEL 4 | E    | computer LEVEL 3
 B    | ENGLISH LEVEL 6 | A    | computer LEVEL 2
 B    | ENGLISH LEVEL 6 | F    | computer LEVEL 2
 B    | ENGLISH LEVEL 6 | C    | computer LEVEL 3
 B    | ENGLISH LEVEL 6 | E    | computer LEVEL 3
 C    | ENGLISH LEVEL 8 | A    | computer LEVEL 2
 C    | ENGLISH LEVEL 8 | F    | computer LEVEL 2
 C    | ENGLISH LEVEL 8 | C    | computer LEVEL 3
 C    | ENGLISH LEVEL 8 | E    | computer LEVEL 3
 D    | ENGLISH LEVEL 6 | A    | computer LEVEL 2
 D    | ENGLISH LEVEL 6 | F    | computer LEVEL 2
 D    | ENGLISH LEVEL 6 | C    | computer LEVEL 3
 D    | ENGLISH LEVEL 6 | E    | computer LEVEL 3
(16 rows)

三. union简单应用

SELECT name from computer_cert
	union
	SELECT name from english_cert;
---执行结果 
name
------
 D
 B
 A
 E
 C
 F
(6 rows)

----union all 关键字不去重
qianbase=# SELECT name from computer_cert
qianbase-# union ALL
qianbase-# SELECT name from english_cert;
 name
------
 A
 F
 C
 E
 A
 B
 C
 D
(8 rows)

参考链接： https://www.runoob.com/w3cnote/sql-join-image-explain.html