连接查询（非等值查询、连接、子查询）

• 一、连接查询
• 1、传统模式的多表连接 等值连接(where)——非等值连接
• 非等值查询
• 2、sql99推出的标准，使用join关键字实现连接 内连接——外连接
• 内连接
• 外连接

• 1、单行子查询
• 2、多行子查询

一、连接查询

1、传统模式的多表连接 等值连接(where)——非等值连接

非等值查询

#案例：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.

salary

BETWEEN g.

lowest_sal

AND g.

highest_sal

AND e.

last_name

LIKE ‘__a_e%’;

2、sql99推出的标准，使用join关键字实现连接 内连接——外连接

#join连接
join关键字属于sql99语法的标准
1999推出了sql的一种新标准，使用到的关键字，叫做join，join连接,属于sql99语法
分类：
内连接：[inner] join on
外连接：
左外连接 left 【outer】 join on
右外连接 right 【outer】 join on
全外连接 full 【outer】 join on ，mysql中不支持！！！ sqlserver 和oracle 没有问题

内连接

语法：
select 字段1，字段2，…
from 表1
inner join 表2 on 连接条件;
特点：
① 效果和等值连接是一样的！
② 表的顺序可以调换
③ 可以为表起别名
④ 可以添加筛选、分组、排序
⑤ 将筛选条件和连接条件实现了分类，提高代码的可读性
⑥ n表连接，至少需要n-1 个连接条件
案例：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM departments d
JOIN employees e ON e.

department_id

=d.

department_id

WHERE commission_pct IS NOT NULL;

外连接

语法：
select 字段1，字段2，…
from 表1
left|right 【outer】 join 表2
on 连接条件
特点：
①查询结果：内连接的结果+主表中有但从表没有的记录（从表的字段用null填充）
②左连接，左边的就是主表； 右连接，右边的就是主表
③一般来讲要查询的字段来自于哪个表，那哪个表就是主表
④一般用于查询主表中有但从表中没有的记录
案例2：查询哪个城市没有部门（myemployees）
SELECT city,department_name
FROM locations l
LEFT OUTER JOIN departments d ON l.

location_id

=d.

location_id

WHERE d.

department_id

IS NULL;

3、全外连接和自连接

#全外连接
SELECT j.job_id,e.

employee_id

FROM employees e
FULL OUTER JOIN jobs j ON e.

job_id

=j.

job_id

#自连接（自己连接自己）
#案例：查询员工名和上级领导的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.

manager_id

=m.employee_id;
等价：
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.

manager_id

=m.employee_id;

二、子查询

概念：
嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询，类似于java中的内部类
语法 ：
select 字段1，字段2
from 表1
where Id in( select 字段1 from 表2)

**特点或者说注意事项： **
1、子查询放在小括号内
2、放在条件（where）右侧
3、子查询优先于主查询执行，先子再主

分类：
单行子查询：子查询的结果只有一个值，使用单行操作符（> < >= <= = <>）
多行子查询:使用多行操作符（any、all、in、not in）

1、单行子查询

#案例1：谁的工资比Abel高
#①查询Abel的工资
SELECT salary FROM employees
WHERE last_name = ‘Abel’

#②查询员工的信息满足工资>①的结果
SELECT * FROM employees
WHERE salary>( SELECT salary FROM employees WHERE last_name = ‘Abel’ );

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资
#①查询141的job_id
SELECT job_id FROM employees
WHERE employee_id=141

#②查询143的salary
SELECT salary FROM employees
WHERE employee_id=143

#③查询 姓名，job_id 和工资，满足job_id=①并且salary>②
SELECT last_name,job_id,salary FROM employees
WHERE job_id=( SELECT job_id FROM employees WHERE employee_id=141 )
AND salary>( SELECT salary FROM employees WHERE employee_id=143 );

2、多行子查询

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT department_id
FROM departments WHERE location_id IN(1400,1700)

#②查询department_id满足①结果的员工姓名
SELECT last_name FROM employees
WHERE department_id IN( SELECT department_id FROM departments WHERE location_id IN(1400,1700) )

#案例2：返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员工号、姓名、job_id 以及salary
#①查询job_id为‘IT_PROG’部门工资
SELECT salary FROM employees WHERE job_id = ‘IT_PROG’

#②返回其它部门中，工资<any ①的结果
SELECT last_name,employee_id,job_id,salary
FROM employees WHERE salary<( SELECT MAX(salary) FROM employees WHERE job_id = ‘IT_PROG’ )AND job_id<>‘IT_PROG’;

#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工#的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees WHERE salary<ALL( SELECT salary FROM employees WHERE job_id = ‘IT_PROG’ )
AND job_id<>‘IT_PROG’;