Unit 9 - Retrieve Data Using Subqueries

Retrieve Data Using Subqueries
Using Subqueries
Agenda
• Retrieving data by using a subquery as a source

• Writing a multiple-column subquery
• Using scalar subqueries in SQL
• Solving problems with correlated subqueries
• Using the EXISTS and NOT EXISTS operators
• Using the WITH clause
Using Subqueries
Retrieving Data
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') as loc;
Using Subqueries
Retrieving Data
• Create a database view:
CREATE OR REPLACE VIEW european_cities AS

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';
• Join the EUROPEAN_CITIES view with the DEPARTMENTS table:
SELECT department_name, city

FROM departments
NATURAL JOIN european_cities;
Using Subqueries
Multiple-Column Subqueries
• Each row of the main query is compared to values from a multiple-row and
multiple-column subquery:
SELECT column, column, ...

FROM table
WHERE (column, column, ...) IN
(SELECT column, column, ...
FROM table
WHERE condition);
Using Subqueries
Pairwise Comparison Subquery
• Display the details of the employees who are managed by the same manager
and work in the same department as employees with the first name of “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';
Using Subqueries
Nonpairwise Comparison Subquery
• Display the details of the employees who are managed by the same manager as the
employees with the first name of “John” and work in the same department as the
employees with the first name of “John.”
SELECT employee_id, manager_id, department_id

FROM empl_demo
WHERE manager_id IN
(SELECT manager_id FROM empl_demo
AND department_id IN
(SELECT department_id FROM empl_demo
AND first_name <> 'John';
Using Subqueries
Scalar Subquery Expressions
• 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 CASE
• All clauses of SELECT except GROUP BY
• The SET clause and WHERE clause of an UPDATE statement
Using Subqueries
Examples
• Scalar subqueries in CASE expressions:
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;
• Scalar subqueries in the ORDER BY clause:
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
Using Subqueries
Correlated Subqueries
• Correlated subqueries are used for row-by-row processing.

• Each subquery is executed once for every row of the outer query.
GET candidate row from outer query
EXECUTE inner query using candidate row value
USE values from inner query to qualify or disqualify candidate row

Using Subqueries
• The subquery references a column from a table in the parent query.
SELECT column1, column2, ...

FROM table1 Outer_table
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 =
Outer_table.expr2);
Using Subqueries
• Display details of those employees who have changed jobs at least twice.
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
Using Subqueries
The EXISTS Operator
• 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
Using Subqueries
The EXISTS Operator
SELECT employee_id, last_name, job_id, department_id

FROM employees ext
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id = ext.employee_id);
Using Subqueries
The NON EXISTS Operator
• Find all departments that do not have any employees:

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);
Using Subqueries
WITH Clause
• Helps you to use the same query block in a SELECT statement when it
occurs more than once within a complex query.
• Retrieves the results of a query block and stores it in the user’s temporary
tablespace.
• May improve performance
Using Subqueries
WITH Clause
• Write a query to display the department name and total salaries for those departments
whose total salary is greater than the average salary across departments.
WITH dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total >
(SELECT dept_avg FROM avg_cost)ORDER BY department_name;
Using Subqueries
WITH Clause
WITH RECURSIVE my_crt AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_crt WHERE n<10
)
SELECT * FROM my_crt;
Quiz
• With a correlated subquery, the inner SELECT statement drives the outer
SELECT statement
a)True
b) False
Using Subqueries
Practice 7
This practice covers the following topics:

• Creating multiple-column subqueries
• Writing correlated subqueries
• Using the EXISTS operator
• Using scalar subqueries
• Using the WITH clause

Unit 9 - Retrieve Data Using Subqueries

Uploaded by

Copyright:

Available Formats

Unit 9 - Retrieve Data Using Subqueries

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Unit 9 - Retrieve Data Using Subqueries

Uploaded by

Copyright:

Available Formats

Retrieve Data Using Subqueries

• Retrieving data by using a subquery as a source

SELECT department_name, city

• Create a database view:

CREATE OR REPLACE VIEW european_cities AS

• Join the EUROPEAN_CITIES view with the DEPARTMENTS table:

SELECT department_name, city

SELECT column, column, ...

SELECT employee_id, manager_id, department_id

SELECT employee_id, manager_id, department_id

• A scalar subquery expression is a subquery that returns exactly one

• Scalar subqueries can be used in:

• Correlated subqueries are used for row-by-row processing.

GET candidate row from outer query

EXECUTE inner query using candidate row value

USE values from inner query to qualify or disqualify candidate row

• The subquery references a column from a table in the parent query.

SELECT column1, column2, ...

• If a subquery row value is found:

• If a subquery row value is not found:

SELECT employee_id, last_name, job_id, department_id

• Find all departments that do not have any employees:

This practice covers the following topics:

You might also like