Oracle入门第三天（下）——子查询

一、子查询

　　1.子查询语法

SELECT    select_list
FROM    table
WHERE    expr operator
             (SELECT    select_list
                FROM        table)

　　示例：

SELECT LAST_NAME
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE LAST_NAME = 'Abel')

　　2.子查询示例

　　　　使用组函数：查询最低工资的员工信息

SELECT LAST_NAME,salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees)

　　　　使用HAVING：

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

　　　　多行子查询：IN,ANY,ALL

 

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

 

　　// 提示，使用不等于建议采用通用的 <>符号