数据库总结之八种常用join变化

1-7 图中圆各表示 A 表、B表中 对应的key的区间。
8 为两个表中所有字段合成一个表的字段，数据为两个表中所有数据的笛卡尔积形式。

1.内连接:
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

2.左连接：
SLEECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

3.右链接：
SELECT <select_list> FORM TableA A RIGHT JION TableB B ON A.Key = B.Kye;

4.左连接 右空
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

5.右连接 左空
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NUL

6.全链接：
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key

7.全连接 左为空或右为空
SELECT <select_list> FROM TableA A OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NUL OR B.Key IS NULL;

8.交叉连接 交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

SELECT * FROM TableA AS A CROSS JOIN TableB AS B ;

例如：
select * from book as a cross join stu as b order by a.id

a表中记录5条 ，字典为 id、bookname、sutid
b表中记录5条，字典为stuid，sutname
交叉连接后记录为 25条。