mysql中的联表查询

联表查询

以三种基本查询为基础，可以分为七种查询，本文只讲三种基本查询
inner join 交叉集合，左右的表有匹配的值，才返回
left join 即使右表没有匹配，也会从左表返回值
right join 即使左表没有匹配，也会从右表返回值

多表联接需要一个一个进行联接，把大问题分解为小问题

-- 联表查询
-- 七种查询(基于以下三种)

SELECT * FROM student
SELECT * FROM result

-- 左联
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.`studentno` = r.`studentno`

-- 右联
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`

-- 交叉
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
ON s.`studentno` = r.`studentno`

-- 当需要多张表联查的时候，一步一步联系
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno