mysql 左连接_MySQL中的几种关联方式

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.id2. 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.idSELECT * FROM student_b b LEFT JOIN student_a a ON b.id = a.idSELECT * FROM student_a a RIGHT JOIN student_b b ON a.id = b.idSELECT * 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.idUNION 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