SQL JOINS 的集中类型

CROSS JOIN

对集合进行笛卡尔积。

SELECT  
	A.a AS A_a,
	A.b A_b,
	B.a AS B_a,
	B.b B_b 
FROM 
	table_a A CROSS JOIN table_b B ;

 a_a | a_b | b_a | b_b 
-----+-----+-----+-----
   1 | 123 |   3 | 345
   1 | 123 |   2 | 234
   1 | 123 |   1 | 123
   1 | 123 |   7 | 789
   1 | 123 |   8 | 890
   1 | 123 |   9 | 999
   2 | 234 |   3 | 345
   2 | 234 |   2 | 234
   2 | 234 |   1 | 123
   2 | 234 |   7 | 789
   2 | 234 |   8 | 890
   2 | 234 |   9 | 999
   3 | 345 |   3 | 345
   3 | 345 |   2 | 234
   3 | 345 |   1 | 123
   3 | 345 |   7 | 789
   3 | 345 |   8 | 890
   3 | 345 |   9 | 999
   4 | 456 |   3 | 345
   4 | 456 |   2 | 234
   4 | 456 |   1 | 123
   4 | 456 |   7 | 789
   4 | 456 |   8 | 890
   4 | 456 |   9 | 999
   5 | 567 |   3 | 345
   5 | 567 |   2 | 234
   5 | 567 |   1 | 123
   5 | 567 |   7 | 789
   5 | 567 |   8 | 890
   5 | 567 |   9 | 999
   6 | 678 |   3 | 345
   6 | 678 |   2 | 234
   6 | 678 |   1 | 123
   6 | 678 |   7 | 789
   6 | 678 |   8 | 890
   6 | 678 |   9 | 999
(36 rows)

INNER JOIN(JOIN)

内连接即两个集合的交集。

SELECT  
	A.a AS A_a,
	A.b A_b,
	B.a AS B_a,
	B.b B_b 
FROM 
	table_a A INNER JOIN table_b B 
ON A.a = B.a;

 a_a | a_b | b_a | b_b 
-----+-----+-----+-----
   1 | 123 |   1 | 123
   2 | 234 |   2 | 234
   3 | 345 |   3 | 345
(3 rows)

LEFT JOIN（LEFT OUTER JOIN)

左连接是左边表的所有数据都显示出来，右边的只显示共有的部分。

SELECT  
	A.a AS A_a,
	A.b A_b,
	B.a AS B_a,
	B.b B_b 
FROM 
	table_a A LEFT JOIN  table_b B 
ON 
	A.a = B.a;

 a_a | a_b | b_a | b_b 
-----+-----+-----+-----
   1 | 123 |   1 | 123
   2 | 234 |   2 | 234
   3 | 345 |   3 | 345
   4 | 456 |     | 
   5 | 567 |     | 
   6 | 678 |     | 
(6 rows)

RIGHT JION (RIGHT OUTER JOIN)

右连接是右边表的所有数据都显示出来，左边的只显示共有的部分。

SELECT  
	A.a AS A_a,
	A.b A_b,
	B.a AS B_a,
	B.b B_b 
FROM 
	table_a A RIGHT JOIN  table_b B 
ON 
	A.a = B.a;

 a_a | a_b | b_a | b_b 
-----+-----+-----+-----
   3 | 345 |   3 | 345
   2 | 234 |   2 | 234
   1 | 123 |   1 | 123
     |     |   7 | 789
     |     |   8 | 890
     |     |   9 | 999
(6 rows)

FULL JOIN （FULL OUTER JOIN）

全连接就是指的是两个集合取并集。

SELECT  
	A.a AS A_a,
	A.b A_b,
	B.a AS B_a,
	B.b B_b 
FROM 
	table_a A FULL OUTER JOIN  table_b B 
ON 
	A.a = B.a;

 a_a | a_b | b_a | b_b 
-----+-----+-----+-----
   1 | 123 |   1 | 123
   2 | 234 |   2 | 234
   3 | 345 |   3 | 345
   4 | 456 |     | 
   5 | 567 |     | 
   6 | 678 |     | 
     |     |   8 | 890
     |     |   9 | 999
     |     |   7 | 789
(9 rows)