sql server 三张表链接查询 sql server三张表格链接

背景：

1：学习B站数据库多表查询，解决一些实际问题

过程：

1：sqlserver2012中用到的数据库相关表格

--职工表
CREATE TABLE emp(
    empno        INT,
    ename        VARCHAR(50),
    job        VARCHAR(50),
    mgr        INT,
    hiredate    DATE,
    sal        DECIMAL(7,2),
    comm        DECIMAL(7,2),
    deptno        INT
) ;

--部门表
CREATE TABLE dept(
    deptno        INT,
    dname        VARCHAR(14),
    loc        VARCHAR(13)
);

CREATE TABLE salgrade
( grade INT,  -- 工资等级
  losal Float, -- 最低工资
  hisal Float 
); -- 最高工资
		


--插入数据到职工表
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

--插入数据到部门表
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

--插入数据到工资表
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

2：双表查询-内连接

/****** Script for SelectTopNRows command from SSMS  ******/
-- 多表查询：查询员工的员工编号、姓名、薪水和部门编号、部门名称

--  数据来源
--  经典的图

-- 查询员工的员工编号、姓名、薪水和部门编号
-- use dota1;
select empno,ename,sal,deptno from emp -- 14
select * from dept -- 4
-- 解决：将多条单标查询语句转换为一条多表查询语句
-- 1：交叉连接 都连接一次 14 * 4 笛卡尔积 无实际意义，有理论意义，用于明白查询底层的原理
select * from emp cross join dept order by empno -- 14 * 4 = 56
select * from dept cross join emp order by empno -- 14 * 4 = 56

--2：自然连接 natural sqlserver好像不支持自然连接 mysql是支持的
--2.1：（sqlserver执行未成功）
select * from emp natural join dept
-- 自动匹配所有的同名列（目前通名列只有deptno）,目前同名列只保留一份
-- 优点：简单（我觉得实际很少用）
--2.2：（sqlserver执行未成功）
select empno,ename,sal,comm,deptno,dname from emp natural join dept
--缺点：没有指明各列属于哪个表，效率低下
--解决：指定表名
--2.3 （sqlserver执行未成功）
select epm.empno,epm.ename,epm.sal,epm.comm,dept.deptno,dept.dname from emp natural join dept
--缺点:表名可能比较长
--解决：别名
--2.4（sqlserver执行未成功）
select e.empno,e.ename,e.sal,e.comm,d.deptno,d.dname 
from emp e 
natural join dept d

--3:using子句
--natural自然连接的缺点：会自动的按照（所有的）同名列进行匹配，
--如果希望只按照某个同名列匹配，怎么办？
--解决：using子句
--3.1 （sqlserver执行未成功，很奇怪！）
--这在mysql中相当于笛卡尔，只不过省略了cross
select e.empno,e.ename,e.sal,e.comm,e.deptno,d.deptno,d.dname 
from emp e
join dept d
--3.2 
select e.empno,e.ename,e.sal,e.comm,e.deptno,d.deptno,d.dname 
from emp e
join dept d using(deptno) --e.deptno=d.deptno
-- natural、using的缺点：必须有同名列，如果有主键、外键不同名，怎么办?
-- 解决：on子句

-- on子句
select e.empno,e.ename,e.sal,e.comm,e.deptno,d.deptno,d.dname 
from emp e
join dept d on (e.deptno = d.deptno)


-- 问题：到底使用哪种连接查询？
-- cross 不用 不知道如何写了，分析
-- natural、using：必须有同名列
-- on不管是否有同名列，均可使用，可读性强
-- 建议使用on子句


--内连接而已：只显示匹配的数据
--外连接：还可以显示全部或者部分不匹配的数据

3：双表查询-外连接

--内连接而已：只显示匹配的数据
select * from emp e 
join dept d
on (e.deptno=d.deptno) -- SCOTT没有部门，只需要关注这个
--外连接：还可以显示全部或者部分不匹配的数据
--左外连接 left outer join：除了显示匹配的数据，还可以显示左表的不匹配数据
select * from emp e 
left join dept d
on (e.deptno = d.deptno)--outer可以省略

--右外连接 right outer join：除了显示匹配的数据，还可以显示右表的不匹配数据
select * from emp e 
right join dept d
on (e.deptno = d.deptno)--outer可以省略

--全外连接 full outer join 除了显示匹配的数据，还可以显示左、右表的不匹配数据13+1+1
--mysql不支持全外连接 oracle、sqlserver支持
select * from emp e 
full join dept d
on (e.deptno=d.deptno)

--mysql变通的解决方案 666
select * from emp e 
left join dept d
on (e.deptno = d.deptno)
union -- 14 + 14 - 13 = 15 去重 效率低
select * from emp e 
right join dept d
on (e.deptno = d.deptno) --和全外连接一个效果 15


select * from emp e 
left join dept d
on (e.deptno = d.deptno)
union all -- 14 + 14 = 28 不去重 效率高
select * from emp e 
right join dept d
on (e.deptno = d.deptno)-- 28

--oracle可以轻松的实现交际、差集，mysql中没有提供直接支持
select * from emp e 
left join dept d
on (e.deptno = d.deptno)
minus -- sqlserver 
select * from emp e 
right join dept d
on (e.deptno = d.deptno)-- 28

4：三表查询

--三表连接查询
--查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级

--查询员工的编号、姓名、薪水、部门编号
select empno,ename,sal,deptno from emp

--查询员工的编号、姓名、薪水、部门编号、部门名称
--未显示SCOTT
select e.empno,e.ename,e.sal,d.deptno,d.dname 
from emp e 
join dept d 
on (e.deptno = d.deptno)  

--显示SCOTT
select e.empno,e.ename,e.sal,d.deptno,d.dname 
from emp e 
left join dept d 
on (e.deptno = d.deptno)  

--查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级

select * from salgrade

--mysql可以只写join,但是sqlserver中必须要写cross join方可以执行！！！
select e.empno,e.ename,e.sal,d.deptno,d.dname,sg.*
from emp e 
left join dept d 
on (e.deptno = d.deptno)  
cross join salgrade sg 
--加了on筛选条件后，sqlserver执行失败 这句话需要去掉cross 来实行内连接，和mysql区别还是很大！
select e.empno,e.ename,e.sal,d.deptno,d.dname,sg.*
from emp e 
left join dept d 
on (e.deptno = d.deptno)  
join salgrade sg 
on(e.sal between sg.losal and sg.hisal)
--部分字段
select e.empno,e.ename,e.sal,d.deptno,d.dname,sg.grade
from emp e 
left join dept d 
on (e.deptno = d.deptno)  
join salgrade sg 
on(e.sal between sg.losal and sg.hisal)
--
select e.empno,e.ename,e.sal,d.deptno,d.dname,sg.grade,t4.*
from emp e 
left join dept d 
on (e.deptno = d.deptno)  
join salgrade sg 
on(e.sal between sg.losal and sg.hisal)
join table4 t4
on(......)