mysql_leftjoin.txt
    sampdb.zip

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

    1. # create student table for grade-keeping project
    3. DROP TABLE IF EXISTS student;
    4. #@ _CREATE_TABLE_
    5. CREATE TABLE student
    6. (
    7.   name       VARCHAR(20) NOT NULL,
    8.   sex        ENUM('F','M') NOT NULL,
    9.   student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    10.   PRIMARY KEY (student_id)
    11. ) ENGINE=InnoDB;
    12. #@ _CREATE_TABLE_
    15. # create grade event table for grade-keeping project
    17. DROP TABLE IF EXISTS grade_event;
    18. #@ _CREATE_TABLE_
    19. CREATE TABLE grade_event
    20. (
    21.   date     DATE NOT NULL,
    22.   category ENUM('T','Q') NOT NULL,
    23.   event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    24.   PRIMARY KEY (event_id)
    25. ) ENGINE=InnoDB;
    26. #@ _CREATE_TABLE_
    29. # create score table for grade-keeping project
    31. # The PRIMARY KEY comprises two columns to prevent any combination
    32. # of event_id/student_id from appearing more than once.
    34. DROP TABLE IF EXISTS score;
    35. #@ _CREATE_TABLE_
    36. CREATE TABLE score
    37. (
    38.   student_id INT UNSIGNED NOT NULL,
    39.   event_id   INT UNSIGNED NOT NULL,
    40.   score      INT NOT NULL,
    41.   PRIMARY KEY (event_id, student_id),
    42.   INDEX (student_id),
    43.   FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
    44.   FOREIGN KEY (student_id) REFERENCES student (student_id)
    45. ) ENGINE=InnoDB;
    46. #@ _CREATE_TABLE_
    49. ##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;