MySQL连接查询分为 内连接(inner join)、右外连接(right outer join)、左外连接(left outer join)、全外连接(full outer join), 其中无修饰的 join 默认为内连接,无修饰的 outer join 默认为全外连接。
- 内连接 inner join
inner 可省略, 内连接查询符合ON连接条件下,两表都存在的记录
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
3. 右外连接 right outer join
outer 可省略,ON连接条件下,右表(主表FROM)存在的记录
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;
5. 左外连接 left outer join
outer可省略, ON连接条件下,左表(主表FROM)存在的记录
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT JOIN classes c
ON s.class_id = c.id;
7. 全外连接 full outer join
full可省略, ON连接条件下,左右表(主表FROM)存在的记录
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;
参考文章:连接查询 - 廖雪峰的官方网站