一篇搞懂 SQL Join

0、数据准备 tableA tableB

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

1、取交集

Select * FROM tableA
JOIN tableB
ON tableA.name = tableB.name 

2、取左全集，没有匹配的用 null 代替

SELECT * FROM tableA
left JOIN tableB
on tableA.name=tableB.name

3、取右全集，没有匹配的用 null 代替

Select * FROM tableA
RIGHT JOIN tableB
ON tableA.name = tableB.name 

4、取并集,由于 mysql 没有 full join 只有使用 UNION

SELECT * FROM tableA
RIGHT JOIN tableB
on tableA.name=tableB.name
UNION
SELECT * FROM tableA
LEFT JOIN tableB
on tableA.name=tableB.name

5、取不交叉的数据

SELECT * FROM tableA
RIGHT JOIN tableB
ON tableA.name=tableB.name 
WHERE tableA.id IS NULL
UNION
SELECT * FROM tableA
LEFT JOIN tableB
ON tableA.name=tableB.name 
WHERE tableB.id IS NULL

6、笛卡尔积，笛卡尔乘积会产生 4 x 4 = 16 条

SELECT * FROM tableA JOIN tableB