WITH_clause::=
|
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. |
|
|
expr |
selects an expression. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
ORDER BY |
orders rows returned by the statement. |
|
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.
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.
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 ;
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.
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.
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.
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.
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;
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