DBA > Articles

ANSI ISO SQL1999

By: menon srivalsala kumar
To read more DBA articles, visit http://dba.fyicenter.com/article/

1. Introduction
Running sqls on various database and Standardizing the sqls will be the best option in Development of Application. We can make the sqls are standardizes using ANSI/ISO SQL:1999:

The various sqls joins available herein below:
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 onwards the following SQL:1999-compliant joins are available:
1.1 NATURAL Join
1.2 CROSS 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 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 empand departments have one common
column, department_id.
SELECT employee_id, last_name, department_id FROM emp
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 empand 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 emp
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 emp
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 emp
JOIN job_history
ON (employees.employee_id=job_history.employee_id)
WHERE mgrid=3;

The following illustrates a self-join of the emptable to itself based on the employee_id and manager_id columns:
SELECT e.last_name employee, m.last_name manager
FROM empe
JOIN empm
ON (e.manager_id=m.employee_id);

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 emp
CROSS JOIN departments;

This is equivalent to the following:
SELECT last_name, department_name FROM employees, departments;

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 empare assigned to it.
SELECT employee_id, department_name
FROM departments d
LEFT OUTER JOIN empe
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, empe
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 empe
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 empe
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.

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/