文章目录
建立相关表七大JOINInner JOINLeft JOINLeft Excluding JOINRight JOINRight Excluding JOINFULL Outer JOINFULL Outer Excluding JOIN本文将着重分析下图中联合查询的用法：
图片来源： 一张图看懂 SQL 的各种 JOIN 用法 。
建立相关表
在理解之前，我们首先建立相关的表，首先建立学生表：
create table student(stuNochar(5) primary key, -- 学生学号stuName varchar(32) not null, -- 学生姓名gender char(1) not nulldefault '男', -- 学生性别age int not null -- 学生年龄)charset='utf8';
之后插入数据：
-- 添加学生数据insert into student(stuNo,stuName,gender,age) values('001','李志','男',14);insert into student(stuNo,stuName,gender,age) values('002','宋东野','男',23);insert into student(stuNo,stuName,gender,age) values('003','赵雷','男',34);insert into student(stuNo,stuName,gender,age) values('004','马頔','男',32);insert into student(stuNo,stuName,gender,age) values('005','陈粒','女',18);insert into student(stuNo,stuName,gender,age) values('006','筠子','女',4);insert into student(stuNo,stuName,gender,age) values('007','寸铁','男',56);insert into student(stuNo,stuName,gender,age) values('008','狗毛','男',47);insert into student(stuNo,stuName,gender,age) values('009','崔健','男',39);insert into student(stuNo,stuName,gender,age) values('010','草东','男',40);insert into student(stuNo,stuName,gender,age) values('011','张悬','女',36);insert into student(stuNo,stuName,gender,age) values('012','撒娇','女',25);insert into student(stuNo,stuName,gender,age) values('013','宋东野','男',35);
再建立分数表，此处的分数表不严谨，因为并没有相关课程的信息，并且应该和学生表有一个外键关联，但为了简单的理解联合查询以及需要插入一个特殊的数据，此处就不弄那么复杂了：
create table score(id int not null AUTO_INCREMENT primary key,sNo char(5) not null , -- 教师号score double not null -- 成绩-- foreign key(sNo) references student(stuNo));
之后插入数据：
-- 添加成绩数据insert into score(sNo,score) values('001',56);insert into score(sNo,score) values('002',87);insert into score(sNo,score) values('004',92);insert into score(sNo,score) values('005',76);insert into score(sNo,score) values('007',90);insert into score(sNo,score) values('008',53);insert into score(sNo,score) values('010',67);insert into score(sNo,score) values('011',69);insert into score(sNo,score) values('012',78);insert into score(sNo,score) values('014',80); -- 此数据如果有外键关联则无法插入
此时就得到了学生表和分数表，删除重建命令如下：
-- 删除表drop table if exists score;drop table if exists student;
七大JOIN
现在我们假设学生表为A，分数表为B。
两张表的详细信息如下：
Inner JOIN
查询学生中参加考试的学生的分数
select s.stuNo, s.stuName, sc.score FROM student as s inner join score as sc on s.stuNo=sc.sNo ;
可得结果：
Left JOIN
查询所有学生的分数（包含缺考的）
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo order by s.stuNo ASC;
可得结果：
Left Excluding JOIN
查询缺考的学生
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo where sc.score is NULL order by s.stuNo ASC;
可得结果如下：
Right JOIN
查询所有分数对应的学生
select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo order by s.stuNo ASC;
可得结果如下：
Right Excluding JOIN
查询有分数但不在学生表的分数
select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo where s.stuNo is NULL order by s.stuNo ASC;
可得结果如下：
FULL Outer JOIN
我们需要注意， Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ union+右外连接实现。 UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
查询所有的学生和所有的分数
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNoUNIONselect s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo;
可得结果如下：
FULL Outer Excluding JOIN
查询分数为空的学生和学生为空的分数
select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo where sc.score is NULL UNIONselect s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo where s.stuNo is NULL ;
可得结果如下：
