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

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

摘要：本文主要介绍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');