Retrieving Data by Using Subqueries 子查询

you should be able to:

1、Write a multiple-column subquery 多列子查询2、Use scalar subqueries in SQL 标量子查询3、Solve problems with correlated subqueries 关联子查询 ，子查询子句与主查询相互利用4、Use the EXISTS and NOT EXISTS operators EXISTS 子句有1条及以上的值输出，则主查询执行。NOT EXISTS 无值则匹配输出。方法：先执行主查询的结果，再找一行值带入子查询来理解子查询的意思。5、Use the WITH clause 类似视图

SELECT department_name, city

FROM departmentsNATURAL JOIN (SELECT l.location_id, l.city, l.country_idFROM locations lJOIN countries cON(l.country_id = c.country_id)JOIN regions USING(region_id)WHERE region_name = 'Europe');

SELECT employee_id, manager_id, department_id

FROM employeesWHERE (manager_id, department_id) IN(SELECT manager_id, department_idFROM employeesWHERE employee_id IN (174, 199))AND employee_id NOT IN (174,199);

SELECT employee_id, manager_id, department_id

FROM employeesWHERE manager_id IN(SELECT manager_idFROM employeesWHERE employee_id IN (174,141))AND department_id IN(SELECT department_idFROM employeesWHERE employee_id IN (174,141))AND employee_id NOT IN(174,141);

A scalar subquery expression is a subquery thatreturns exactly one column value from one row.

Scalar subqueries can be used in:The condition and expression part of DECODE and CASEAll clauses of SELECT except GROUP BYThe SET clause and WHERE clause of an UPDATE statement

SELECT employee_id, last_name,

(CASEWHEN department_id =(SELECT department_id FROM departmentsWHERE location_id = 1800)THEN 'Canada' ELSE 'USA' END) locationFROM employees;

select department_id, department_name,

(select count(*)from employees ewhere e.department_id = d.department_id) as emp_countfrom departments d;

SELECT column1, column2, ...

FROM table1 outertableWHERE column1 operator (SELECT column1, column2FROM table2WHERE expr1 = outertable.expr2);

SELECT last_name, salary, department_id

FROM employees outer_tableWHERE salary > (SELECT AVG(salary)FROM employees inner_tableWHERE inner_table.department_id = outer_table.department_id);

SELECT department_id, employee_id, salary

FROM EMPLOYEES eWHERE 1 = (SELECT COUNT(DISTINCT salary)FROM EMPLOYEES WHERE e.department_id = department_idAND e.salary <= salary)

The EXISTS operator tests for existence of rows in the results set of the subquery.

If a subquery row value is found:The search does not continue in the inner queryThe condition is flagged TRUEIf a subquery row value is not found:The condition is flagged FALSEThe search continues in the inner query

SELECT employee_id, last_name, job_id, department_id

FROM employees outerWHERE EXISTS ( SELECT NULLFROM employeesWHERE manager_id = outer.employee_id);

SELECT department_id, department_name

FROM departments dWHERE NOT EXISTS (SELECT NULLFROM employeesWHERE department_id = d.department_id);

Using the WITH clause, you can use the same query block in a SELECT statement when it occurs more than once within a complex query.

The WITH clause retrieves the results of a query block and stores it in the user’s temporary tablespace.The WITH clause may improve performance.

WITH CNT_DEPT AS

(SELECT department_id,COUNT(1) NUM_EMPFROM EMPLOYEESGROUP BY department_id)SELECT employee_id,SALARY/NUM_EMPFROM EMPLOYEES EJOIN CNT_DEPT CON (e.department_id = c.department_id);

WITH Reachable_From (Source, Destin, TotalFlightTime) AS

(SELECT Source, Destin, Flight_timeFROM FlightsUNION ALLSELECT incoming.Source, outgoing.Destin, incoming.TotalFlightTime+outgoing.Flight_timeFROM Reachable_From incoming, Flights outgoingWHERE incoming.Destin = outgoing.Source)SELECT Source, Destin, TotalFlightTime FROM Reachable_From;