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:
- Joining two or more tables
- Grouping and summarizing data
- Using sub-queries
This module covers some advanced SQL features, such as hierarchy queries and combining multiple queries with the
command. In addition, you will try out some query features that are exclusive to Oracle, such as the use of pseudocolumns
. 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.
By the end of this module, you will know how to:
- Use a variety of operators and conditions to write a query
- Write a query using the
CONNECT BY feature
- Compare the
- Correctly place the
HAVING DISTINCT or
UNIQUE clauses in two queries
- 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:
When the preceding query is executed, the optimizer will execute each of the queries separately, and then combine the results.
The first query is
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
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
Processing Revised Query
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.
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.
Oracle Database SQL