数据库子查询

# WHERE后加子查询

SELECT e.salary FROM employees e WHERE e.salary>(

 SELECT e.salary FROM employees e WHERE last_name='Abel'

);

SELECT e.last_name,e.first_name,e.job_id,e.salary FROM employees e WHERE job_id=(

 SELECT ee.job_id FROM employees ee WHERE ee.employee_id = 141

) AND e.salary>(SELECT eee.salary FROM employees eee WHERE eee.employee_id=143);

# HAVING后加子查询

SELECT MIN(salary),department_id

FROM employees

GROUP BY department_id

HAVING MIN(salary)>(

 SELECT MIN(salary)

 FROM employees

 WHERE department_id = 50

);

补充：

分类

操作符

# 行子查询 例1

SELECT *

FROM employees

WHERE (employee_id,salary)=(

SELECT MIN(employee_id),MAX(salary)

FROM employees

)

# 行子查询 例2

SELECT d.*,(

SELECT COUNT(*)

FROM employees e

WHERE e.department_id=d.department_id

) 个数

FROM departments d;

# EXISTS后子查询

SELECT *

FROM boys

WHERE boys.id NOT IN(

SELECT boyfriend_id

FROM beauty

);

SELECT *

FROM boys

WHERE NOT EXISTS(

SELECT boyfriend_id

FROM beauty

WHERE boys.id=boyfriend_id

);