mysql-sql99标准-连接查询

sql99标准语法

select 查询列表
from 表名1 别名
【连接类型】 join
表名2  别名2
on  连接条件
【where 筛选条件】
【group by】分组
【having 】筛选条件
【order by】排序列表

连接类型

1、内连接 inner join
2、外连接

• 左外 left outer join
• 右外 right outer join
• 全外 full outer join
• 交叉连接 cross join

内连接

语法

select 查询列表
from 表名1   别名1
inner join 表名2  别名2
on  连接条件

等值连接举例
eg1 :查询员工名、部门名

SELECT e.last_name,d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

eg2:查询员工名字中包含e的员工名和工种名（筛选）

select last_name,job_title
from employees e
inner join jobs j
on e.`job_id`=j.`job_id`
where e.`last_name` like ('%e%');

eg3:查询部门个数>3的城市名和部门个数

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

eg4:查询哪个部门的员工个数>3的部门名和员工个数，并按照个数降序排列

SELECT department_name,COUNT(*)
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*)DESC;

eg5:查询员工名、部门名、工种名并按部门名降序

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;

内连接-等值连接特点

• 可以添加排序、分组、筛选
• inner可以省略不写
• 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
• inner join内连接与sql92语法中的等值连接实现的效果都是一样的，都是查询的多表的交集

非等值连接

eg：查询员工的工资级别

SELECT salary ,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

eg：查询工资级别的个数>20的个数，并且按工资级别降序

SELECT grade_level,COUNT(*)
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

自连接

eg：查询员工名及上级员工名

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`
WHERE e.last_name LIKE ('%k%');

外连接

应用场景：查询的是一个表中有，另一表中没有
特点：

• 外连接的查询结果为主表中所有的记录，如果主表中有与它匹配的，则显示匹配的值。如果表中没有与它匹配的，则显示null
• 外连接查询的结果=内连接结果+主表中有而从表中没有的记录
• 左外连接：left outer join 左边是主表
• 右外连接：right outer join 右边是主表
• 左边和右边交换两个表的顺序可实现同样的效果

eg：查询男朋友不在男神表中的女神名

USE girls;  //切换到girls库中

SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

eg:哪个部门没有员工 (左外连接)

select d.*,e.employee_id
from departments d
left outer join employees e
on d.department_id=e.department_id
where e.employee_id is  null;

eg:哪个部门没有员工 (右外连接)

SELECT d.*,e.employee_id
FROM employees e
right OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS  NULL;

全外连接

可以实现内连接的结果+表1中有的表2中没有的+表2中有的表1中没有
语法：

select 查询列表
from 表名1   别名1
full outer  join 表名2  别名2
on  连接条件

交叉连接

实现的就是笛卡尔积乘积的效果

select 查询列表
from 表1  别名1
cross join 表2 别名2；

sql92和sql99的异同点

1. sql99支持的功能比较多
2. sql99实现连接条件与筛选条件的分离，可读性较高
   
   

#查询哪个城市没有部门

SELECT city
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;

#查询部门名为SAL或IT的员工信息

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