SQL based use of subqueries to retrieve data (22)
Multi-column subquery
Where (manager_id, department_id) in
Subquery
100 90
102 60
124 50
Each row of the main query is compared to a subquery with multiple rows and columns
Comparison of columns
A comparison of multiple columns, including subqueries, which can be:
Non-paired comparison
Pairwise comparison
Compare subquery with subquery
1. Display other employee information in the same department and with the same manager as the employee named "John"
Select employee_id, manager_id, department_id from empl_demo
Where (manager_id, department_id) IN
(select manager_id, department_id from empl_demo
Where first_name = 'John')
AND first_name 'John'
Non-paired comparison
1. Display the employee number, manager number and department number of the manager ID and department ID whose name is not "John"
Select employee_id, manager_id, department_id
From empl_demo
Where manager_id in
(select manager_id
From empl_demo
Where first_name = 'john')
And department_id in
(select department_id
From empl_demo
Where first_name = 'john')
And first_name 'john'
Scalar quantum query expression
A scalar quantum query is a subquery that returns a column from a row.
Scalar quantum queries can be used in the following situations:
-part of DECODE and CASE conditions and expressions
-in all clauses except the GROUP BY clause in SELECT
SET clause and WHERE clause of-UPDATE statement
Scalar quantum queries in CASE expressions:
Select employee_id, last_name, department_id
(case
When department_id =
(select department_id
From departments
Where location_id = 1800)
Then 'canada' else' usa' end) location
From employees
Scalar quantum query in the ORDER BY clause:
Select employee_id, last_name,department_id
From employees e
Order by (select department_name
From departments d
Where e.department_id = d.department_id)
Related subquery
Related subqueries are executed row after row, and each row of the main query executes a subquery
Use columns from the main query in a subquery
Select column1, column2,...
From table1 Outer_table
Where column1 operator
(selecT column1, column2
From table2
Where expr1 = Outer_table.expr2)
2. Find all employee information about who earns more than the average salary of their department.
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)
3. Show which employees have changed their jobs at least twice
Select e.employee_id, last_name,e.job_id from employees e
Where 2
(select dept_avg
From avg_cost)
Order by department_name
Recursive WITH clause
Recursive WITH clause:
Enables formulation of recursive queries.
Creates query with a name, called the Recursive WITH element name
Contains two types of query blocks member: anchor and a recursive
Is ANSI-compatible
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
