LEFT JOIN与RIGHT JOIN学习笔记

--82
SELECT COUNT(*) FROM [tbiz_PuzzleBasic]

SELECT A.BasicID,A.Name,A.Gender,B.WorkID,B.Company,B.Position
FROM [tbiz_PuzzleBasic] A LEFT JOIN [tbiz_PuzzleWork] B ON B.BasicID = A.BasicID

读作:A表在B表的左边,A表的中的记录都将返回,A表是主表

FROM [tbiz_PuzzleBasic] A RIGHT JOIN [tbiz_PuzzleWork] B ON B.BasicID = A.BasicID
--读作A表在B表的右边,这时B表作为主表

小结:始终看两表在"现实"中按从左到右出现的先后顺序来决定"谁"是主表,

 

SELECT COUNT(*) FROM sheet1
-- 187241
SELECT COUNT(*) FROM sheet2
-- 182112

SELECT sheet1.账单编号 FROM sheet1 LEFT JOIN sheet2 ON sheet1.账单编号 = sheet2.账单编号
-- 记录数187241 账单编号为主键

SELECT sheet1.账单编号 FROM sheet1 RIGHT JOIN sheet2 ON sheet1.账单编号 = sheet2.账单编号
-- 记录数182112

INNER JOIN

SELECT sheet1.账单编号 FROM sheet1 INNER JOIN sheet2 ON sheet1.账单编号 = sheet2.账单编号
-- 记录数169589

INNER JOIN之后发现记录变少了, 原因在于INNER JOIN返回 on 条件的交集, 即不返回左表中在右表中不匹配的记录, 而LEFT JOIN 返回左边的全部记录

-- INNER JOIN 返回记录数 169589, 与下面SELECT 同过滤条件
SELECT 账单编号 FROM sheet1 WHERE 账单编号 IN (SELECT 账单编号 FROM sheet2)