SQL - Queries and Subqueries

Previous Chapter | Next Chapter

A query is an operation that retrieves data from one or more tables or views. In this reference, a top-level SELECT statement is called a query, and a query nested within another SQL statement is called a subquery.

This section describes some types of queries and subqueries and how to use them..

Creating Simple Queries
The list of expressions that appears after the SELECT keyword and before the FROM clause is called the select list. Within the select list, you specify one or more columns in the set of rows you want Oracle to return from one or more tables, views, or materialized views. The number of columns, as well as their datatype and length, are determined by the elements of the select list..

If two or more tables have some column names in common, you must qualify column names with names of tables. Otherwise, fully qualified column names are optional. However, it is always a good idea to qualify table and column references explicitly. Oracle often does less work with fully qualified table and column names..

You can use a column alias, c_alias, to label the preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query..

You can use comments in a SELECT statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement..

Hierarchical Queries
If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause: .

* START WITH specifies the root row(s) of the hierarchy..

* CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. Some part of the connect_by_condition must use the PRIOR operator to refer to the parent row..

* PRIOR evaluates the connect_by_condition for the parent row of the current row in a hierarchical query. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators..

The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:.

* If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing..

* Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy..

Oracle uses the information from the hierarchical query clause to form the hierarchy using the following steps:.

1. Oracle processes the WHERE clause either before or after the CONNECT BY clause depending on whether the WHERE clause contains any join predicates (as described in the preceding bullet list)..

2. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition..

3. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows..

4. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 3, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row..

5. If the query contains a WHERE clause without a join, Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition. .

To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query. The CONNECT BY condition cannot contain a subquery..

If the CONNECT BY condition results in a loop in the hierarchy, Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row..


The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:.

SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.

----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101

The next example is similar to the preceding example, but uses the LEVEL pseudocolumn to show parent and child rows:.

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;.

----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order..

The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype. If component queries select character data, the datatype of the return values are determined as follows:.

* If both queries select values of datatype CHAR, the returned values have datatype CHAR..

* If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2..

Restrictions on set operators:
* The set operators are not valid on columns of type BLOB, CLOB, BFILE, varray, or nested table..

* The UNION, INTERSECT, and MINUS operators are not valid on LONG columns..

* To reference a column, you must use an alias to name the column..

* You cannot also specify the for_update_clause with these set operators..

* You cannot specify the order_by_clause in the subquery of these operators..

* You cannot use these operators in SELECT statements containing TABLE collection expressions. .

The following examples combine the two query results with each of the set operators..

UNION Example
The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table:.

SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse" FROM departments
SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name
FROM warehouses;.

LOCATION_ID Department Warehouse ----------- --------------------- -------------------------- 1400 IT 1400 Southlake, Texas 1500 Shipping 1500 San Francisco 1600 New Jersey 1700 Accounting 1700 Administration 1700 Benefits 1700 Construction . . ..

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows:.

SELECT product_id FROM order_items
SELECT product_id FROM inventories;.

SELECT location_id FROM locations
SELECT location_id FROM departments;.

A location_id value that appears multiple times in either or both queries (such as '1700') is returned only once by the UNION operator, but multiple times by the UNION ALL operator..

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:.

SELECT product_id FROM inventories
SELECT product_id FROM order_items;.

MINUS Example
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:.

SELECT product_id FROM inventories
SELECT product_id FROM order_items;.

Sorting Query Results
Use the ORDER BY clause to order the rows selected by a query. Sorting by position is useful in the following cases:.

* To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause..

* For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query..

The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause. .

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity. .

Join Conditions
Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The columns in the join conditions need not also appear in the select list..

To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables..

In addition to join conditions, the WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query. .

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter DB_BLOCK_SIZE. .

Self Joins
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition. .

Cartesian Products
If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, the optimizer may choose a join order that avoids producing an intermediate Cartesian product..

Inner Joins
An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition..

Outer Joins
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition..

* To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or apply the outer join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B..

* To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A..

* To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax..

Oracle Corporation recommends that you use the ANSI OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the ANSI syntax:.

* You cannot specify the (+) operator in a query block that also contains ANSI JOIN syntax..

* The (+) operator can appear only in the WHERE clause or, in the context of left-correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view..

* If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join..

* The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator..

* A condition containing the (+) operator cannot be combined with another condition using the OR logical operator..

* A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression..

* A condition cannot compare any column marked with the (+) operator with a subquery..

If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle will return only the results of a simple join..

In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C. .

Using Subqueries
A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery..

A subquery can contain another subquery. Oracle imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause..

If columns in a subquery have the same name as columns in the containing statement, you must prefix any reference to the column of the table from the containing statement with the table name or alias. To make your statements easier for you to read, always qualify the columns in a subquery with the name or alias of the table, view, or materialized view..

Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement..

A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department. .

Use subqueries for the following purposes:.

* To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement.

* To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement.

* To define one or more values to be assigned to existing rows in an UPDATE statement.

* To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements.

* To define a table to be operated on by a containing query..

You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements..

Subqueries so used can employ correlation variables, but only those defined within the subquery itself, not outer references. Outer references ("left-correlated subqueries") are allowed only in the FROM clause of a SELECT statement. .

Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where expr is called for in syntax. .

Unnesting of Nested Subqueries
Subqueries are "nested" when they appear in the WHERE clause of the parent statement. When Oracle evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins..

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the subquery's immediate outer query block..

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:.

* Uncorrelated IN subqueries.

* IN and EXISTS correlated subqueries as long, as they do not contain aggregate functions or a GROUP BY clause.

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:.

* You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery..

* You can unnest other subqueries by specifying the UNNEST hint in the subquery .

Selecting from the DUAL Table
DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. .

Distributed Queries
Oracle's distributed database management system architecture lets you access data in remote databases using Oracle Net and an Oracle server. You can identify a remote table, view, or materialized view by appending @dblink to the end of its name. The dblink must be a complete or partial name for a database link to the database containing the remote table, view, or materialized view. .

Restrictions on Distributed Queries
Distributed queries are currently subject to the restriction that all tables locked by a FOR UPDATE clause and all tables with LONG columns selected by the query must be located on the same database. For example, the following statement will raise an error:.

SELECT employees_ny.*
FROM employees_ny@ny, departments
WHERE employees_ny.department_id = departments.department_id
AND departments.department_name = 'ACCOUNTING'
FOR UPDATE OF employees_ny.salary; .

The following statement fails because it selects long_column, a LONG value, from the employees_review table on the ny database and locks the employees table on the local database:.

SELECT employees.employee_id, review.long_column, employees.salary
FROM employees, employees_review@ny review
WHERE employees.employee_id = employees_review.employee_id
FOR UPDATE OF employees.salary;.

In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.

Previous Chapter | Next Chapter

Discuss SQL Queries and Subqueries | More Tutorials on Oracle dba | Source : Oracle Documentation

Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Want to share or request more about SQL Queries and Subqueries in SQL Tutorial to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info