©2020-2024 ioDraw All rights reserved,
Privacy Policy
数据库总结之八种常用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条。