MySQL学习笔记之子查询

自连接方式

自连接就是表A连接表A，通过where关键字实现，比如查询工资比Abel高的员工信息：

SELECTe2.last_name,e2.salary
FROMemployees e1,employees e2
WHEREe1.last_name = "Abel"
AND e2.salary > e1.salary;

子查询

亦称为嵌套查询：

SELECTlast_name,salary
FROMemployees
WHEREsalary > (SELECTsalaryFROMemployeesWHERElast_name = "Abel");

子查询写在括号内，放在比较条件的右侧。单行操作符对应单行子查询，多行操作符对应多行子查询。

子查询分类

• 角度1：
  单行子查询：只返回一行结果的子查询；
  多行子查询：返回多行结果的子查询。

• 角度2：
  相关子查询：子查询用到了主查询表的字段；
  不相关子查询：子查询用不到主查询表的字段。

相关子查询

SELECTlast_name,salary,department_id
FROMemployees e1
WHEREsalary > (SELECTavg(salary)FROMemployees e2WHEREe1.`department_id` = e2.department_id);

也可以在from中声明子查询：

SELECTemployees.last_name,employees.salary,employees.department_id
FROMemployees,(SELECTdepartment_id,avg(salary)FROMemployeesGROUP BYdepartment_id) sal_dept
WHEREemployees.department_id = sal_dept.department_id;

再看一个例子：查询在job_history中出现两次的employee_id对应的last_name、employee_id和job_id：

SELECTemployees.last_name,employees.employee_id,employees.job_id
FROMemployees,(SELECTemployee_id,count(employee_id) employee_id_countFROMjob_historyGROUP BYemployee_id) employee_hist
WHEREemployee_hist.employee_id = employees.employee_id
AND employee_hist.employee_id_count >= 2;

exists与not exists

exists关键字

exists关键字，用来检查在子查询中是否存在满足条件的行，一经发现，随即返回。
案例：查询公司管理者的信息：

# 方式1：
SELECT DISTINCTmanager.employee_id,manager.last_name,manager.job_id,manager.department_id
FROMemployees emp
JOIN employees manager ON emp.manager_id = manager.employee_id;# 方式2：
SELECTemployees.employee_id,employees.last_name,employees.job_id,employees.department_id
FROMemployees,(SELECT DISTINCTmanager_idFROMemployees) manager
WHEREemployees.employee_id = manager.manager_id;# 方式3：
SELECTe1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROMemployees e1
WHEREEXISTS (SELECT*FROMemployees e2WHEREe2.manager_id = e1.employee_id);

not exists关键字

not exists关键字的含义和exists相反
案例：查询employees表中不存在的department_id和department_name：

# 方式1：
SELECTdepartments.department_id,departments.department_name
FROMdepartments
LEFT JOIN employees ON departments.department_id = employees.department_id
WHEREemployees.department_id IS NULL;# 方式2：
SELECTdepartment_id,department_name
FROMdepartments
WHERENOT EXISTS (SELECT*FROMemployeesWHEREdepartments.department_id = employees.department_id);

如果一种查询既能用子查询实现，也能用自连接实现，一般选择后者，因为自连接效率更高。