RelationalDBDesign RelationalDBDesign





Select Statement  «Prev  Next»
Lesson 1

Introduction to the Oracle SELECT Statement

This course assumes that you are familiar enough with SQL to write basic queries, including queries that take advantage of these SQL features:
  1. Joining two or more tables
  2. Grouping and summarizing data
  3. Using sub-queries
This module covers some advanced SQL features, such as hierarchy queries and combining multiple queries with the UNION command. In addition, you will try out some query features that are exclusive to Oracle, such as the use of pseudocolumns[1].
A pseudocolumn is a special column generated when Oracle executes the query. There are a number of pseudocolumns, as you will see in this module.

Module Objectives

By the end of this module, you will know how to:
  1. Use a variety of operators and conditions to write a query
  2. Write a query using the CONNECT BY feature
  3. Compare the INTERSECT, MINUS, and UNION commands
  4. Correctly place the HAVING DISTINCT or UNIQUE clauses in two queries
  5. Identify the uses of various pseudocolumns

UNION, MINUS, and INTERSECT

The union, minus, and intersect clauses allow the results of multiple queries to be processed and compared.
Each of the functions has an associated operation, the names of the operations are UNION, MINUS, and INTERSECTION.
The following query selects all of the Title values from the BOOKSHELF table and from the BOOK_ORDER table:
select Title
from BOOKSHELF
UNION
select Title
from BOOK_ORDER;

When the preceding query is executed, the optimizer will execute each of the queries separately, and then combine the results. The first query is
select Title
from BOOKSHELF

There are no limiting conditions in the query, and the Title column is indexed, so the primary key index on the BOOKSHELF table will be scanned. The second query is
select Title
from BOOK_ORDER

There are no limiting conditions in the query, and the Title column is indexed, so the primary key index on the BOOK_ORDER table will be scanned.
Since the query performs a union of the results of the two queries, the two result sets will then be merged via a UNION-ALL operation. Using the union operator forces Oracle to eliminate duplicate records, so the result set is processed by a SORT UNIQUE NOSORT operation before the records are returned to the user. If the query had used a union all clause in place of union, the SORT UNIQUE NOSORT operation would not have been necessary. The query would be
select Title
from BOOKSHELF
UNION ALL
select Title
from BOOK_ORDER;

When processing the revised query, the optimizer would perform the scans required by the two queries, followed by a UNION-ALL operation. No SORT UNIQUE NOSORT operation would be required, since a union all clause does not eliminate duplicate records. When processing the union query, the optimizer addresses each of the unioned queries separately. Although the examples shown in the preceding listings all involved simple queries with full table scans, the unioned queries can be very complex, with correspondingly complex execution paths. The results are not returned to the user until all of the records have been processed.
The next lesson discusses operators and conditions that are not usually covered in introductory SQL courses.

[1]Pseudocolumn: A pseudocolumn is data that Oracle makes available to you when it executes SQL commands.
These are created for convenience. For example, the Oracle user name that is executing the SQL is contained in the pseudocolumn called USER.