Oracle_071_lesson_p16
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 thatreturns 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
wheree.department_id = d.department_id) as emp_count
from departments d;
SELECT column1, column2, ...
FROM table1outertable
WHEREcolumn1 operator
(SELECTcolumn1, column2
FROM table2
WHERE expr1 = outertable.expr2);
SELECT last_name, salary, department_id
FROM employees outer_table
WHEREsalary > (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 =
(SELECTCOUNT(DISTINCT salary)
FROM EMPLOYEES
WHERE e.department_id = department_id
AND e.salary
页:
[1]