Release 8.0
A58225-01
Library |
Product |
Contents |
Index |
| Oracle8
SQL Reference
Release 8.0 A58225-01 |
|
To retrieve data from one or more tables, object tables,
views, object views, or snapshots.
|
Note: Descriptions of commands and clauses preceded by |
For you to select data from a table or snapshot, the table
or snapshot must be in your own schema or you must have SELECT privilege
on the table or snapshot.
For you to select rows from the base tables of a view,
The SELECT ANY TABLE system privilege also allows you to
select data from any table or any snapshot or any view's base table.
|
DISTINCT |
returns only one copy of each set of duplicate rows selected. Duplicate rows are those with matching values for each expression in the select list. |
|
|
ALL |
returns all rows selected, including all copies of duplicates. The default is ALL. |
|
|
* |
selects all columns from all tables, views, or snapshots, listed in the FROM clause. |
|
|
table.*
|
selects all columns from the specified table, view, or snapshot. You can use the schema qualifier to select from a table, view, or snapshot in a schema other than your own. See also "Joins". |
|
|
expr |
selects an expression. See the syntax description of expr in "Expressions"; see also "Creating Simple Queries". A column name in this list can be qualified only with schema if the table, view, or snapshot containing the column is qualified with schema in the FROM clause. |
|
|
c_alias |
provides a different name for the column expression and causes the alias to be used in the column heading. The AS keyword is optional. 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. |
|
|
PARTITION (partition_name) |
specifies partition-level data retrieval. The partition_name parameter may be the name of the partition within table from which to retrieve data or a more complicated predicate restricting retrieval to just one partition of the table. |
|
|
schema |
is the schema containing the selected table, view, or snapshot. If you omit schema, Oracle assumes the table, view, or snapshot is in your own schema. |
|
|
table, view, snapshot |
is the name of a table, view, or snapshot from which data is selected. |
|
|
dblink |
is the complete or partial name for a database link to a remote database where the table, view, or snapshot is located. For more information on referring to database links, see the section "Referring to Objects in Remote Databases". Note that this database need not be an Oracle database. |
|
|
|
If you omit dblink, Oracle assumes that the table, view, or snapshot is on the local database. |
|
|
|
If you apply the keyword THE, the subquery must return a single column value which must be a nested table or an expression that yields a nested table. |
|
|
|
informs Oracle that the column value returned by the subquery is a nested table, not a scalar value. A subquery prefixed by THE is called a flattened subquery. "Using Flattened Subqueries". Note: You cannot use the set operators in a flattened subquery; see set operators below. |
|
|
subquery |
is a subquery that is treated in the same manner as a view. "Subqueries". Oracle executes the subquery and then uses the resulting rows as a view in the FROM clause. |
|
|
t_alias |
provides a different name for the table, view, snapshot, or subquery for evaluating the query and is most often used in a correlated query. Other references to the table, view, or snapshot throughout the query must refer to the alias. |
|
|
WHERE |
restricts the rows selected to those for which the condition is TRUE. If you omit this clause, Oracle returns all rows from the tables, views, or snapshots in the FROM clause. See the syntax description of condition in "Conditions". |
|
|
START WITH ... CONNECT BY |
returns rows in a hierarchical order. See also "Hierarchical Queries". |
|
|
GROUP BY |
groups the selected rows based on the value of expr for each row, and returns a single row of summary information for each group. See also "GROUP BY Clause". |
|
|
HAVING |
restricts the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause, Oracle returns summary rows for all groups. See also "HAVING Clause". |
|
|
|
See also the syntax description of expr in "Expressions" and the syntax description of condition in "Conditions". |
|
|
UNION UNION ALL INTERSECT MINUS |
combine the rows returned by two SELECT statements using a set operation. To reference a column, you must use an alias to name the column. The FOR UPDATE clause cannot be used with these set operators.
|
|
|
ORDER BY |
orders rows returned by the statement. |
|
|
|
expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or snapshots in the FROM clause. |
|
|
|
position orders rows based on their value for the expression in this position of the select list. |
|
|
|
ASC and DESC specify either ascending or descending order. ASC is the default. See also "ORDER BY Clause". |
|
|
FOR UPDATE |
locks the selected rows. |
|
|
|
OF |
Locks the select rows only for a particular table in a join. |
|
|
NOWAIT |
returns control to you if the SELECT statement attempts to lock a row that is locked by another user. If you omit this clause, Oracle waits until the row is available and then returns the results of the SELECT statement. |
|
|
See also "FOR UPDATE Clause". |
|
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, although it is always better to explicitly qualify
table and column references. Oracle often does less work with fully qualified
table and column names.
You can select rows from a single partition of a partitioned table by specifying the keyword PARTITION in the FROM clause. This SQL statement assigns an alias for and retrieves rows from the NOV96 partition of the SALES table:
SELECT * FROM sales PARTITION (nov96) s WHERE s.amount_of_sale > 1000;
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.
If you use the DISTINCT option to return only a single copy
of duplicate rows, the total number of bytes in all select list expressions
is limited to the size of a data block minus some overhead. This size is
specified by the initialization parameter DB_BLOCK_SIZE.
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. For more information on hints, see
Oracle8 Tuning.
The following statement selects rows from the EMP table with the department number of 30:
SELECT * FROM emp WHERE deptno = 30;
The following statement selects the name, job, salary and department number of all employees except sales people from department number 30:
SELECT ename, job, sal, deptno FROM emp WHERE NOT (job = 'SALESMAN' AND deptno = 30);
The following statement selects from subqueries in the FROM clause and gives departments' total employees and salaries as a decimal value of all the departments:
SELECT a.deptno "Department", a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal "%Salary" FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a, (SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ;
If a table contains hierarchical data, you can select rows in a hierarchical order using the following clauses:
Oracle uses the information from the above clause to form the hierarchy using the following steps:
SELECT statements performing hierarchical queries are subject to the following restrictions:
The following sections discuss the START WITH and CONNECT
BY clauses.
The START WITH clause identifies the row(s) to be used as
the root(s) of a hierarchical query. This clause specifies a condition
that the roots must satisfy. If you omit this clause, Oracle uses all rows
in the table as root rows. A START WITH condition can contain a subquery.
The CONNECT BY clause specifies the relationship between parent and child rows in a hierarchical query. This clause contains a condition that defines this relationship. This condition can be any condition as described in "Conditions"; however, some part of the condition must use the PRIOR operator to refer to the parent row. The part of the condition containing the PRIOR operator must have one of the following forms:
PRIOR expr comparison_operator expr expr comparison_operator PRIOR expr
To find the children of a parent row, Oracle evaluates the
PRIOR expression 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 clause can contain other conditions to further
filter the rows selected by the query. The CONNECT BY clause cannot contain
a subquery.
If the CONNECT BY clause 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 CONNECT BY clause defines a hierarchical relationship in which the EMPNO value of the parent row is equal to the MGR value of the child row:
CONNECT BY PRIOR empno = mgr;
In the following CONNECT BY clause, the PRIOR operator applies only to the EMPNO value. To evaluate this condition, Oracle evaluates EMPNO values for the parent row and MGR, SAL, and COMM values for the child row:
CONNECT BY PRIOR empno = mgr AND sal > comm;
To qualify as a child row, a row must have a MGR value equal
to the EMPNO value of the parent row and it must have a SAL value greater
than its COMM value.
SELECT statements that perform hierarchical queries can use
the LEVEL pseudocolumn. LEVEL returns the value 1 for a root node, 2 for
a child node of a root node, 3 for a grandchild, etc. For more information
on LEVEL, see the section "Pseudocolumns".
The number of levels returned by a hierarchical query may
be limited by available user memory.
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'PRESIDENT'. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr; ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
The following statement is similar to the previous one, except that it does not select employees with the job 'ANALYST'.
SELECT LPAD(' ', 2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp WHERE job != 'ANALYST' START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr; ORG_CHART EMPNO MGR JOB -------------------- ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER ADAMS 7876 7788 CLERK SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
Oracle does not return the analysts SCOTT and FORD, although
it does return employees who are managed by SCOTT and FORD.
The following statement is similar to the first one, except that it uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy:
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr AND LEVEL <= 2; ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER BLAKE 7698 7839 MANAGER CLARK 7782 7839 MANAGER
Use the GROUP BY clause to group selected rows and return
a single row of summary information. Oracle collects each group of rows
based on the values of the expression(s) specified in the GROUP BY clause.
If a SELECT statement contains the GROUP BY clause, the select list can contain only the following types of expressions:
Expressions in the GROUP BY clause can contain any columns
in the tables, views, and snapshots in the FROM clause, regardless of whether
the columns appear in the select list.
The GROUP BY clause can contain no more than 255 expressions.
The total number of bytes in all expressions in the GROUP BY clause is
limited to the size of a data block minus some overhead. This size is specified
by the initialization parameter DB_BLOCK_SIZE.
To return the minimum and maximum salaries for each department in the employee table, issue the following statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp GROUP BY deptno; DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 10 1300 5000 20 800 3000 30 950 2850
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno; DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 10 1300 1300 20 800 1100 30 950 950
Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query. Oracle processes the WHERE, GROUP BY, and HAVING clauses in the following manner:
Specify the GROUP BY and HAVING clauses after the WHERE and
CONNECT BY clauses. If both the GROUP BY and HAVING clauses are specified,
they can appear in either order.
To return the minimum and maximum salaries for the clerks in each department whose lowest salary is below $1,000, issue the following statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal) < 1000; DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 20 800 1100 30 950 950
The UNION, UNION ALL, INTERSECT, and MINUS operators combine
the results of two queries into a single result. The number and datatypes
of the columns selected by each component query must be the same, but the
column lengths can be different. For information, see "Set
Operators".
If more than two queries are combined with set operators,
adjacent pairs of queries are evaluated from left to right. You can use
parentheses to specify a different order of evaluation.
The total number of bytes in all select list expressions
of a component query is 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.
You cannot use these set operators
to combine the results of queries that use the THE or MULTISET keywords.
Use the ORDER BY clause to order the rows selected by a query.
Without an ORDER BY clause, it is not guaranteed that the same query executed
more than once will retrieve rows in the same order. The clause specifies
either expressions or positions or aliases of expressions in the select
list of the statement. Oracle returns rows based on their values for these
expressions.
You can specify multiple expressions in the ORDER BY clause.
Oracle first sorts rows based on their values for the first expression.
Rows with the same value for the first expression are then sorted based
on their values for the second expression, and so on. Oracle sorts nulls
following all others in ascending order and preceding all others in descending
order.
Sorting by position is useful in the following cases:
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 Oracle8
Reference. You can also change the sort mechanism dynamically from
one linguistic sort sequence to another using the ALTER SESSION command.
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.
The ORDER BY clause is subject to the following restrictions:
If you use the ORDER BY and GROUP BY clauses together, the
expressions that can appear in the ORDER BY clause are subject to the same
restrictions as the expressions in the select list, described in the "GROUP
BY Clause".
If you use the ORDER BY clause in a hierarchical query, Oracle
uses the ORDER BY clause rather than the hierarchy to order the rows.
To select all salesmen's records from EMP, and order the results by commission in descending order, issue the following statement:
SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC;
To select the employees from EMP ordered first by ascending department number and then by descending salary, issue the following statement:
SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement:
SELECT ename, deptno, sal FROM emp ORDER BY 2 ASC, 3 DESC;
The FOR UPDATE clause locks the rows selected by the query.
Once you have selected a row for update, other users cannot lock or update
it until you end your transaction. The FOR UPDATE clause signals that you
intend to insert, update, or delete the rows returned by the query, but
does not require that you perform one of these operations. A SELECT statement
with a FOR UPDATE clause is often followed by one or more UPDATE statements
with WHERE clauses.
The FOR UPDATE clause cannot be used with the following other constructs:
The tables locked by the FOR UPDATE clause must all be located
on the same database. These locked tables must also be on the same database
as any LONG columns and sequences referenced in the same statement.
If a row selected for update is currently locked by another
user, Oracle waits until the row is available, locks it, and then returns
control to you. You can use the NOWAIT option to cause Oracle to terminate
the statement without waiting if such a row is already locked.
The rows returned from subqueries
whose column value is a nested table or a VARRAY, not a scalar value, are
not locked. Only the top-level rows of such select lists are locked.
Prior to updating a LOB value, you must lock the row containing
the LOB. One way to lock the row is with a SELECT... FOR UPDATE statement.
INSERT INTO t_table VALUES (1, 'abcd'); COMMIT; DECLARE num_var NUMBER; clob_var CLOB; clob_locked CLOB; write_amount NUMBER; write_offset NUMBER; buffer VARCHAR2(20) := 'efg'; BEGIN SELECT clob_col INTO clob_locked FROM t_table WHERE num_col = 1 FOR UPDATE; write_amount := 3; dbms_lob.write(clob_locked, write_amount, write_offset, buffer); END;
The columns in the OF clause only specify which tables' rows
are locked. The specific columns of the table that you specify are not
significant. If you omit the OF clause, Oracle locks the selected rows
from all the tables in the query.
The following statement locks rows in the EMP table with clerks located in New York and locks rows in the DEPT table with departments in New York that have clerks:
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE;
The following statement locks only those rows in the EMP table with clerks located in New York; no rows are locked in the DEPT table:
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE OF emp.sal;
A join is a query that combines rows from two or more
tables, views, or 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.
This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- CLARK MANAGER 10 ACCOUNTING KING PRESIDENT 10 ACCOUNTING MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH SCOTT ANALYST 20 RESEARCH JONES MANAGER 20 RESEARCH ALLEN SALESMAN 30 SALES BLAKE MANAGER 30 SALES MARTIN SALESMAN 30 SALES JAMES CLERK 30 SALES TURNER SALESMAN 30 SALES WARD SALESMAN 30 SALES
You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle combines rows of the two tables according to this join condition:
emp.deptno = dept.deptno
The following equijoin returns the name, job, department number, and department name of all clerks:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES
This query is identical to Example XII except that it uses an additional WHERE clause condition to return only rows with a JOB value of 'CLERK':
job = 'CLERK'
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.
This query uses a self join to return the name of each employee along with the name of the employee's manager:
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno; Employees and their Managers ------------------------------- BLAKE works for KING CLARK works for KING JONES works for KING FORD works for JONES SMITH works for FORD ALLEN works for BLAKE WARD works for BLAKE MARTIN works for BLAKE SCOTT works for JONES TURNER works for BLAKE ADAMS works for SCOTT JAMES works for BLAKE MILLER works for CLARK
The join condition for this query uses the aliases E1 and E2 for the EMP table:
e1.mgr = e2.empno
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.
The 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.
This is the basic syntax of an outer join of two tables:
Outer join queries are subject to the following rules and restrictions:
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.
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.
This query uses an outer join to extend the results of Example XIV:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno; ENAME JOB DEPTN DNAME ---------- --------- ---------- -------------- CLARK MANAGER 10 ACCOUNTING KING PRESIDENT 10 ACCOUNTING MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH SCOTT ANALYST 20 RESEARCH JONES MANAGER 20 RESEARCH ALLEN SALESMAN 30 SALES BLAKE MANAGER 30 SALES MARTIN SALESMAN 30 SALES JAMES CLERK 30 SALES TURNER SALESMAN 30 SALES WARD SALESMAN 30 SALES 40 OPERATIONS
In this outer join, Oracle returns a row containing the OPERATIONS
department even though no employees work in this department. Oracle returns
NULL in the ENAME and JOB columns for this row. The join query in Example
X selects only departments that have employees.
The following query uses an outer join to extend the results of Example XV:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES 40 OPERATIONS
In this outer join, Oracle returns a row containing the OPERATIONS
department even though no clerks work in this department. The (+) operator
on the JOB column ensures that rows for which the JOB column is NULL are
also returned. If this (+) were omitted, the row containing the OPERATIONS
department would not be returned because its JOB value is not 'CLERK'.
This example shows four outer join queries on the CUSTOMERS, ORDERS, LINEITEMS, and PARTS tables. These tables are shown here:
SELECT custno, custname FROM customers; CUSTNO CUSTNAME ---------- -------------------- 1 Angelic Co. 2 Believable Co. 3 Cabels R Us SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM orders; ORDERNO CUSTNO ORDERDATE ---------- ---------- ----------- 9001 1 OCT-13-1993 9002 2 OCT-13-1993 9003 1 OCT-20-1993 9004 1 OCT-27-1993 9005 2 OCT-31-1993 SELECT orderno, lineno, partno, quantity FROM lineitems; ORDERNO LINENO PARTNO QUANTITY ---------- ---------- ---------- ---------- 9001 1 101 15 9001 2 102 10 9002 1 101 25 9002 2 103 50 9003 1 101 15 9004 1 102 10 9004 2 103 20 SELECT partno, partname FROM parts; PARTNO PARTNAME ------ -------- 101 X-Ray Screen 102 Yellow Bag 103 Zoot Suit
Note that the customer Cables R Us has placed no orders and
that order number 9005 has no line items.
The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM customers, orders WHERE customers.custno = orders.custno (+); CUSTNAME ORDERDATE -------------------- -------------- Angelic Co. OCT-13-1993 Angelic Co. OCT-20-1993 Angelic Co. OCT-27-1993 Believable Co. OCT-13-1993 Believable Co. OCT-31-1993 Cables R Us
The following outer join builds on the result of the previous one by adding the LINEITEMS table to the FROM clause, columns from this table to the select list, and a join condition joining this table to the ORDERS table to the WHERE clause. This query joins the results of the previous query to the LINEITEMS table and returns all customers, the dates they placed orders, and the part number and quantity of each part they ordered. The first (+) operator serves the same purpose as in the previous query. The second (+) operator ensures that orders with no line items are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", partno, quantity FROM customers, orders, lineitems WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+); CUSTNAME ORDERDATE PARTNO QUANTITY -------------------- -------------- ---------- ---------- Angelic Co. OCT-13-1993 101 15 Angelic Co. OCT-13-1993 102 10 Angelic Co. OCT-20-1993 101 15 Angelic Co. OCT-27-1993 102 10 Angelic Co. OCT-27-1993 103 20 Believable Co. OCT-13-1993 101 25 Believable Co. OCT-13-1993 103 50 Believable Co. OCT-31-1993 Cables R Us
The following outer join builds on the result of the previous one by adding the PARTS table to the FROM clause, the PARTNAME column from this table to the select list, and a join condition joining this table to the LINEITEMS table to the WHERE clause. This query joins the results of the previous query to the PARTS table to return all customers, the dates they placed orders, and the quantity and name of each part they ordered. The first two (+) operators serve the same purposes as in the previous query. The third (+) operator ensures that rows with NULL part numbers are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", quantity, partname FROM customers, orders, lineitems, parts WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+) AND lineitems.partno = parts.partno (+); CUSTNAME ORDERDATE QUANTITY PARTNAME -------------------- -------------- ---------- ------------ Angelic Co. OCT-13-1993 15 X-Ray Screen Angelic Co. OCT-13-1993 10 Yellow Bag Angelic Co. OCT-20-1993 15 X-Ray Screen Angelic Co. OCT-27-1993 10 Yellow Bag Angelic Co. OCT-27-1993 20 Zoot Suit Believable Co. OCT-13-1993 25 X-Ray Screen Believable Co. OCT-13-1993 50 Zoot Suit Believable Co. OCT-31-1993 Cables R Us
To specify, for a particular transaction, whether a deferrable
constraint is checked following each DML statement (IMMEDIATE) or when
the transaction is committed (DEFERRED). For illustrations, see "Examples".
Setting when a deferrable constraint is checked requires
that the table to which the constraint applies must be in your own schema
or you must have SELECT privilege on the table.
The following example sets all deferrable constraints in this transaction to be checked immediately following each DML statement:
SET CONSTRAINTS ALL IMMEDIATE;
The following statement checks three deferred constraints when the transaction is committed:
SET CONSTRAINTS unq_name, scott.nn_sal, adams.pk_dept@dblink DEFERRED;
To enable and disable roles for your current session. For
illustrations, see "Examples".
You must already have been granted the roles that you name
in the SET ROLE statement. See also "Privilege Domains".
At logon, Oracle establishes your default privilege domain
by enabling your default roles. Your default privilege domain contains
all privileges granted explicitly to you and all privileges in the privilege
domains of your default roles. You can then perform any operations authorized
by the privileges in your default privilege domain.
During your session, you can change your privilege domain
with the SET ROLE command, which changes the roles currently enabled for
your session. You can change your enabled roles any number of times during
a session. The number of roles that can be concurrently enabled is limited
by the initialization parameter MAX_ENABLED_ROLES.
You can use the SET ROLE command to enable or disable any of the following roles:
You cannot use the SET ROLE command to enable roles that
you have not been granted either directly or through other roles.
Your current privilege domain is also changed in the following cases:
If none of the above conditions occur and you do not issue
the SET ROLE command, your default privilege domain remains in effect for
the duration of your session. In the last two cases, the change in your
privilege domain does not take effect until you log on to Oracle again
or issue a SET ROLE statement.
You can determine which roles are in your current privilege
domain at any time by examining the SESSION_ROLES data dictionary view.
To change your default roles, use the ALTER USER command.
To enable the role GARDENER identified by the password MARIGOLDS for your current session, issue the following statement:
SET ROLE gardener IDENTIFIED BY marigolds;
To enable all roles granted to you for the current session, issue the following statement:
SET ROLE ALL;
To enable all roles granted to you except BANKER, issue the following statement:
SET ROLE ALL EXCEPT banker IV;
To disable all roles granted to you for the current session, issue the following statement:
SET ROLE NONE;
are available only if the Oracle objects option is installed on your database
server.
WITH_clause::=
THE 
SELECT statements using THE or MULTISET
keywords cannot be used with these set operators. See also 
CURSOR operator