Left Join error

之前写链接查询的时候遇到了个小错误,记录下:

 SELECT *  FROM ilike_class_sign as s1

    right  join

(SELECT *  FROM ilike_student  where 1=1 AND name like CONCAT("%",1,"%")  and status =0)s2

    on

   s1.student_id = s2.id

    where cls_id = 1

这种写法是对的,右外连接查询是以右表为主题进行链接

 

换成 left join 错误写法

(SELECT *  FROM ilike_student  where 1=1 AND name like CONCAT("%",1,"%")  and status =0)s1

    left  join

 SELECT *  FROM ilike_class_sign as s2

    on

   s1.id = s2.student_id

    where cls_id = 1

 

正确写法

SELECT * FROM

(SELECT *  FROM ilike_student  where 1=1 AND name like CONCAT("%",1,"%")  and status =0)s1

    left  join

 SELECT *  FROM ilike_class_sign as s2

    on

   s1.id = s2.student_id

    where cls_id = 1

左外链接是已左表为基准 , 错误写法 只有s1的查询结果 没有查询条件(红色标注) ,所以会报错 !