Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Expressions, Conditions, and Queries, 4 of 4
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. The full syntax of all the clauses, and the semantics of the keywords and parameters, appear in "SELECT and Subqueries".
The list of expressions that appears after the SELECT
keyword and before the FROM
clause is called the select list. Each expression expr becomes the name of one column in the set of returned rows, and each table.* becomes a set of columns, one for each column in the table in the order they were defined when the table was created. The datatype and length of each expression is determined by the elements of the expression.
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.
If a table contains hierarchical data, you can select rows in a hierarchical order using the hierarchical query clause:
|
specifies the root row(s) of the hierarchy. |
|
specifies the relationship between parent rows and child rows of the hierarchy. Some part of condition must use the |
|
restricts the rows returned by the query without affecting other rows of the hierarchy. |
Oracle uses the information from the hierarchical query clause clause to form the hierarchy using the following steps:
START
WITH
condition.
CONNECT
BY
condition with respect to one of the root rows.
CONNECT
BY
condition with respect to a current parent row.
WHERE
clause, 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.
You can use the ORDER
BY
clause to order the rows selected by a query. Sorting by position is useful in the following cases:
ORDER
BY
clause.
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. For information on these parameters, see Oracle8i National Language Support Guide. 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 ("snapshots"). 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.
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
. See the "Equijoin Examples".
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. See the "Self Join Example".
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.
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, 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. See the syntax for an outer join in "SELECT and Subqueries".
Outer join queries are subject to the following rules and restrictions:
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.
OR
logical operator.
IN
comparison operator to compare a column marked with the (+) operator with an expression.
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.
Use subqueries for the following purposes:
INSERT
or CREATE
TABLE
statement
CREATE
VIEW
or CREATE
MATERIALIZED
VIEW
statement
UPDATE
statement
WHERE
clause, HAVING
clause, or START
WITH
clause of SELECT
, UPDATE
, and DELETE
statements
INSERT
... VALUES
list
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
, UDPATE
, 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. See table_collection_expression.
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 can contain another subquery. You can nest up to 255 levels of subqueries.
If tables in a subquery have the same name as tables 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. See the "Correlated Subquery Examples".
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.
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 subqueries that contain a CONNECT
BY
or START
WITH
clause, 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 nested subqueries:
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:
NOT
IN
subquery by specifying the HASH_AJ
or MERGE_AJ
hint in the subquery.
UNNEST
hint in the subquery
For information on hints, see Chapter 2, "Basic Elements of Oracle SQL".
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. See "SQL Functions" for many examples of selecting a constant value from DUAL
.
Oracle's distributed database management system architecture allows you to access data in remote databases using Net8 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.
See Also:
"Referring to Objects in Remote Databases" for more information on referring to database links. |
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 emp_ny.* FROM emp_ny@ny, dept WHERE emp_ny.deptno = dept.deptno AND dept.dname = 'ACCOUNTING' FOR UPDATE OF emp_ny.sal;
The following statement fails because it selects LONG_COLUMN
, a LONG
value, from the EMP_REVIEW
table on the NY
database and locks the EMP
table on the local database:
SELECT emp.empno, review.long_column, emp.sal FROM emp, emp_review@ny review WHERE emp.empno = emp_review.empno FOR UPDATE OF emp.sal;
In addition, Oracle currently does not support distributed queries that select user-defined types or object REFs on remote tables.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|