Using Sub queries to Solve Queries
&
Displaying Data from Multiple tables
Objectives
After completing this lesson, you should be able to do the
following:
• Describe the types of problems that the subqueries can
solve
• List the types of subqueries
• Write single-row and multiple-row subqueries
• Display data from multiple tables
Using a Subquery to Solve a Problem
Who has a salary greater than Abel’s?
Main query:
Which employees have salaries greater than Abel’s
salary?
Subquery:
What is Abel’s salary?
Subquery Syntax
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
• The subquery (inner query) executes before the main query
(outer query).
• The result of the subquery is used by the main query.
Using a Subquery
SELECT last_name, salary
FROM employees 11000
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
Guidelines for Using Subqueries
• Enclose subqueries in parentheses.
• Place subqueries on the right side of the comparison
condition for readability (However, the subquery can appear
on either side of the comparison operator.).
• Use single-row operators with single-row subqueries and
multiple-row operators with multiple-row subqueries.
Types of Subqueries
• Single-row subquery
Main query
returns
Subquery ST_CLERK
• Multiple-row subquery
Main query
returns ST_CLERK
Subquery
SA_MAN
Single-Row Subqueries
• Return only one row
• Use single-row comparison operators
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to
Executing Single-Row Subqueries
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = SA_REP
(SELECT job_id
FROM employees
WHERE last_name = ‘Abel’)
AND salary > 8600
(SELECT salary
FROM employees
WHERE last_name = ‘Abel’);
Using Group Functions in a Subquery
SELECT last_name, job_id, salary
FROM employees 2500
WHERE salary =
(SELECT MIN(salary)
FROM employees);
The HAVING Clause with Subqueries
• The Oracle server executes the subqueries first.
• The Oracle server returns results into the HAVING clause of
the main query.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id 2500
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
…
What Is Wrong with This Statement?
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Single-row operator
with multiple-row
subquery
Multiple-Row Subqueries
• Return more than one row
• Use multiple-row comparison operators
Operator Meaning
IN Equal to any member in the list
ANY Must be preceded by =, !=, >, <, <=, >=.
Compares a value to each value in a list or
returned by a query. Evaluates to FALSE if the
query returns no rows.
ALL Must be preceded by =, !=, >, <, <=, >=.
Compares a value to every value in a list or
returned by a query. Evaluates to TRUE if the
query returns no rows.
Using the ANY Operator
in Multiple-Row Subqueries
SELECT employee_id, last_name, job_id, salary
FROM employees 9000, 6000, 4200
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
…
Using the ALL Operator
in Multiple-Row Subqueries
SELECT employee_id, last_name, job_id, salary
FROM employees 9000, 6000, 4200
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
Obtaining Data from Multiple Tables
EMPLOYEES DEPARTMENTS
…
Creating Joins with the ON Clause
• The join condition for the natural join is basically an equijoin
of all columns with the same name.
• Use the ON clause to specify arbitrary conditions or specify
columns to join.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand.
Retrieving Records with the ON Clause
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
…
Creating Three-Way Joins with
the ON Clause
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
…
Applying Additional Conditions
to a Join
Use the AND clause or the WHERE clause to apply additional
conditions:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;
Or
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149 ;
Joining a Table to Itself
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
… …
MANAGER_ID in the WORKER table is equal to
EMPLOYEE_ID in the MANAGER table.
Self-Joins Using the ON Clause
SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id);
