左连接demo

mysql_leftjoin.txt
sampdb.zip
我们有一个记录学生的student表，有一个记录已发生考试或测验事件的grade_event表，还有一个记录每位学生的每次考试成绩的score表。现在查找缺考的学生。

# create student table for grade-keeping project
DROP TABLE IF EXISTS student;
#@ _CREATE_TABLE_
CREATE TABLE student
(
  name       VARCHAR(20) NOT NULL,
  sex        ENUM('F','M') NOT NULL,
  student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (student_id)
) ENGINE=InnoDB;
#@ _CREATE_TABLE_
# create grade event table for grade-keeping project
DROP TABLE IF EXISTS grade_event;
#@ _CREATE_TABLE_
CREATE TABLE grade_event
(
  date     DATE NOT NULL,
  category ENUM('T','Q') NOT NULL,
  event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (event_id)
) ENGINE=InnoDB;
#@ _CREATE_TABLE_
# create score table for grade-keeping project
# The PRIMARY KEY comprises two columns to prevent any combination
# of event_id/student_id from appearing more than once.
DROP TABLE IF EXISTS score;
#@ _CREATE_TABLE_
CREATE TABLE score
(
  student_id INT UNSIGNED NOT NULL,
  event_id   INT UNSIGNED NOT NULL,
  score      INT NOT NULL,
  PRIMARY KEY (event_id, student_id),
  INDEX (student_id),
  FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
  FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE=InnoDB;
#@ _CREATE_TABLE_
##insert 语句见附件

要解决的问题是：确定哪些学生在某次考试或测验当天缺考了。也就是说 我们要查找学生和事件的组合没有在score表里出现过。
第一步：确定“学生＋事件”的组合

from student inner join grade_event

第二步：“学生+事件”组合结果集，与score表一起执行left join操作

from student inner join grade_event
    LEFT JOIN   score
        ON  student.student_id=score.student_id
        AND grade_event.event_id =score.event_id

第三步：将缺失的列筛选出来

where score.score is null

最终SQL为：

SELECT
    student.name,
    student.student_id,
    grade_event.date,
    grade_event.event_id,
    grade_event.category
FROM
    student
INNER JOIN
    grade_event
LEFT JOIN
    score
ON
    student.student_id=score.student_id
AND grade_event.event_id =score.event_id
WHERE
    score.score IS NULL
ORDER BY
    student.student_id,
    grade_event.event_id;