联表查询
联表查询
LEFT JOIN INNER JOIN RIGHT join三张表
查询参加考试的学生
- 字段来源
- 7种连接查询的哪一种
- 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