sql 内连接

连接查询

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

分类

• 内连接
• 外连接
• 完全连接
• 交叉连接
• 自连接
• 联合

内连接

inner join 其中 inner 可省略。

1. select ... from A,B 的用法
2. select ... from A,B where ... 的用法
3. select ... from A join B on ... 的用法
4. select ... from A,B where ... 与 select ... from A join B on ...比较
5. select、from、where、join、on、group、order、top、having 的混合使用

练习：

1. 求出每个员工的姓名、部门编号、薪水和薪水等级。
2. 查找每个部门的编号、该部门所有员工平均工资、平均工资等级。
3. 求出 emp 表中所有领导的信息。
4. 求出平均薪水最高的部门编号和部门平均工资。
5. 去掉工资最低的，把剩下的工资最低前3人的姓名、工资、部门编号、部门名称、工资等级输出

emp 员工表：

emp 全部列：

emp.png

emp 全部行：

emp.png

dept 部门表：

dept.png

salGrade 工资等级表：

salGrade.png

1、select ... from A,B

emp 表 14 行 8列，dept 表 5 行 3 列：

select * from emp,dept

输出结果 70 行 11 列，行数是两个表行数的乘积，列是两个表的列数相加之和。

将 emp 表中的每一条记录和 dept 表中每一条记录连接起来，同理 dept 中的每一条记录和 emp 表中的每一条记录连接起来。即 emp 表中第一条记录和 dept 表中的 5 条记录连接，依次第2条...第14条记录和 dept 表中的 5 条记录连接，形成 14 * 5 = 70 行。

2、select ... from A,B where ... 的用法

select * from emp,dept wher empno = 7369;

• 输出 5 行
• 对 select ... from A,B 产生的结果进行过滤。

3、select ... from A join B on ... 的用法

输出 70 行 2 列

select "E".ename "员工姓名"，“D”.dname
    from emp "E"
    join dept "D" --join 是连接
    on 1=1 -- on 连接条件，有 join 就必须有 on

deptno 字段在两个表中都有：

-- 下面错误写法：
select deptno  --error，不清楚 deptno 在哪个表中
    from emp "E"
    join dept "D"
    on 1=1

-- 下面正确写法：
select "E".deptno
    from emp "E"
    join dept "D"
    on 1=1

输出 14 行 2列：

select "E".ename "员工姓名"，“D”.dname "部门名称"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno 

4、select ... from A,B where ... 与 select ... from A join B on ... 区别

select * 
    from emp,dept
    where emp.deptno = dept.deptno

等价于

select *
    from emp
    join dept
    on emp.deptno = dept.deptno

• select ... from A,B where ...：sql92 标准
• select ... from A join B on ...：sql99 标准

输出结果是一样的，推荐使用第二种 join on。

例1：把工资大于 2000 的员工的姓名和部门的名称输出。

sql92 的实现方式：

select E.ename "员工姓名","D".dname "部门名称"
    from emp "E",dept "D"
    where "E".sal > 2000 and "E".deptno = "D".deptno

这种方式将表的链接和工资过滤放到了一起。

sql99 的实现方式：

select E.ename "员工姓名","D".dname "部门名称"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    where "E".sal > 2000 

第二种方式逻辑更清晰，先链接表，在过滤。

例2：把工资大于 2000 的员工的姓名、部门的名称和工资等级输出。

sql99 标准：

select "E".ename "员工姓名","D".dname "部门名称","S".grade "工资等级"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno,
    join salgrade "S"
    on "E".sal >= "S".losal and "E".sal <= "S".hisal
    where "E".sal > 2000

sql92 标准：

select "E".ename "员工姓名","D".dname "部门名称","S".grade "工资等级"
    from emp "E",dept "D",salgrade "S"
    where  "E".deptno = "D".deptno and ("E".sal >= "S".losal and "E".sal <= "S".hisal ) and "E".sal > 2000

例三：

select * 
    from emp,dept
    where dept.deptno = 10    -- 过滤条件

等价于

select *
    from emp
    join dept
    on 1=1    -- 链接条件
    where dept.deptno = 10    -- 过滤条件

5、select、from、where、join、on、group、order、top、having 的混合使用

1. 输出工资最高的前三名每个员工的姓名，工资，工资等级，部门名称:

select top 3 "E".ename,"E".sal "工资","S".grade "工资等级","D".dname "部门名称"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    join salgrade "S"
    on "E".sal between "S".losal and "S".hisal
    order by "E".sal desc

2. 输出姓名不包含A的员工工资最高的前三名每个员工的姓名，工资，工资等级，部门名称:

select top 3 "E".ename,"E".sal "工资","S".grade "工资等级","D".dname "部门名称"
    from emp "E"
    join dept "D"
    on "E".deptno = "D".deptno
    join salgrade "S"
    on "E".sal between "S".losal and "S".hisal
    where "E".ename not like '%A%'
    order by "E".sal desc

练习

1、求出每个员工的姓名、部门编号、薪水和薪水等级。

select "E".ename "员工姓名","E".deptno "部门编号","E".sal "薪水","S".grade "薪水等级"
    from emp "E"
    join salgrade "S"
    on "E".sal between "S".losal and "S".hisal

2、查找每个部门的编号、该部门所有员工平均工资、平均工资等级。

select "T".deptno "部门编号","T".avg_sal as "平均工资","S".grade "工资等级"
    from(
        select deptno,avg(sal) as "avg_sal"
            from emp 
            group by deptno
    ) as "T"
    join salgrade "S"
    on "T".avg_sal between "S".losal and "S".hisal

3、求出 emp 表中所有领导的信息

select * from emp
    where empno in (select mgr from emp)  -- 过滤出员工编号在领导集合编号中即可。

4、求出平均薪水最高的部门编号和部门平均工资。

select top 1 detpno,avg(sal) "avg_sal"
    from emp
    group by deptno
    order by avg(sal) desc

5、去掉工资最低的，把剩下的工资最低前3人的姓名、工资、部门编号、部门名称、工资等级输出。

select top 3 "E".ename "姓名","E".sal "工资","E".deptno "部门编号","D".name "部门名称","S".grade "工资等级"
    from (
        select * 
            from emp 
            where sal > (select min(sal) from emp)   -- 去掉最低工资的员工
    ) "E"
    join dept "D"
    on "E".deptno = "D".deptno
    join salgrade "S"
    on "E".sal between "S".losal and "S".hisal
    order by "E".sal asc