数据库子查询

子查询语法

SELECT select_list

FROM table

WHERE expr operator

  (SELECT select_list

       FROM table);

子查询 (内查询) 在主查询之前一次执行完成

子查询的结果被主查询使用 (外查询)

注意事项

子查询要包含在括号内。

将子查询放在比较条件的右侧。

除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。

单行操作符对应单行子查询，多行操作符对应多行子查询。

 

 

执行单行子查询

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);

 

在子查询中使用组函数

SELECT last_name, job_id, salary

FROM   employees

WHERE  salary =

                (SELECT MIN(salary)

                 FROM   employees);

 

 

子查询中的 HAVING 子句

首先执行子查询。

向主查询中的HAVING 子句返回结果

SELECT   department_id, MIN(salary)

FROM     employees

GROUP BY department_id

HAVING   MIN(salary) >

                       (SELECT MIN(salary)

                        FROM   employees

                        WHERE  department_id = 50);

 

 

多行子查询中使用 ANY 操作符

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary < ANY

                    (SELECT salary

                     FROM   employees

                     WHERE  job_id = 'IT_PROG')

AND    job_id <> 'IT_PROG';

 

 

多行子查询中使用 ALL 操作符

SELECT employee_id, last_name, job_id, salary

FROM   employees

WHERE  salary < ALL

                    (SELECT salary

                     FROM   employees

                     WHERE  job_id = 'IT_PROG')

AND    job_id <> 'IT_PROG';