1_4 多表连接与子查询.pptVIP

* * * * * * * * * * * * 单行子查询 返回一行记录 使用单行记录比较运算符 Operator = = = Meaning Equal to Greater than Greater than or equal to Less than Less than or equal to Not equal to 下面的语句错在哪里? 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 对多行子查询使用了单行比较操作符. 下面的语句将会返回行吗? SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = Haas); no rows selected 子查询不返回任何值 多行子查询 返回多行 使用多行比较运算符 运算符 IN ANY ALL 含义 等于列表中的任何值 将值与子查询返回的任意一个值进行比较 比较子查询返回的每一个值 在多行子查询中使用ANY运算 符 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运算符 ALL指大于最大值 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; 补充: 高级子查询 本章小结 本章讲述了什么是多表连接，以及多表连接的几种类型，通过例子说明了以前的多表连接语法和新的Sql99标准中的连接语法的差异。在本章的最后也讲述了子查询 的基本语法和使用，并且重点强调了子查询中容易出的错误。 * * * * * * * * * * * * * * * Joining a Table to Itself (continued) The above example joins the EMP table to itself. To simulate two tables in the FROM clause, there are two aliases, namely WORKER and MANAGER, for the same table, EMP. In this example, the WHERE clause contains the join that means “where a worker’s manager number matches the employee number for the manager.” Class Management Note Point out the following to the students: The column heading in the result of the above query seems meaningless. A meaningful column alias should have been used instead. Ther