将两个表或者两个以上的表以一定的连接条件连接起来，从中检索出满足条件的数据。

内连接

使用inner join，inner可以省略

-- 查询员工的姓名和部门名称
select "E".ename as "员工姓名", "D".dname as "部门名称"
	from emp "E"
	join dept "D"
	on "E".deptno = "D".deptno
select … from A, B

假设A表有sql server中如何查看当前连接数据加用户 sql server 连接查询_左外连接行，则行可以表示为集合sql server中如何查看当前连接数据加用户 sql server 连接查询_SQL_02
假设B表有sql server中如何查看当前连接数据加用户 sql server 连接查询_左外连接_03行，则行可以表示为集合sql server中如何查看当前连接数据加用户 sql server 连接查询_子查询_04
则select ... from A,B就是将两个表的行进行笛卡尔成绩，并将两个行进行合并得到sql server中如何查看当前连接数据加用户 sql server 连接查询_左外连接_05，因此总共的行数是sql server中如何查看当前连接数据加用户 sql server 连接查询_左外连接_06，总共的列数是两个表列数相加

即把A表的每一条记录都和B表的每一条记录组合在一起

select … from A,B where …

对上面的表用where的条件进行过滤

select E.ename as "员工姓名", D.dname as "部门名称"
	from emp as "E", dept as "D"
	where E.deptno = D.deptno
select … from A join B on …

join是连接的意思 on 表示连接条件
如果使用join就必须使用on

select * 
	from emp
	join dept
	on 1=1	--70*11
	
select emp.ename as '员工姓名', dept.deptno as '部门编号'
	from emp
	join dept
	on 1 = 1 --70*2
	
select emp.ename as '员工姓名', dept.deptno as '部门编号'
	from emp
	join dept
	on emp.deptno=dept.deptno --14*2

select E.ename as '员工姓名', D.deptno as '部门编号'
	from emp as "E"
	join dept as "D"
	on E.deptno=D.deptno --14*2
select *
	from emp as "E"
	join dept as "D"
	on 1 =1 
	
select *
	from dept as "D"
	join emp as "E"
	on 1 =1 
	order by D.deptno
	
select * from dept,emp
where dept.deptno = emp.deptno
--实际中发现无论将哪个表放在前面，总是用行数少的表匹配行数多的

from和join后面可以使用别名，如果在这里使用别名，其他的地方也都必须使用别名。区别于select后面的别名不能在其他地方使用，我认为根本原因在于语句的执行顺序

实际上和select ... from A,B where ...等价，推荐使用join on

使用join on可以再使用where对得到的数据过滤，从而实现不同的分工

混合使用
select * from emp as "E", dept as "D"
where E.deptno=D.deptno and E.sal>2000
--等价于下面的写法，下面的写法更加清晰
select * from emp as "E"
	join dept as "D"
	on E.deptno = D.deptno
	where E.sal > 2000

--求出工资大于2000的员工的姓名 部门编号 薪水 薪水等级
select emp.ename as "员工姓名", dept.dname as "部门名称", emp.sal as "薪水", SALGRADE.GRADE as "薪水等级"
	from emp,dept,SALGRADE
	where emp.deptno=dept.deptno and emp.sal>2000 and emp.sal >= SALGRADE.LOSAL and emp.sal <=SALGRADE.HISAL

select  emp.ename as "员工姓名", dept.dname as "部门名称", emp.sal as "薪水", SALGRADE.GRADE as "薪水等级"
	from emp
	join dept
	on emp.deptno=dept.deptno
	join SALGRADE
	on emp.sal>=SALGRADE.LOSAL and emp.sal<=SALGRADE.HISAL
	where emp.sal>2000

我们也可以把查询的表当作一个表，进行子查询

-- 输出部门名称,该部门所有员工的平均工资 平均工资等级

select dept.dname as "部门名称", tmp.avg_sal as "平均工资", SALGRADE.GRADE as "平均工资等级"
	from(
	select emp.deptno as "dept_no", AVG(emp.sal) as "avg_sal"
		from emp
		group by emp.deptno) "tmp"
	join dept
	on dept.deptno = tmp.dept_no
	join SALGRADE
	on tmp.avg_sal between SALGRADE.LOSAL and SALGRADE.HISAL

语句顺序

SELECT ...
	INTO
	FROM
	JOIN
	ON
	WHERE
	GROUP BY
	HAVING
	ORDER BY
-- 输出3个姓名中不含有O的工资最高的员工的姓名、工资、工资等级、部门名称

select top 3 emp.ename as "员工姓名", emp.sal as "员工工资", SALGRADE.GRADE as "工资等级", dept.dname as "部门名称"
	from emp
	join dept
	on emp.deptno=dept.deptno
	join SALGRADE
	on emp.sal >= SALGRADE.LOSAL and emp.sal <= SALGRADE.HISAL
	where emp.ename not like '%O%'
	order by emp.sal desc

当null和not in在一起的时候需要注意。如果表中有null，则使用not in的时候返回的总为空。

这与SQL的比较机制有关。在SQL中比较结果分为true``false``null，只有结果为true的时候系统才认为匹配成功并返回记录，in的本质是等于的or，not in的本质是不等于的and。

比较结果

and null

or null

true

null

true

false

false

null

null

null

null

当使用in的时候因为是or进行连接，所以可以正常返回true，在not in的时候是and连接，因此返回总为null，因此返回为空。

详细原因可以看这篇文章：传送门。为了解决这个问题我们可以使用is [not] null和isnull()函数组合判断

--求出emp表中所有领导的姓名

select distinct E1.ename as "领导姓名"
	from emp "E1"
	join emp "E2"
	on  E1.EMPNO = E2.mgr
	
select emp.ename as "领导姓名"
	from emp
	where emp.EMPNO in (select distinct mgr from emp)
	
--输出所有非领导的信息


select *
	from emp
	where emp.EMPNO not in (select distinct mgr from emp where mgr is not null)
--求出平均薪水最高的部门的名称和部门平均工资

select dept.dname as "部门名称", tmp.avg_sal as "平均工资"
	from (select top 1 emp.deptno as "dept_no", AVG(emp.sal) as avg_sal
		from emp
		group by emp.deptno
		order by AVG(emp.sal) desc) "tmp"
	join dept
	on tmp.dept_no=dept.deptno

当子查询的值只有一个的时候可以将子查询放在表达式中

--工资大于 所有员工中工资最低的人中的工资 的人中
--前三个人的姓名 工资 部门编号 部门名称 工资等级
select top 3 emp.ename as "姓名", emp.sal as "工资", emp.deptno as "部门编号", dept.dname as "部门名称", SALGRADE.GRADE as "工资等级"
	from emp
	join (select MIN(sal) as "min_sal" from emp) as "tmp"
	on emp.sal > tmp.min_sal
	join dept 
	on emp.deptno = dept.deptno
	join SALGRADE
	on emp.sal between SALGRADE.LOSAL and SALGRADE.HISAL
	order by emp.sal 

select top 3 tmp.ename as "姓名", tmp.sal as "工资", tmp.deptno as "部门编号", dept.dname as "部门名称", SALGRADE.GRADE as "工资等级"
	from ( select ename,sal,deptno from emp where sal > (select MIN(sal) as "min_sal" from emp)) as "tmp"
	join dept  
	on tmp.deptno = dept.deptno
	join SALGRADE
	on tmp.sal between SALGRADE.LOSAL and SALGRADE.HISAL
	order by tmp.sal
--把工资大于1500的所有员工按部门分组，
--按升序输出最后两个平均工资小于3000的部门名称，人数，平均工资，平均工资水平
select dept.dname as "部门名称", tmp.number as "部门人数", tmp.avg_sal as "平均工资", SALGRADE.GRADE as "平均给工资水平"
	from(
	select top 2 deptno as "dept_no", COUNT(*) as "number", AVG(sal) as "avg_sal"
		from emp 
		where sal>1500 
		group by deptno
		having AVG(sal)<3000
		order by AVG(sal) desc) "tmp"
	join dept
	on tmp.dept_no=dept.deptno
	join SALGRADE
	on tmp.avg_sal between SALGRADE.LOSAL and SALGRADE.HISAL
	order by tmp.avg_sal

order by的顺序应该在最后，因此可以用别名。group by和having都不可以用别名

外连接

不但返回满足条件的所有记录，而且会返回部门不满足条件的记录。

左外连接
select * from emp
	left join dept
	on emp.deptno=dept.deptno
  • 用左表的一行分别和右表的所有行进行连接，如果没有匹配的行，则一起输出，如果右表有多行匹配，则结果输出多行。如果没有匹配行，则结果只输出一行，该输出左边为左表的一行的内容，右边全部输出null
  • 因为右边很可能出现有多行和左表的某一行匹配，所以左连接产生的结果集的行数很可能大于左边表的行数
select * from dept
	left join emp
	on dept.deptno=emp.deptno	--16行

返回一个事物和该事物的相关信息，如果没有相关信息，就输出空

右外连接

同左外连接

完全连接

full join

  • 两个表中匹配的所有行记录
  • 左表中那些在右表找不到匹配的行的记录，右边为NULL
  • 右表中那些在左表找不到匹配的行的记录，左边为NULL

交叉连接

cross join等价于join on 1=1，后面不用加on

自连接

一张表和自己连接起来，注意连接自己的时候需要标明是哪一张表中的字段

--求薪水最高的员工的信息

select *
	from emp
	where sal = (select MAX(sal) from emp)

-- 不准用聚合函数，求薪水最高的员工的信息
select *
	from emp
	join (select top 1 EMPNO from emp order by sal desc) "tmp"
	on emp.EMPNO=tmp.EMPNO

select *
	from emp
	where sal not in (select distinct E1.sal
						from emp as "E1"
						join emp as "E2"
						on E1.sal < E2.sal)

联合

纵向连接表中的数据，即添加一行

--输出每个员工的姓名，工资，上司的姓名

select E1.ename as "姓名", E1.sal as "工资", E2.ename as "上司"
	from emp as "E1"
	left join emp as "E2"	--用左连接的原因是有一个没有上司
	on E1.mgr = E2.EMPNO
	
--或者使用联合
	
select E1.ename as "姓名", E1.sal as "工资", E2.ename as "上司"
	from emp as "E1"
	join emp as "E2"
	on E1.mgr = E2.EMPNO
union
select ename, sal, 'BOSS' from emp where  mgr is null

注意：

  • select子句输出列数相等
  • 数据类型也相同，至少是兼容的
分页查询