连接查询（续）— SQL99语法

本文主要是介绍连接查询（续）— SQL99语法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

进阶6：连接查询（续）
- SQL99语法
- - （一）内连接
  - - 1、等值连接
    - 2、等值连接
    - 3、自连接
  - （二）外连接
  - （三）交叉连接（笛卡尔乘积）
测试：

进阶6：连接查询（续）

SQL99语法

语法：select 查询列表from 表1 别名 【连接类型】join 表2 别名on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序条件】分类：内连接：inner外连接：左外：left【outer】右外：right【outer】全外：full【outer】交叉连接：cross

（一）内连接

在这里插入图片描述

语法：

select 查询列表
from 表1 别名 
inner join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序条件】分类：等值非等值自连接特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在ON后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

1、等值连接

案例1 ：查询员工名、部门名

SELECT last_name,department_name 
FROMemployees e INNER JOIN departments d ON e.`department_id` = d.`department_id` ;

结果：
在这里插入图片描述
案例2：查询名字中包含e的员工名和工种名（添加筛选）

SELECT last_name,job_title 
FROMemployees e INNER JOIN jobs j ON e.`job_id` = j.`job_id` 
WHERE last_name LIKE "%e" ;

结果：
在这里插入图片描述
案例3：查询部门个数>3的城市名和部门个数，（添加分组+筛选）
分步骤：

查询每个城市的部门个数
在1结果上筛选满足条件的

SELECT city,COUNT(*) 部门个数 
FROMlocations l INNER JOIN departments d ON d.`location_id` = l.`location_id` 
GROUP BY city 
HAVING COUNT(*) > 3 ;

结果：
在这里插入图片描述
案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

SELECT department_name,COUNT(*) 员工个数 
FROMdepartments d INNER JOIN employees e ON d.`department_id` = e.`department_id` 
GROUP BY department_name 
HAVING COUNT(*) > 3 
ORDER BY COUNT(*) DESC ;

结果：
在这里插入图片描述
案例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name,department_name,job_title 
FROMemployees 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、等值连接

案例1：查询员工的工资级别

SELECT salary,grade_level 
FROMemployees e INNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` ;

结果：
在这里插入图片描述
案例2：查询工资级别的个数>20的个数，并且按工资级别降序

SELECT COUNT(*) 个数,grade_level 
FROMjob_grades j INNER JOIN employees e ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` 
GROUP BY grade_level 
HAVING COUNT(*) > 20 
ORDER BY grade_level DESC ;

结果：
在这里插入图片描述

3、自连接

案例1：查询员工的名字、上级的名字

SELECT e.`last_name`,m.`last_name` 
FROMemployees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;

结果：
在这里插入图片描述
案例2：查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.`last_name` 姓名,m.`last_name` 上级姓名 
FROMemployees e INNER JOIN `employees` m ON e.`manager_id` = m.`employee_id` 
WHERE e.`last_name` LIKE "%k%" ;

结果：
在这里插入图片描述

（二）外连接

在这里插入图片描述

应用场景：用于查询一个表中有，另一个表没有的记录
结果=表1能一一匹配表2-内容+表1不能不配表2-内容（为NULL）
特点：

外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示NULL
外连接查询结果=内连接结果+主表中有而从表没有的记录
左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
全外连接=内连接的结果(交集)+表1中有但表2没有的+表2中有但表1没有的

引入：查询男朋友不在男神表的的女神名（左外连接）

SELECT b.`name`,bo.* 
FROMbeauty b LEFT OUTER JOIN boys bo ON b.`boyfriend_id` = bo.`id`

结果：
在这里插入图片描述
案例1：查询哪个部门没有员工
（1）左外

SELECT d.*,e.employee_id 
FROMdepartments d LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

结果：
在这里插入图片描述
（2）右外

SELECT d.*,e.employee_id 
FROMemployees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

结果和上面的结果一样

（3）全外(不支持)

USE girls ;SELECT b.*,bo.* 
FROMbeauty b FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id ;

（三）交叉连接（笛卡尔乘积）

在这里插入图片描述

SELECT b.*,bo.* 
FROMbeauty b CROSS JOIN boys bo ;

测试：

1、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用NULL填充

SELECT b.`id`,b.`name`,bo.* 
FROMbeauty b LEFT OUTER JOIN boys bo ON b.`boyfriend_id` = bo.`id` 
WHERE b.`id` > 3 ;

结果：
在这里插入图片描述
2、查询哪个城市没有部门

SELECT city,d.* 
FROMdepartments d RIGHT OUTER JOIN locations l ON d.`location_id` = l.`location_id` 
WHERE d.`department_id` IS NULL ;

结果：
在这里插入图片描述
3、查询部门名为SAL或IT的员工信息

SELECT d.department_name,e.* 
FROMdepartments d LEFT JOIN employees e ON d.`department_id` = e.`department_id` 
WHERE d.`department_name` IN('SAL','IT');