Database System
Nested Query
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
1. Nested Query
Outline 2. Location of the Subquery
3. Operator in Subquery
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Nested Query
• A SQL Select command that has another SQL Select command in it.
• A nested query is a SQL Select command that has another SQL Select
command in it.
• The main query is the outer SQL select command (called the outer query)
• A subquery is an SQL select command that is inside the main query (called
the inner query).
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Subquery
outer query
inner query
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list
FROM table);
• The subquery (inner query) is executed once before executing the main query (main
query/outer query).
• The result of the subquery is used in the main query
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Nested Query
SQL> SELECT ename
FROM emp 2975
WHERE sal >
(SELECT sal
FROM emp
WHERE empno = 7566);
ENAME
ENAME
----------
----------
KING
KING
FORD
FORD
SCOTT
SCOTT
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Location of Subquery
Subquery (Inner Query) After SELECT
can be located at
After FROM
Condition of WHERE
Condition of HAVING
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Subquery after SELECT
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Subquery after FROM
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Subquery in Condition of WHERE
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Subquery in Condition of HAVING
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Operators on subquery
A subquery can return a single value or multiple
rows of values
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Single row subquery
Query that returns a single row of data from the SELECT command
Single row operator = equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> not equal to
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Single row subquery (1)
Show data on employees who have the same position as Smith.
SELECT last_name, title
FROM employee
WHERE title =
( SELECT title
FROM employee
WHERE last_name = ‘Smith’ );
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Single row subquery (2)
Displays data on employees who have below-average salaries.
SELECT last_name, title, salary
FROM employee
WHERE salary <
( SELECT AVG(salary)
FROM employee );
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Multiple row subquery
A subquery that returns more than one row of data is called a multiple row subquery
Multiple row operator IN
NOT IN
ANY
ALL
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Multiple row subquery (1)
Select nota_detail data that has “SETRIKA” or “CUCI KERING+PEWANGI” services
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Multiple row subquery (2)
Select nota_detail data that is not “SETRIKA” service or not “CUCI KERING+PEWANGI” service
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Multiple row subquery (3)
Select nota_detail data whose price is above 8000 and 15000
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
Example of Multiple row subquery (4)
Select nota_detail data whose price is above 8000 or 15000
www.its.ac.id/informatika Departemen Teknik Informatika, INSTITUT TEKNOLOGI SEPULUH NOPEMBER
- TERIMA KASIH -
