Les06子查询.ppt

Click to edit Master title style,First Level,Second Level,Third Level,Fourth Level,Fifth Level,6-,*,Copyright Oracle Corporation,2001.All rights reserved.,子查询,目标,通过本章学习，您将可以:,描述子查询可以解决的问题,定义子查询。,列句子查询的类型。,书写单行子查询和多行字查询。,使用子查询解决问题,谁的工资比,Abel,高?,谁的工资比,Abel,高?,Main Query:,?,Abel,的工资是多少?,?,Subquery,子查询语法,子查询(内查询)在主查询之前一次执行完成。,子查询的结果被主查询使用(外查询)。,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);,子查询,11000,注意事项,子查询要包含在括号内。,将子查询放在比较条件的右侧。,除非进行,Top-N,分析，否则不要在子查询中使用,ORDER BY,子句。,单行操作符对应单行子查询，多行操作符对应多行子查询。,子查询类型,主查询,子查询,返回,ST_CLERK,多行子查询,ST_CLERK,SA_MAN,主查询,子查询,返回,单行子查询,单行子查询,只返回一行。,使用单行比较操作符。,操作符,=,=,(SELECT salary,FROM employees,WHERE employee_id=143);,执行单行子查询,ST_CLERK,2600,SELECT last_name,job_id,salary,FROM employees,WHERE salary=,(SELECT MIN(salary),FROM employees);,在子查询中使用组函数,2500,子查询中的,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);,2500,SELECT employee_id,last_name,FROM employees,WHERE salary=,(SELECT MIN(salary),FROM employees,GROUP BY department_id);,非法使用子查询,ERROR at line 4:,ORA-01427:single-row subquery returns more thanone row,多行子查询使用单行比较符,子查询中的空值问题,no rows selected,SELECT last_name,job_id,FROM employees,WHERE job_id=,(SELECT job_id,FROM employees,WHERE last_name=Haas);,子查询不返回任何行,多行子查询,返回多行。,使用多行比较操作符。,操作符,IN,ANY,ALL,含义,等于列表中的任何一个,和子查询返回的任意一个值比较,和子查询返回的所有值比较,在多行子查询中使用,ANY,操作符,9000,6000,4200,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;,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;,在多行子查询中使用,ALL,操作符,9000,6000,4200,子查询中的空值问题,SELECT emp.last_name,FROM employees emp,WHERE emp.employee_id NOT IN,(SELECT mgr.manager_id,FROM employees mgr);,no rows selected,总结,通过本章学习，您已经学会:,在什么时候遇到什么问题应该使用子查询。,在查询是基于未知的值时应使用子查询。,SELECT,select_list,FROM,table,WHERE,expr,operator,(SELECT,select_list,FROM,table,);,