Tài liệu subqueries doc
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (166.72 KB, 26 trang )
Subqueries
6
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder6Ć2
Subqueries 6Ć3
Objectives
This lesson covers more advanced features of the SELECT statement. You can
write subqueries in the WHERE clause of another SQL statement to obtain
values based on an unknown conditional value.
At the end of this lesson, you should be able to
D
Write nested subqueries to query data based on unknown criteria.
D
Use subqueries in data manipulation statements.
D
Order data with subqueries.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder6Ć4
Server
ÉÉ
ÉÉ
ÉÉ
ÉÉ
Subqueries 6Ć5
Overview
A subquery is a SELECT statement that is embedded in a clause of another SQL
statement.You can build powerful commands out of simple ones by using subqueries.
They can be very useful when you need to select rows from a table with a condition
that depends on the data in the table itself.
You can place the subquery in a number of SQL command clauses:
D
WHERE clause
D
HAVING clause
D
FROM clause of a SELECT or DELETE statement
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder6Ć6
Subqueries 6Ć7
Subqueries
Subqueries are very useful for writing SELECT statements that query values based on
an unknown conditional value. You can use the subquery to find out the values of the
unknown data.
Syntax
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
where: operator includes a comparison operator such as >, =, or
IN.
Note: Comparison operators fall into two classes: single row (>, =, >=, <, <>, <=)
and multiple row (IN, NOT IN) operators.
The subquery is often referred to as a nested SELECT, sub-SELECT, or inner
SELECT statement. The subquery generally executes first, and its output is used to
complete the query condition for the main or outer query.
Guidelines
D
Enclose a subquery within parentheses.
D
Place the subquery after the comparison operator.
D
Do not add an ORDER BY clause to a subquery. You can have only one ORDER
BY clause for a SELECT statement, and if specified, it must be the last clause in
the main SELECT statement.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder6Ć8
43
Subqueries 6Ć9
How Are Nested Subqueries Processed?
A SELECT statement can be considered as a query block. This example consists of
two query blocks: the main query and the inner query.
Example
Retrieve the last name and title of the employees in the same department as Biri.
SQL> SELECT last_name, title
2 FROM s_emp
3 WHERE dept_id =
4 (SELECT dept_id
5 FROM s_emp
6 WHERE UPPER(last_name)=’BIRI’);
1.
The nested SELECT statement or query block is executed first, producing a query
result: 43.
SQL> SELECT dept_id
2 FROM s_emp
3 WHERE UPPER(last_name)=’BIRI’;
2.
The main query block is then processed and uses the value returned by the nested
subquery to complete its search condition.
In fact, the main query would finally look like this to the Oracle7 Server:
SQL> SELECT last_name, title
2 FROM s_emp
3 WHERE dept_id = 43;
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder6Ć10
