[oracle] Oracle9i ANSI/ISO SQL:1999-Compliant SQL
- From: "Osy Mad" <osymad@xxxxxxxxxxx>
- To: oracle@xxxxxxxxxxxxx
- Date: Wed, 03 Dec 2003 22:50:28 +0000
Subject: Oracle9i ANSI/ISO SQL:1999-Compliant SQL
Content Type: TEXT/PLAIN
Creation Date: 21-MAR-2001
Last Revision Date: 02-JAN-2003
PURPOSE
This article is intended to introduce the new types of SQL statements
available in Oracle9i which support ANSI/ISO SQL:1999 standards.
SCOPE &APPLICATION
This article assumes the reader has an understanding of SQL and the
current
syntax.
RELATED DOCUMENTS
Oracle9i SQL Reference
Oracle9i ANSI/ISO SQL:1999-compliant SQL
========================================
This article introduces new features for conformance to ANSI/ISO SQL:1999:
1. SQL:1999 joins
2. CASE expressions
3. Explicit defaults
4. WITH clause
1. SQL:1999 Joins
-----------------
SQL:1999 join syntax differs from traditional Oracle join syntax in that the
join type is specified explicitly in the FROM clause.
Oracle9i introduces the following SQL:1999-compliant joins:
1.1 CROSS Join
1.2 NATURAL Join
1.3 OUTER Join
1.3.1 LEFT OUTER Join
1.3.2 RIGHT OUTER Join
1.3.3 FULL OUTER Join
1.1 CROSS Join
--------------
A CROSS join is the cross-product of two tables. It is the equivalent of a
Cartesian product.
Example:
SELECT last_name, department_name
FROM employees
CROSS JOIN departments;
This is equivalent to the following:
SELECT last_name, department_name
FROM employees, departments;
1.2 NATURAL Join
---------------
A NATURAL join selects rows from the tables which have equal values in all
matched columns(same column names). If the columns having the same names
have different datatypes, an error is returned.
If SELECT * syntax is used, the common columns appear only once in the
result
set.
Qualifiers such as table names or aliases may not be used for those columns
involved in the natural join.
Example:
For this example, assume tables employees and departments have one
common
column, department_id.
SELECT employee_id, last_name, department_id
FROM employees
NATURAL JOIN departments;
This is equivalent to the following:
SELECT employee_id, last_name, departments.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
If several columns have the same names but all of the datatypes do not
match,
then the NATURAL JOIN can be modified to a join with a USING clause which
specifies which columns should be used for the equi-join.
The columns in the USING clause should also not have a qualifier (table name
or alias) anywhere in the SQL statement.
The key words NATURAL and USING are mutually exclusive.
Example:
Assume tables employees and job_history have two columns in common,
employee_id and department_id. The following query joins the two tables
together using only the employee_id as a join condition.
SELECT employee_id, last_name, job_history.department_id
FROM employees
JOIN job_history
USING (employee_id);
This is equivalent to the following:
SELECT employees.employee_id, last_name, job_history.department_id
FROM employees, job_history
WHERE employees.employee_id=job_history.employee_id;
To specify arbitrary conditions or specify columns to be used in the join,
the
ON clause is used. This also separates the join condition from other
conditions.
It can also be used to specify a join for which the columns have different
names. Another use for the ON clause is simply to make the SQL easier to
read
and understand by explicitly stating the join condition.
Examples:
The following is equivalent to the example above for the USING clause.
SELECT employees.employee_id, last_name, job_history.department_id
FROM employees
JOIN job_history
ON (employees.employee_id=job_history.employee_id);
This example adds an additional where clause to the query:
SELECT employees.employee_id, last_name, job_history.department_id
FROM employees
JOIN job_history
ON (employees.employee_id=job_history.employee_id)
WHERE mgrid=3;
The following illustrates a self-join of the employees table to itself based
on
the employee_id and manager_id columns:
SELECT e.last_name employee, m.last_name manager
FROM employees e
JOIN employees m
ON (e.manager_id=m.employee_id);
1.3 OUTER Join
--------------
The join of two tables returning only matched rows is considered an INNER
JOIN.
A join between two tables which returns the result of the INNER join as well
as unmatched rows from the LEFT(or RIGHT) table is a LEFT(or RIGHT) OUTER
join.
INNER and OUTER JOINS were available in prior relaeses. The '(+)' symbol was
used on one of the tables to create outer joins.
1.3.1 LEFT OUTER Join
---------------------
A join between two tables which returns the result of the INNER join as well
as
unmatched rows from the LEFT table is a LEFT OUTER join.
Example:
This query returns rows from the departments table even if no employees
are
assigned to it.
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (e.department_id= d.department_id);
This is equivalent to the following pre-Oracle9i outer join notation:
SELECT employee_id, department_name
FROM departments d, employees e
WHERE e.department_id(+) = d.department_id;
1.3.2 RIGHT OUTER Join
----------------------
A join between two tables which returns the result of the INNER join as well
as
unmatched rows from the RIGHT table is a RIGHT OUTER join.
Example:
This query returns rows from the customers table even if this customer
has
not placed any orders.
SELECT cust_last_name, order_id,order_date
FROM orders o
RIGHT OUTER JOIN customers c
ON (o.customer_id = c.customer_id);
This is equivalent to the following pre-Oracle9i outer join notation:
SELECT cust_last_name, order_id, order_date
FROM orders o, customers c
WHERE c.customer_id = o.customer_id(+);
1.3.3 FULL OUTER Join
---------------------
A join between two tables which returns the result of the INNER join as well
as
unmatched rows from the LEFT and RIGHT outer joins is a FULL OUTER join.
This
is a new type of join in Oracle9i which did not exist previously.
Example:
This query returns locations which have no corresponding country and
countries which have no locations assigned.
SELECT l.city, c.country_name
FROM locations l
FULL OUTER JOIN countries c
ON (l.country_id = c.country_id);
This could have been accomplished in earlier versions using a UNION:
SELECT l.city, c.country_name
FROM locations l, countries c
WHERE l.country_id = c.country_id(+)
UNION
SELECT l.city, c.country_name
FROM locations l, countries c
WHERE l.country_id(+) = c.country_id;
Restrictions
------------
Use of these new joins is allowed in the definition of materialized views.
However, query rewrite is only possible if the query could also be expressed
using traditional Oracle syntax. The same restrictions apply when
determining
whether the materialized view can be fast refreshed.
For example, the following materialized view can make use of query rewrite:
CREATE MATERIALIZED VIEW mv1 AS
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (e.department_id= d.department_id);
But, this query cannot use query rewrite because the use of subqueries was
not
permitted with outer joins:
CREATE MATERIALIZED VIEW mv2 AS
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN employees e
ON (e.department_id= d.department_id)
WHERE e.department_id >10;
The columns that are referenced in the USING or ON clause cannot be a
collection or LOB type.
2. CASE statements
------------------
There are 4 types of CASE logic available in SQL:1999:
2.1 Simple CASE
2.2 Searched CASE
2.3 NULLIF
2.4 COALESCE
2.1 Simple CASE
---------------
The simple CASE is similar to DECODE. It was available in Oracle 8.1.7. It
can
be used to search and replace values within a given expression. You can
specify
a return value for each searched value. No comparison operators can be used
in
the simple CASE.
Example:
Assume the employees table has a salgrade column.
SELECT employee_id,
CASE salgrade WHEN 'A' THEN 'Low'
WHEN 'B' THEN 'Medium'
WHEN 'C' THEN 'High' END "Salary Grade"
FROM employees;
It is also possible to have an ELSE clause for a default.
The above query is equivalent to the following with DECODE:
SELECT employee_id,
DECODE(salgrade,'A','Low','B','Medium','C','High')
FROM employees;
2.2 Searched CASE
-----------------
The searched CASE is similar to IF..THEN..ELSE. Searched CASE was available
beginning in version 8.1.6.
Each WHEN clause can be different and logical operators can be used to
combine
multiple conditions. Comparison operators are also allowed.
Example:
SELECT employee_id,
CASE
WHEN salary >=75000 THEN 'High'
WHEN salary <=30000 THEN 'Low'
ELSE 'Medium'
END "Salary"
FROM employees;
2.3 NULLIF
----------
NULLIF returns NULL if the first argument is equal to the second. Otherwise,
the first value of the first argument is returned.
Example:
SELECT employee_id,
NULLIF(commission_pct,.1) "new commission"
FROM employees;
2.4 COALESCE
------------
COALESCE returns the first argument (beginning on the left) that is NOT
NULL.
It is similar to the NVL function, but it can take multiple alternate
values.
It accepts any number of arguments.
Example:
SELECT employee_id,
COALESCE(to_char(commission_pct),'none') commission
FROM employees;
3. Explicit Defaults
--------------------
If a column has a default value defined, the explicit default allows the
DEFAULT keyword to be used in an INSERT or UPDATE statement. It can also be
used in bind variables. The use of the explicit default eliminates any
ambiguity of which values will be placed in a column and avoids hard-coding
of
literals in applications.
Benefits of EXPLICIT DEFAULTS:
* provides better data integrity
* avoids the use of hard-coded litterals in applications
* more user-friendly and provide a more flexible interface
Examples:
Assume the employee table has a default defined for the department_id
column.
INSERT INTO employees
(employee_id, first_name, last_name, department_id)
VALUES
(1, 'Tom', 'Smith', DEFAULT);
UPDATE employees
SET department_id = DEFAULT
WHERE department_id=20;
4. WITH clause
--------------
The WITH clause allows a query block to be assigned a name and used multiple
times in a query by referring to this name. This is very useful to reduce
the
cost of a query block which will need to be evaluated more than once in a
query
by materializing the query block.
Here is an example of a query which benefits from a WITH clause:
SELECT department_name, SUM(salary) dept_total
FROM employees, departments
WHERE employees.department_id=departments.department_id
GROUP BY department_name HAVING
SUM(salary) >(
SELECT sum(salary) * 1/3
FROM employees, departments
WHERE employees.department_id=departments.department_id
)
ORDER BY dept_total;
This query can be rewritten as follows:
WITH summary AS (
SELECT department_name, SUM(salary) dept_total
FROM employees, departments
WHERE employees.department_id=departments.department_id
GROUP BY department_name
)
SELECT department_name, dept_total
FROM summary
WHERE dept_total >(
SELECT SUM(dept_total) * 1/3
FROM summary
)
ORDER BY dept_total DESC;
By using the WITH clause, summarizing the department total more than once is
avoided.
Another example:
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;
.
----------------------------------------------------------------------------
MSN 8 helps ELIMINATE E-MAIL VIRUSES. [1]Get 2 months FREE*.
--- Links ---
1 http://g.msn.com/8HMAEN/2743??PS=
Other related posts:
- » [oracle] Oracle9i ANSI/ISO SQL:1999-Compliant SQL
