Oracle Certification Notes: Part XII

This is a continuation of my notes as I study for the Oracle Certified Associate credential:

Subqueries: Overview

Subqueries can be included within SELECT, FROM, WHERE, GROUP BY and HAVING clauses.

The WHERE clause may only contain 255 levels of subqueries; whereas, an unlimited depth may be used within the FROM clause.

Example of subquery in a SELECT clause: SELECT (SELECT COUNT(*) FROM employees), 'Hello World!' FROM dual;

A subquery cannot be used within the VALUES clause of an INSERT statement

Multi-row subquery comparison operators include IN, NOT IN, ANY, ALL. = ANY is the same as IN.

An example of ANY/ALL: SELECT * FROM TABLE1 WHERE AMT > ALL (SELECT AMT FROM TABLE2);

Subqueries: Types

Single-row, multiple-row, and correlated subqueries

A correlated subquery: Where a subquery references a column from the parent query. Often inefficient construction of code.

A scalar subquery is one that only returns a single column from a single row - often substituted for a literal value.

Star Transformation: Modifying a query using traditional table joins to use IN-Subquery relationships. May have execution benefits, especially for data warehouses, as well easier to maintain. Oracle parameter included for automatic star transformation.

SET operators

UNION, UNION ALL, INTERSECT, MINUS (ISO equivalent: EXCEPT) are used to make a compound query

No order of precedence.** Read top-to-bottom, left-to-right. Use parentheses to establish order of precedence. **(yet. appears INTERSECT may eventually take precedence)

Columns from each query may be different in name, but must be of same datatype group. Column names from first query will be displayed as the result set.

Data from a compound query is returned with no duplicates and sorted by column values from left-to-right, with the exception of UNION ALL - returns all rows without sorting

ORDER BY can only be placed at the bottom of the last query of a compound query

Posted via Woodland Blog (http://dramse01.blogspot.com)