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 departments NATURAL JOIN (SELECT l.location_id, l.city, l.country_id FROM locations l JOIN countries c ON(l.country_id = c.country_id) JOIN regions USING(region_id) WHERE region_name = 'Europe');
SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (174, 199)) AND employee_id NOT IN (174,199);
SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141);
A scalar subquery expression is a subquery that returns exactly one column value from one row. Scalar subqueries can be used in: The condition and expression part of DECODE and CASE All clauses of SELECT except GROUP BY The SET clause and WHERE clause of an UPDATE statement
SELECT employee_id, last_name, (CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END) location FROM employees;
select department_id, department_name, (select count(*) from employees e where e.department_id = d.department_id) as emp_count from departments d;
SELECT column1, column2, ... FROM table1 outertable WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = outertable.expr2);
SELECT last_name, salary, department_id
FROM employees outer_table
WHERE salary > (SELECT AVG(salary)
FROM employees inner_table
WHERE inner_table.department_id =
outer_table.department_id);
SELECT department_id, employee_id, salary FROM EMPLOYEES e WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM EMPLOYEES WHERE e.department_id = department_id AND 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 query The condition is flagged TRUE If a subquery row value is not found: The condition is flagged FALSE The search continues in the inner query
SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT NULL FROM employees WHERE manager_id = outer.employee_id);
SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT NULL FROM employees WHERE 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_EMP FROM EMPLOYEES GROUP BY department_id ) SELECT employee_id, SALARY/NUM_EMP FROM EMPLOYEES E JOIN CNT_DEPT C ON (e.department_id = c.department_id);
WITH Reachable_From (Source, Destin, TotalFlightTime) AS ( SELECT Source, Destin, Flight_time FROM Flights UNION ALL SELECT incoming.Source, outgoing.Destin, incoming.TotalFlightTime+outgoing.Flight_time FROM Reachable_From incoming, Flights outgoing WHERE incoming.Destin = outgoing.Source ) SELECT Source, Destin, TotalFlightTime FROM Reachable_From;
