下面这篇文章简单介绍子查询。

子查询指在一个select语句中还嵌套另一个select语句。

1、子查询的语法

SELECT   select_list

FROM    table

WHERE  expr operator

                    (SELECT  select_list

                     FROM             table);

2、使用子查询

SELECT last_name

FROM   employees

WHERE  salary >

               (SELECT salary

                FROM   employees

                WHERE  last_name = 'Abel');

上面的结果是输出比last_name为Abel的职员的工资高的职员的last_name.

3、单行子查询

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

上面的结果是输出job_id等于employee_id为141的职员的job_id并且工资大于employee_id为143的职员的工资的职员的last_name, job_id, salary

4、多行子查询

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

上面的结果是输出工资比其中一个job_id为'IT_PROG'的工资小并且job_id不等于'IT_PROG'的职员的employee_id, last_name, job_id, salary

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

上面的结果是输出工资比任何一个job_id为'IT_PROG'的工资小并且job_id不等于'IT_PROG'的职员的employee_id, last_name, job_id, salary。这里的