[玩转SQL]:06 子查询

• 子查询

  ---

子查询分为单行子查询和多行子查询

---

单行子查询

一般内部查询语句可以出现在SQL的三个位置:FROM WHERE HVING

语法:

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

特点:

  ++ 子查询使用小括号括起来.

  ++ 子查询一般放在比较操作符的右边.

  ++ 子查询有可能返回一条或多条记录.

  ++ 子查询中可以使用组函数.

  ++ 一个SQL语句中可以使用多个子查询.

--比abel工资高的员工(传统方式)
--首先获取abel工资
SELECT employee_id, first_name, last_name, salary
FROM   employees
WHERE  last_name LIKE 'Abel%';
--查询比abel工资高的员工
SELECT employee_id, first_name, last_name, salary
FROM   employees
WHERE  salary > 11000;

--比abel工资高的员工(子查询)
SELECT last_name, salary
FROM   employees
WHERE  salary >
      (SELECT salary
       FROM   employees
       WHERE  last_name = 'Abel');

--查询最低工资大于部门ID为50的最低工资的部门
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

ANY如果没有返回记录结果为FALSE.

ALL 如果没有返回记录结果为TRUE.

IN

SELECT employee_id, last_name
FROM   employees
WHERE  salary IN 
                (SELECT MIN(salary)
                 FROM   employees
                 GROUP BY department_id);

ANY

  ++  < ANY 相当于小于返回的多行数据里边的最大值.

  ++  > ANY 相当于大于返回的多行数据里边的最小值.

  ++   = ANY 相当于IN.

ANY可以理解为逻辑操作符的or

SELECT employee_id, last_name, job_id, salary
FROM   employees
WHERE  salary > ANY
                   (SELECT salary 
                    FROM   employees
                    WHERE  job_id = 'IT_PROG');

ALL

  ++ < ALL相当于小于返回的多行数据里边的最小值.

  ++  > ALL相当于大于返回的多行数据礼拜的最大值.

  ++  没有= ALL.

  ++  <> ALL相当于NOT IN

ALL相当于逻辑操作符的and

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

进行子查询是要注意NULL值.

若返回的值中含有NULL值很有可能查询不到预期的结果.

--------------------------------------------------------------------------------------
版权所有,转载时必须以链接方式注明源地址,否则追究法律责任!

QQ    :   413844436
Email  :   softomg@163.com
Blog   :   http://blog.csdn.net/softomg