SQL教程——连接查询之SQL99标准

冷不防 2022-12-25 03:57 255阅读 0赞

> 本教程中所使用的数据库的建表语句都在“SQL教程——索引”这篇文章中，点击链接直达：[索引&建表语句][Link 1]

> 摘要：本文主要介绍SQL99标准的连接查询

# SQL99标准 #

**语法：**

select 查询列表

from 表1 别名 【链接类型】

join   表2 别名

on 链接条件

【where 筛选条件】

【group by 分组】

【having 筛选条件】

【order by 排序】

**分类：**

1.  内连接（※）inner
2.  外连接
    
    1.  左外（※）：left【outer】
    2.  右外（※）：right【outer】
    3.  全外：full【outer】
3.  交叉连接：cross

### 1、内连接 ###

语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 链接条件

分类：

1.  等值连接
2.  非等值连接
3.  自连接

特点：

*  添加排序、分组、筛选。
 *  inner可以省略。
 *  筛选条件放在where后面，连接诶条件放在on后面，提高分离性，便于阅读。
 *  inner join链接和sql92语法中的的等值连接效果是一样的。

\#1、等值连接

#案例1：查询员工名、部门名
    
    select last_name, department_name
    
    from employees e
    
    inner join departments d
    
    on e.department_id = d.department_id;
    
    
    
    #2、查询名字中包含e的员工名和工种名（添加筛选）
    
    select last_name, job_title
    
    from employees e
    
    inner join jobs j
    
    on j.job_id
    
    where e.last_name like '%e%';
    
    
    
    #3、查询部门个数>3的城市名和部门个数，（分组+筛选）
    
    select city, count(*)
    
    from departments d
    
    inner join locations l
    
    on d.location_id= l.location_id
    
    group by city
    
    having count(*) > 3;
    
    
    
    #4、查询哪个部门的员工个数>3的部门名和员工个数，并按照个数降序（添加排序）
    
    select department_name, count(*)
    
    from employees e
    
    inner join departments d
    
    on e.department_id = d.department_id
    
    group by department_name
    
    having count(*) > 3
    
    order by count(*) desc;
    
    
    
    #5、查询员工名、部门名、工种名，并按部门名降序
    
    select last_name, department_name, job_title
    
    from employees e
    
    inner join departments d on e.department_id = d.department_id
    
    inner join jobs j on e.job_id = j.job_id
    
    order by department_name desc;

\#2、非等值连接

#查询员工的工资级别
    
    select last_name, grade_level
    
    from employees e
    
    join job_grades j
    
    on e.salary between j.lowest_sal and j.highest_sal;
    
    #查询员工工资级别个数>2的级别个数，并按工资级别排序
    
    select count(*), grade_level
    
    from employees e
    
    join job_grades j
    
    on e.salary between j.lowest_sal and j.highest_sal
    
    group by grade_level
    
    having count(*) > 2
    
    order by grade_level desc;

\#3、自连接

#查询员工的名字、上级的名字
    
    select e.last_name 员工, m.last_name 上级员工
    
    from employees e
    
    inner join employees m
    
    on e.manager_id = m.employee_id;

## ##

### 2、外连接 ###

应用场景：用于查询一个表中有，另一个表中没有的情况

**特点：**

1.  外连接查询结果为主表中的所有记录
    
    如果从表中有和它匹配的记录，则显示匹配的
    
    如果从表中没有和它匹配的记录，则显示null值
    
    左右外链接查询结果 = 内连接结果 + 主表中有而从表没有的记录
    
    全外连接不分主表从表，它的查询结果包括两表交集+A表有B表没有的部分+B表有A表没有的部分
2.  左外连接，left join左边是主表
    
    右外链接，right join右边的是主表
3.  左外和右外交换两个表的顺序，可以实现同样的效果

#引入：查询男朋友不在男神表的女神名
    
    #于是你可能会想到用嵌套查询：
    
    select name
    
    from beauty g
    
    where boyfriend_id not in
    
    (select id from boys);
    
    
    
    但如果不让用嵌套呢？
    
    两种方法：
    
    左外：
    
    select b.name, bo.*
    
    from beauty b
    
    left outer join boys bo
    
    on b.boyfriend_id = bo.id
    
    where bo.id is null;
    
    右外：
    
    select b.name, bo.*
    
    from boys bo
    
    right outer join beauty b
    
    on b.boyfriend_id = bo.id
    
    where bo.id is null;
    
    
    
    #案例1：查询哪个部门没有员工
    
    #右外
    
    select d.*, e.employee_id
    
    from employees e
    
    right outer join departments d
    
    on e.department_id = d.department_id
    
    where e.employee_id is null;
    
    #左外
    
    select d.*, e.employee_id
    
    from departments d
    
    left outer join employees e
    
    on e.department_id = d.department_id
    
    where e.employee_id is null;
    
    #全外（mysql不支持全外连接）
    
    select b.*, bo.*
    
    from beauty b
    
    full outer join boys bo
    
    on b.boyfriend_id = bo.id;

### 3、交叉连接（笛卡尔乘） ###

select b.*, bo.*
    
    from beauty b
    
    cross join boys bo;

### 小结： ###

SQL92 VS SQL99

*  功能：SQL99支持的较多
 *  可读性：SQL99实现连接条件和筛选条件的分离，可读性较高

#Test
    
    #案例1：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
    
    select b.name, bo.*
    
    from beauty b
    
    left outer join boys bo
    
    on b.boyfriend_id = bo.id
    
    where b.id > 3;
    
    
    
    #案例2：查询哪个城市没有部门
    
    select city, department_name
    
    from locations l
    
    left outer join departments d
    
    on l.location_id = d.location_id
    
    where department_id is null;
    
    
    
    #案例3：查询部门名为SAL或IT的员工信息
    
    select d.department_name, e.*
    
    from employees e
    
    join departments d
    
    on e.department_id = d.department_id
    
    where d.department_name in ('SAL', 'IT');

[Link 1]: https://blog.csdn.net/IT_TIfarmer/article/details/110382794