使用left join 查询后右表数据为空仍能查询出一条数据

在做查询时偶然发现的问题，简单记录一下！！

我的sql:

SELECT   a.*, GROUP_CONCAT( d.teacherName ) 'teacher_name', e.studentNum 'student_num' 
FROM   school_class a
LEFT JOIN (
    SELECT
        a.*, IFNULL( b.`name`, '' ) 'teacherName' 
    FROM class_teacher a  LEFT JOIN teacher b ON a.teacher_id = b.id 
    WHERE       a.school_class_elective_ids != '' 
    ) d ON LOCATE( a.id, d.school_class_elective_ids )
 LEFT JOIN (
    SELECT
        a.class_elective_id,
        a.school_session_text,
        IFNULL( COUNT( * ), 0 ) 'studentNum' 
    FROM
        class_student_access a 
    WHERE
        a.school_session_text >= '2018' 
    GROUP BY
        a.class_elective_id,
        a.school_session_text 
) e ON ( a.id = e.class_elective_id AND a.school_seesion_text = e.school_session_text ) 
WHERE    a.type = 'elective'    AND a.school_seesion_text >= '2018' 

LIMIT 0,20

执行结果:

出现问题原因：

group_concat()函数的问题

 解决方法：

把用到group_concat()函数的查询作为一个结果集进行左连接

如下:

SELECT
    a.*,
    d.teacherName 'teacher_name',
    e.studentNum 'student_num' 
FROM
    school_class a
    LEFT JOIN (
        SELECT a.id,GROUP_CONCAT(b.teacherName) 'teacherName' from school_class a 
        LEFT JOIN (
            SELECT b.*,t.`name` 'teacherName' from class_teacher b
            LEFT JOIN teacher t on b.teacher_id = t.id
        )b on LOCATE(a.id,b.school_class_elective_ids)
        WHERE a.type = 'elective'
            GROUP BY a.id
    ) d ON a.id = d.id
    LEFT JOIN (
    SELECT
        a.class_elective_id,
        a.school_session_text,
        IFNULL( COUNT( * ), 0 ) 'studentNum' 
    FROM
        class_student_access a 
    GROUP BY
        a.class_elective_id,
        a.school_session_text 
    ) e ON ( a.id = e.class_elective_id AND a.school_seesion_text = e.school_session_text ) 
WHERE
    a.type = 'elective' 
    AND a.school_seesion_text >= '2018' 
    LIMIT 0,20

执行结果如下:
        

 问题解决!!