左外连接的错误，左外，右外，内连接的区别

题目：查询小尹老师底下所有的学生

create database test;
use test;
create table stu(
    id bigint,
    classID bigint,
    name varchar(10)
    );
create table class(
    id bigint,
    teacherID bigint
    );
create table teacher(
    id  bigint,
    name varchar(10)
    );
    
 insert into stu values(1,2,'马哥');
 insert into class values(1,2);
 insert into teacher values(2,'小尹');
 update class set id = 1 where id = 2;

错误的因为左外连接尽量保存左表的数据 on的条件相当于没有

SELECT S.* FROM `stu` S LEFT JOIN class C on S.classID = C.id
LEFT JOIN teacher T on C.teacherID = T.id and T.name = "小尹";

正确的

SELECT S.* FROM `teacher` T LEFT JOIN class C on T.id = C.teacherID
LEFT JOIN stu S on S.classID = 2 and T.name = "小尹";

同时备注左外，右外，内连接的区别

SELECT * from users; -- 结果一
SELECT * from t_order; -- 结果二

SELECT u.id, o.* from users u LEFT JOIN t_order o on u.id = o.user_id; -- 结果三 

SELECT u.id, o.* from users u RIGHT JOIN t_order o on u.id = o.user_id; -- 结果四

SELECT u.id, o.* from users u INNER JOIN t_order o on u.id = o.user_id; -- 结果五

以上可以看出，左外连接保存左边全部的，右外连接保存右边全部的，内连接是保存双方的交集

左外连接的错误，左外，右外，内连接的区别

推荐阅读