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)

  • Leave a Comment

    Your email address will not be published.

    This site uses Akismet to reduce spam. Learn how your comment data is processed.