一.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