Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
SAVEPOINT to UPDATE, 3 of 9
Use a SELECT
statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views.
See Also:
|
For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the SELECT
privilege on the table or materialized view.
For you to select rows from the base tables of a view,
SELECT
privilege on the view, and
SELECT
privilege on the base tables.
The SELECT
ANY
TABLE
system privilege also allows you to select data from any table or any materialized view or any view's base table.
select::=
select
subquery::=
subquery
subquery_factoring_clause
select_list
table_reference::=
table_reference
query_table_expression
sample_clause
subquery_restriction_clause::=
subquery_restriction_clause
table_collection_expression::=
table_collection_expression
joined_table
join_type
hierarchical_query_clause
connect_by_condition::=
connect_by_condition
group_by_clause
simple_grouping_clause::=
simple_grouping_clause
grouping_sets_clause::=
grouping_sets_clause
order_by_clause
for_update_clause
The subquery_factoring_clause (WITH
query_name) lets you assign names to subquery blocks. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.
You can specify this clause in any top-level SELECT
statement and in most types of subqueries. The query name is visible to all subsequent subqueries (except the subquery that defines the query name itself) and to the main query.
Restrictions:
FROM
subquery can contain the subquery_factoring_clause.
See Also:
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
See Also:
"Hints" and Oracle9i Database Performance Guide and Reference for the syntax and description of hints |
Specify DISTINCT
or UNIQUE
if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.
Restrictions:
DISTINCT
or UNIQUE
, 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
.
DISTINCT
if the select_list
contains LOB columns.
Specify ALL
if you want Oracle to return all rows selected, including all copies of duplicates. The default is ALL
.
Specify the asterisk to select all columns from all tables, views, or materialized views listed in the FROM
clause.
The select_list lets you specify the columns you want to retrieve from the database.
For query_name, specify a name already specified in the subquery_factoring_clause. You must have specified the subquery_factoring_clause in order to specify query_name
in the SELECT
list.
Specify the object name followed by a period and the asterisk to select all columns from the specified table, view, or materialized view. A query that selects rows from two or more tables, views, or materialized views is a join.
You can use the schema qualifier to select from a table, view, or materialized view in a schema other than your own. If you omit schema, Oracle assumes the table, view, or materialized view is in your own schema.
Specify an expression representing the information you want to select. A column name in this list can be qualified with schema only if the table, view, or materialized view containing the column is qualified with schema in the FROM
clause.
Specify a different name (alias) for the column expression. Oracle will use this alias 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.
Restrictions on the select_list:
The FROM
clause lets you specify the objects from which data is selected.
Use the query_table_expression clause to identify a table, view, materialized view, or partition, or to specify a subquery that identifies the objects.
The ONLY
clause applies only to views. Specify ONLY
if the view in the FROM
clause is a view belonging to a hierarchy and you do not want to include rows from any of its subviews.
For PARTITION
or SUBPARTITION
, specify the partition or subpartition from which you want to retrieve data. The partition 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.
For dblink, specify the complete or partial name for a database link to a remote database where the table, view, or materialized view is located. This database need not be an Oracle database.
See Also:
|
If you omit dblink, Oracle assumes that the table, view, or materialized view is on the local database.
Restriction: You cannot query a user-defined type or an object REF
on a remote table.
For table, view, or materialized view, specify the name of a table, view, or materialized view from which data is selected.
The sample_clause
lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.
BLOCK
instructs Oracle to perform random block sampling instead of random row sampling.
sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to (but not including) 100.
Restrictions on the sample_clause:
SAMPLE
only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE
TABLE
... AS
SELECT
query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.
SAMPLE
, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.
The subquery_restriction_clause lets you restrict the subquery in one of the following ways:
Specify WITH
READ
ONLY
to indicate that the subquery cannot be updated.
Specify WITH
CHECK
OPTION
to indicate that, if the subquery is used in place of a table in an INSERT
, UPDATE
, or DELETE
statement, Oracle prohibits any changes to that table that would produce rows that are not included in the subquery.
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a built-in function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting.
The collection_expression can reference columns of tables defined to its left in the FROM
clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.
The optional "(+)" lets you specify that table_collection_expression should return a row with all fields set to NULL
if the collection is null or empty. The "(+)" is valid only if collection_expression uses left correlation. The result is similar to that of an outer join.
Specify a correlation name (alias) for the table, view, materialized view, or subquery for evaluating the query. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.
Use the joined_table syntax to identify tables that are part of a join from which to select data.
The join_type indicates the kind of join being performed:
INNER
to indicate explicitly that an inner join is being performed. This is the default.
RIGHT
to indicate a right outer join.
LEFT
to indicate a left outer join.
FULL
to indicate a full or two-sided outer join. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join will be preserved and extended with nulls.
OUTER
keyword following RIGHT
, LEFT
, or FULL
to explicitly clarify that an outer join is being performed.
The JOIN
keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in Oracle joins with ANSI syntax.
Use the ON
clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE
clause.
When you are specifying an equijoin of columns that have the same name in both tables, the USING
column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Do not qualify the column name with a table name or table alias.
Restriction: You cannot specify a LOB column or a collection column in the USING
column clause.
The CROSS
keyword indicates that a cross join is being performed. A cross join produces the cross-product of two relations and is essentially the same as the comma-delimited Oracle notation.
The NATURAL
keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.
Restriction: You cannot specify a LOB column or a collection column as part of a natural join.
Note on Cross and Natural Joins:
On occasion, the table pairings in natural or cross joins may be ambiguous. For example:
a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1
can be interpreted in either of the following ways:
a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1) (a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1
To avoid this ambiguity, you can use parentheses to specify the pairings of joined tables. In the absence of such parentheses, Oracle uses left associativity, pairing the tables from left to right.
The WHERE
condition lets you restrict the rows selected to those that satisfy one or more conditions. For condition, specify any valid SQL condition.
If you omit this clause, Oracle returns all rows from the tables, views, or materialized views in the FROM
clause.
The hierarchical_query_clause lets you select rows in a hierarchical order. For a discussion of hierarchical queries, see "Hierarchical Queries".
SELECT
statements that contain hierarchical queries can contain 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, and so on. The number of levels returned by a hierarchical query may be limited by available user memory.
Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle uses as root(s) all rows that satisfy this condition. If you omit this clause, Oracle uses all rows in the table as root rows. The START
WITH
condition can contain a subquery, but it cannot contain a scalar subquery expression.
Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition
can be any condition as described in Chapter 5, "Conditions". However, it must use the PRIOR
operator to refer to the parent row.
Restriction: The connect_by_condition
cannot contain a regular subquery or a scalar subquery expression.
See Also:
|
Notes on Hierarchical Queries:
If you specify a hierarchical query and also specify the ORDER
BY
clause, the ORDER
BY
clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS
keyword in the ORDER
BY
clause.
The manner in which Oracle processes a
WHERE
clause (if any) in a hierarchical query depends on whether the WHERE
clause contains a join:
WHERE
predicate contains a join, Oracle applies the join predicates before doing the CONNECT
BY
processing.
WHERE
clause does not contain a join) after doing the CONNECT
BY
processing without affecting the other rows of the hierarchy.
group_by_clause
Specify the GROUP
BY
clause if you want Oracle to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group. If this clause contains CUBE
or ROLLUP
extensions, then Oracle produces superaggregate groupings in addition to the regular groupings.
Expressions in the GROUP
BY
clause can contain any columns of the tables, views, or materialized views in the FROM
clause, regardless of whether the columns appear in the select list.
See Also:
|
The ROLLUP
operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP
BY
specification, and returns a single row of summary for each group. You can use the ROLLUP
operation to produce subtotal values by using it with the SUM
function. When used with SUM
, ROLLUP
generates subtotals from the most detailed level to the grand total. Aggregate functions such as COUNT
can be used to produce other kinds of superaggregates.
For example, given three expressions (n=3) in the ROLLUP
clause of the simple_grouping_clause, the operation results in n+1 = 3+1 = 4 groupings.
Rows grouped on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.
The CUBE
operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification, and returns a single row of summary information for each group. You can use the CUBE
operation to produce cross-tabulation values.
For example, given three expressions (n=3) in the CUBE
clause of the simple_grouping_clause, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.
GROUPING
SETS
are a further extension of the GROUP
BY
clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation. You specify just the desired groups, and Oracle does not need to perform the full set of aggregations generated by CUBE
or ROLLUP
. Oracle computes all groupings specified in the GROUPING
SETS
clause and combines the results of individual groupings with a UNION
ALL
operation. The UNION
ALL
means that the result set can include duplicate rows.
Within the GROUP
BY
clause, you can combine expressions in various ways:
ROLLUP
or CUBE
operations.
ROLLUP
, and CUBE
operations with commas so that Oracle combines them into a single GROUP
BY
clause. The result is a cross-product of groupings from each grouping set.
Use the HAVING
clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE
. If you omit this clause, Oracle returns summary rows for all groups.
Specify GROUP
BY
and HAVING
after the where_clause and CONNECT
BY
clause. If you specify both GROUP
BY
and HAVING
, they can appear in either order.
Restriction: The HAVING
condition cannot contain a scalar subquery expression.
DB_BLOCK_SIZE
) minus some overhead.
the syntax description of expr in "About SQL Expressions" and the syntax description of condition in Chapter 5, "Conditions"
See Also:
These set operators combine the rows returned by two SELECT
statements 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.
If you combine more than two queries with set operators, Oracle evaluates adjacent queries from left to right. You can use parentheses to specify a different order of evaluation.
Restrictions on set operators:
BLOB
, CLOB
, BFILE
, varray, or nested table.
UNION
, INTERSECT
, and MINUS
operators are not valid on LONG
columns.
for_update_clause
with these set operators.
order_by_clause
in the subquery
of these operators.
SELECT
statements containing TABLE
collection expressions.
Use the ORDER
BY
clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
The SIBLINGS
keyword is valid only if you also specify the hierarchical_query_clause (CONNECT
BY
). Oracle preserves any ordering specified in the hierarchical query clause then applies the order_by_clause to the siblings of the hierarchy.
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 materialized views in the FROM
clause.
Specify position to order rows based on their value for the expression in this position of the select list; position must be an integer.
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.
Specify whether the ordering sequence is ascending or descending. ASC
is the default.
Specify whether returned rows containing null values should appear first or last in the ordering sequence.
NULLS
LAST
is the default for ascending order, and NULLS
FIRST
is the default for descending order.
Restrictions on the order_by_clause:
DISTINCT
operator in this statement, this clause cannot refer to columns unless they appear in the select list.
The FOR
UPDATE
clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT
statement (not in subqueries).
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.
Nested table rows are not locked as a result of locking the parent table rows. If you want the nested table rows to be locked, you must lock them explicitly.
Restrictions:
DISTINCT
or CURSOR
operator, set operators, group_by_clause, or aggregate functions.
LONG
columns and sequences referenced in the same statement.
Use the OF
... column clause to lock the select rows only for a particular table or view in a join. The columns in the OF
clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, Oracle locks the selected rows from all the tables in the query.
The NOWAIT
and WAIT
clauses let you tell Oracle how to proceed if the SELECT
statement attempts to lock a row that is locked by another user.
Specify NOWAIT
to return control to you immediately if a lock exists.
Specify WAIT
to instruct Oracle to wait integer seconds for the row to become available, and then return control to you.
If you specify neither WAIT
nor NOWAIT
, Oracle waits until the row is available and then returns the results of the SELECT
statement.
The following statement creates the query names dept_costs
and avg_cost
for the initial query block containing a join, and then uses the query names in the body of the main query.
WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name; DEPARTMENT_NAME DEPT_TOTAL ------------------------------ ---------- Sales 313800 Shipping 156400
The following statement selects rows from the employees
table with the department number of 30:
SELECT * FROM employees WHERE department_id = 30;
The following statement selects the name, job, salary and department number of all employees except purchase clerks from department number 30:
SELECT last_name, job_id, salary department_id FROM employees WHERE NOT (job_id = 'PU_CLERK' AND department_id = 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.department_id "Department", a.num_emp/b.total_count "%_Employees", a.sal_sum/b.total_sal "%_Salary" FROM (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum FROM employees GROUP BY department_id) a, (SELECT COUNT(*) total_count, SUM(salary) total_sal FROM employees) b;
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 sales_q2_2000
partition of the demo table sh.sales
:
SELECT * FROM sales PARTITION (sales_q2_2000) s WHERE s.amount_sold > 1000;
The following example selects rows from the oe.orders
table for orders earlier than a specified date:
SELECT * FROM orders WHERE order_date < TO_DATE('1999-06-15', 'YYYY-MM-DD');
The following query estimates the number of orders in the oe.orders
table:
SELECT COUNT(*) * 100 FROM orders SAMPLE BLOCK (1);
The following example creates a sampled subset of the demo table hr.employees
table and then joins the resulting sampled table with departments
. This operation circumvents the restriction that you cannot specify the sample_clause in join queries:
CREATE TABLE sample_emp AS SELECT employee_id, department_id FROM employees SAMPLE(10); SELECT e.employee_id FROM sample_emp e, departments d WHERE e.department_id = d.department_id AND d.department_name = 'Sales';
To return the minimum and maximum salaries for each department in the employees
table, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id;
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT department_id, MIN(salary), MAX (salary) FROM employees WHERE job_id = 'PU_CLERK' GROUP BY department_id;
To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the demo tables hr.employees
and hr.departments
:
SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department_name, DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id, COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal" FROM employees e, departments d WHERE d.department_id = e.department_id GROUP BY CUBE (department_name, job_id); DEPARTMENT_NAME JOB_ID Total Empl Average Sal ------------------------------ ---------- ---------- ----------- Accounting AC_ACCOUNT 1 99600 Accounting AC_MGR 1 144000 Accounting All Jobs 2 121800 Administration AD_ASST 1 52800 . . . All Departments ST_MAN 5 87360 All Departments All Jobs 107 77798.1308
The following example finds the sum of sales aggregated for three precisely specified groups:
(channel_desc, calendar_month_desc, country_id)
(channel_desc, country_id)
(calendar_month_desc, country_id)
Without the GROUPING
SETS
syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and UNION
them, or run a query with a CUBE(channel_desc, calendar_month_desc, country_id)
operation and filter out 5 of the 8 groups it would generate.
SELECT channel_desc, calendar_month_desc, co.country_id, TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$ FROM sales, customers, times, channels, countries co WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id AND sales.channel_id= channels.channel_id AND customers.country_id = co.country_id AND channels.channel_desc IN ('Direct Sales', 'Internet') AND times.calendar_month_desc IN ('2000-09', '2000-10') AND co.country_id IN ('UK', 'US') GROUP BY GROUPING SETS( (channel_desc, calendar_month_desc, co.country_id), (channel_desc, co.country_id), ( calendar_month_desc, co.country_id) ); CHANNEL_DESC CALENDAR CO SALES$ -------------------- -------- -- -------------- Direct Sales 2000-09 UK 1,378,126 Direct Sales 2000-10 UK 1,388,051 Direct Sales 2000-09 US 2,835,557 Direct Sales 2000-10 US 2,908,706 Internet 2000-09 UK 911,739 Internet 2000-10 UK 876,571 Internet 2000-09 US 1,732,240 Internet 2000-10 US 1,893,753 Direct Sales UK 2,766,177 Direct Sales US 5,744,263 Internet UK 1,788,310 Internet US 3,625,993 2000-09 UK 2,289,865 2000-09 US 4,567,797 2000-10 UK 2,264,622 2000-10 US 4,802,459
The following query with a CONNECT
BY
clause defines a hierarchical relationship in which the employee_id
value of the parent row is equal to the manager_id
value of the child row:
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY employee_id = manager_id;
In the following CONNECT
BY
clause, the PRIOR
operator applies only to the employee_id
value. To evaluate this condition, Oracle evaluates employee_id
values for the parent row and manager_id
, salary
, and commission_pct
values for the child row:
SELECT last_name, employee_id, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id AND salary > commission_pct;
To qualify as a child row, a row must have a manager_id
value equal to the employee_id
value of the parent row and it must have a salary
value greater than its commission_pct
value.
HAVING
ExampleTo return the minimum and maximum salaries for the employees in each department whose lowest salary is below $5,000, issue the next statement:
SELECT department_id, MIN(salary), MAX (salary) FROM employees GROUP BY department_id HAVING MIN(salary) < 5000; DEPARTMENT_ID MIN(SALARY) MAX(SALARY) ------------- ----------- ----------- 10 4400 4400 30 2500 11000 50 2100 8200 60 4200 9000
ORDER
BY
Examples
To select all salesmen's records from employees
, and order the results by commission in descending order, issue the following statement:
SELECT * FROM employees WHERE job_id = 'PU_CLERK' ORDER BY commission_pct DESC;
To select information from employees
ordered first by ascending department number and then by descending salary, issue the following statement:
SELECT last_name, department_id, salary FROM employees ORDER BY department_id ASC, salary DESC;
To select the same information as the previous SELECT
and use the positional ORDER
BY
notation, issue the following statement:
SELECT last_name, department_id, salary FROM employees ORDER BY 2 ASC, 3 DESC;
The following statement locks rows in the employees
table with purchasing clerks located in Oxford (location_id
2500) and locks rows in the departments
table with departments in Oxford that have purchasing clerks:
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e, departments d WHERE job_id = 'SA_REP' AND e.department_id = d.department_id AND location_id = 2500 FOR UPDATE;
The following statement locks only those rows in the employees
table with purchasing clerks located in Oxford (location_id
2500). No rows are locked in the departments
table:
SELECT e.employee_id, e.salary, e.commission_pct FROM employees e, departments d WHERE job_id = 'SA_REP' AND e.department_id = d.department_id AND location_id = 2500 FOR UPDATE OF e.salary;
The following example uses a SELECT
... FOR
UPDATE
statement to lock a row containing a LOB prior to updating the LOB value.
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 following statement is legal even though the second value violates the condition of the subquery where_clause:
INSERT INTO
(SELECT employee_id, last_name, email, hire_date, job_id, salary
FROM employees WHERE department_id < 10)
VALUES (99999, 'Taylor', 'Taylor@oracle.com',
TO_DATE('07-JUN-99', 'DD-MON-YY'), 'PU_CLERK', 5000);
However, the following statement is illegal because it contains the WITH
CHECK
OPTION
clause:
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary FROM employees WHERE department_id < 10 WITH CHECK OPTION) VALUES (99999, 'Taylor', 'Taylor@oracle.com', TO_DATE('07-JUN-99', 'DD-MON-YY'), 'PU_CLERK', 5000); insert into * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation
The following examples show various ways of joining tables in a query. In the first example, an equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ---------------------- ... Sciarra FI_ACCOUNT 100 Finance Urman FI_ACCOUNT 100 Finance Popp FI_ACCOUNT 100 Finance ...
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:
employees.department_id = departments.department_id
The following equijoin returns the name, job, department number, and department name of all sales managers:
SELECT last_name, job_id, departments.department_id, department_name FROM employees, departments WHERE employees.department_id = departments.department_id AND job_id = 'SA_MAN'; LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME ------------------- ---------- ------------- ----------------------- Russell SA_MAN 80 Sales Partners SA_MAN 80 Sales Errazuriz SA_MAN 80 Sales Cambrault SA_MAN 80 Sales Zlotkey SA_MAN 80 Sales
This query is identical to the preceding example, except that it uses an additional where_clause condition to return only rows with a job
value of 'SA_MAN
'.
To determine who works in the same department as employee 'Lorentz
', issue the following statement:
SELECT last_name, department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Lorentz');
To give all employees in the employees
table a 10% raise if they have changed jobs (that is, if they appear in the job_history
table), issue the following statement:
UPDATE employees SET salary = salary * 1.1 WHERE employee_id IN (SELECT employee_id FROM job_history);
To create a second version of the departments
table new_departments
, with only three of the columns of the original table, issue the following statement:
CREATE TABLE new_departments (department_id, department_name, location_id) AS SELECT department_id, department_name, location_id FROM departments;
The following query uses a self join to return the name of each employee along with the name of the employee's manager. (A WHERE
clause is added to shorten the output.)
SELECT e1.last_name||' works for '||e2.last_name "Employees and Their Managers" FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name LIKE 'R%'; Employees and Their Managers ------------------------------- Rajs works for Mourgos Raphaely works for King Rogers works for Kaufling Russell works for King
The join condition for this query uses the aliases e1
and e2
for the sample table employees
:
e1.manager_id = e2.employee_id
The following example uses a left outer join to return the names of all departments, even if no employees have been assigned to them:
SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id;
Users familiar with the traditional Oracle outer joins syntax will recognize the same query in this form:
SELECT d.department_id, e.last_name FROM departments d, employees e WHERE d.department_id(+) = e.department_id ORDER BY d.department_id;
Oracle Corporation strongly recommends that you use the more flexible Oracle9i ANSI-compliant syntax shown in the former example.
The following example uses a right outer join to return the names of all locations, even if no departments are headquartered in them:
SELECT d.department_name, d.manager_id, l.city FROM departments d RIGHT OUTER JOIN locations l ON d.location_id = l.location_id ORDER BY d.department_name;
The following query uses a full outer join to return all rows from the customers table and all rows from the orders table. Rows that do not satisfy the ON
condition are extended with nulls:
SELECT c.customer_id, c.o.order_id, c.account_mgr_id, o.sales_rep_id FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id ORDER BY c.customer_id; CUSTOMER_ID ORDER_ID ACCOUNT_MGR_ID SALES_REP_ID ----------- ---------- -------------- ------------ . . . 133 149 134 135 136 149 137 149 138 149 139 140 141 2377 145 142 2378 149 143 2380 149 144 2435 149 159 144 2445 149 158 144 2363 149 144 2422 149 153 144 2382 149 145 2455 145 160 . . .
You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expr_clause of an INSERT
, DELETE
, or UPDATE
statement is a table_collection_expression, the collection expression must be a subquery that selects the table's nested table column. The examples that follow are based on this scenario:
CREATE TYPE ProjectType AS OBJECT( pno NUMBER, pname CHAR(31), budget NUMBER); CREATE TYPE ProjectSet AS TABLE OF ProjectType; CREATE TABLE dept_work (dno NUMBER, dname CHAR(31), projs ProjectSet) NESTED TABLE projs STORE AS ProjectSetTable ((Primary Key(Nested_Table_Id, pno)) ORGANIZATION INDEX COMPRESS 1); INSERT INTO dept_work VALUES (1, 'Engineering', ProjectSet());
This example inserts into the 'Engineering' department's 'projs
' nested table:
INSERT INTO TABLE(SELECT d.projs FROM dept_work d WHERE d.dno = 1) VALUES (1, 'Collection Enhancements', 10000);
This example updates the 'Engineering' department's 'projs
' nested table:
UPDATE TABLE(SELECT d.projs FROM dept_work d WHERE d.dno = 1) p SET p.budget = p.budget + 1000;
This example deletes from the 'Engineering' department's 'projs
' nested table
DELETE TABLE(SELECT d.projs FROM dept_work d WHERE d.dno = 1) p WHERE p.budget > 100000;
Suppose the database contains a table hr_info
with columns dept
, location
, and mgr
, and a column of nested table type people
which has name
, dept
, and sal
columns. You could get all the rows from hr_info
and all the rows from people
using the following statement:
SELECT t1.dept, t2.* FROM hr_info t1, TABLE(t1.people) t2 WHERE t2.dept = t1.dept;
Now suppose that people
is not a nested table column of hr_info
, but is instead a separate table with columns name
, dept
, address
, hiredate
, and sal. You can extract the same rows as in the preceding example with this statement:
SELECT t1.department, t2.* FROM hr_info t1, TABLE(CAST(MULTISET( SELECT t3.name, t3.dept, t3.sal FROM people t3 WHERE t3.dept = t1.dept) AS NESTED_PEOPLE)) t2;
Finally, suppose that people
is neither a nested table column of table hr_info
nor a table itself. Instead, you have created a function people_func
that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:
SELECT t1.dept, t2.* FROM HY_INFO t1, TABLE(CAST (people_func( ... ) AS NESTED_PEOPLE)) t2;
See Also:
Oracle9i Application Developer's Guide - Fundamentals for more examples of collection unnesting. |
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'AD_VP
'. 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)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Greenberg 108 101 FI_MGR Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
The following statement is similar to the previous one, except that it does not select employees with the job 'FI_MAN
'.
SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart, employee_id, manager_id, job_id FROM employees WHERE job_id != 'FI_MGR' START WITH job_id = 'AD_VP' CONNECT BY PRIOR employee_id = manager_id; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- Kochhar 101 100 AD_VP Faviet 109 108 FI_ACCOUNT Chen 110 108 FI_ACCOUNT Sciarra 111 108 FI_ACCOUNT Urman 112 108 FI_ACCOUNT Popp 113 108 FI_ACCOUNT Whalen 200 101 AD_ASST Mavris 203 101 HR_REP Baer 204 101 PR_REP Higgins 205 101 AC_MGR Gietz 206 205 AC_ACCOUNT De Haan 102 100 AD_VP Hunold 103 102 IT_PROG Ernst 104 103 IT_PROG Austin 105 103 IT_PROG Pataballa 106 103 IT_PROG Lorentz 107 103 IT_PROG
Oracle does not return the manager greenberg
, although it does return employees who are managed by greenberg
.
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)) || last_name org_chart, employee_id, manager_id, job_id FROM employees START WITH job_id = 'AD_PRES' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2; ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID ------------------ ----------- ---------- ---------- King 100 AD_PRES Kochhar 101 100 AD_VP De Haan 102 100 AD_VP Raphaely 114 100 PU_MAN Weiss 120 100 ST_MAN Fripp 121 100 ST_MAN Kaufling 122 100 ST_MAN Vollman 123 100 ST_MAN Mourgos 124 100 ST_MAN Russell 145 100 SA_MAN Partners 146 100 SA_MAN Errazuriz 147 100 SA_MAN Cambrault 148 100 SA_MAN Zlotkey 149 100 SA_MAN Hartstein 201 100 MK_MAN
This example shows a query that joins the departments
table on the local database with the employees
table on the houston
database:
SELECT last_name, department_name FROM employees@houston, departments WHERE employees.department_id = departments.department_id;
The following examples show the general syntax of a correlated subquery:
SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column);
The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to employees
, the table containing the salary information, and then uses the alias in a correlated subquery:
SELECT department_id, last_name, salary FROM employees x WHERE salary > (SELECT AVG(salary) FROM employees WHERE x.department_id = department_id) ORDER BY department_id;
For each row of the employees
table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the employees
table:
department_id
of the row is determined.
department_id
is then used to evaluate the parent query.
The subquery is evaluated once for each row of the employees
table.
The following statement returns the current date:
SELECT SYSDATE FROM DUAL;
You could select SYSDATE
from the employees
table, but Oracle would return 14 rows of the same SYSDATE
, one for every row of the employees
table. Selecting from DUAL
is more convenient.
The following statement increments the employees_seq sequence and returns the new value:
SELECT employees_seq.nextval FROM dual;
The following statement selects the current value of employees_seq:
SELECT employees_seq.currval FROM dual;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|