2021-06-29

联表查询

联表查询
LEFT JOIN INNER JOIN RIGHT join三张表
查询参加考试的学生

1. 字段来源
2. 7种连接查询的哪一种
3. 3.确定交叉点(两张表的相同字段)

1.inner JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno
2. RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
3. LEFT JOIN
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

操作
inner join	利用笛卡尔积形成一张虚表，根据条件只保留匹配行。
right joi	右表所有行，在左表中没有匹配项，用NULL代替。
left join	左表所有行，在右表中没有匹配项，用NULL代替。

查询缺考同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL
三表连接student,result,SUBJECT
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno