MySQL中的集中关联方式

• 笛卡尔积

SELECT * FROM student_a a JOIN student_b b

• join 与 inner join

在关联查询后面使用ON条件后，join 与 inner join查询的结果是一致的；

1. SELECT * FROM student_a a JOIN student_b b ON a.id = b.id
2. SELECT * FROM student_a a INNER JOIN student_b b ON a.id = b.id

• 左关联查询与右关联查询

左关联查询与右关联查询效果上是一直的：table_a a left join table_b b == table_b b right join table_a；

SELECT * FROM student_a a LEFT JOIN student_b b ON a.id = b.id
SELECT * FROM student_b b LEFT JOIN student_a a ON b.id = a.id

SELECT * FROM student_a a RIGHT JOIN student_b b ON a.id = b.id
SELECT * FROM student_b b RIGHT JOIN student_a a ON b.id = a.id

• 独有数据查询

查询一个数据表中特殊的数据：table_a - (table_a && table_b)

SELECT * FROM student_a a LEFT JOIN student_b b ON a.id = b.id WHERE b.id IS NULL 
SELECT * FROM student_a a RIGHT JOIN student_b b ON a.id = b.id WHERE a.id IS NULL

• 全连接

Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接 + union + 右外连接实现

SELECT * FROM student_a a LEFT JOIN student_b b ON a.id = b.id
UNION 
SELECT * FROM student_a a RIGHT JOIN student_b b ON a.id = b.id

• 并集后去掉交集

SELECT * FROM student_a a LEFT JOIN student_b b ON a.id = b.id WHERE b.id IS NULL 
UNION 
SELECT * FROM student_a a RIGHT JOIN student_b b ON a.id = b.id WHERE a.id IS NULL