Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

ANSI/ISO SQL Support In Oracle 9i

Oracle 9i now supports the ANSI/ISO SQL: 1999 standards. This allows easier product migration and a reduced learning curve when cross-training, but there is no performance increase compared to the existing syntax.

Joins

A range of new join syntax are available that comply with the ANSI/ISO SQL: 1999 standards.

CROSS JOIN

The CROSS JOIN produces a cartesian product.

ANSI/ISO Syntax Existing Syntax
SELECT first_name,
       last_name,
       department_name
FROM   employees
       CROSS JOIN departments;
SELECT first_name,
       last_name,
       department_name
FROM   employees,
       departments;

NATURAL JOIN

The NATURAL JOIN performs a join for all columns with matching names in the two tables.

ANSI/ISO Syntax Existing Syntax
SELECT department_name,
       city
FROM   departments
       NATURAL JOIN locations;
SELECT d.department_name,
       l.city
FROM   departments d,
       locations l
WHERE  d.location_id = l.location_id
AND    d.country     = l.country;

JOIN ... USING

The USING clause is used if several columns share the same name, but you do not wish to join using all of these common columns. The columns listed in the USING clause cannot have any qualifiers in the statement, including the WHERE clause.

ANSI/ISO Syntax Existing Syntax
SELECT d.department_name,
       l.city
FROM   departments d
       JOIN locations l USING (location_id);
SELECT d.department_name,
       l.city
FROM   departments d,
       locations l
WHERE  d.location_id = l.location_id;

JOIN ... ON

The ON clause is used to join tables where the column names do not match. The join conditions are removed from the filter conditions in the where clause.

ANSI/ISO Syntax Existing Syntax
SELECT d.department_name,
       l.city
FROM   departments d
       JOIN locations l ON (d.location_id = l.id);
SELECT d.department_name,
       l.city
FROM   departments d,
       locations l
WHERE  d.location_id = l.id;

Multiple Joins

Multiple Joins are those where more than two tables are joined. The SQL: 1999 standard assumes the tables are joined from the left to the right, with the join conditions only being able to reference columns relating to the current join and any previous joins to the left.

ANSI/ISO Syntax Existing Syntax
SELECT employee_id,
       city,
       department_name
FROM   locations l
       JOIN departments d ON (d.location_id = l.location_id)
       JOIN employees e ON (d.department_id = e.department_id);
SELECT employee_id,
       city,
       department_name
FROM   locations l,
       departments d,
       employees e
WHERE  d.location_id   = l.location_id
AND    d.department_id = e.department_id;

OUTER JOIN

There are three variations on the outer join. The LEFT OUTER JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist. The RIGHT OUTER JOIN does the reverse of this. Finally, the FULL OUTER JOIN returns all rows from both tables, filling in any blanks with nulls.

ANSI/ISO Syntax Existing Syntax
SELECT e.last_name,
       d.department_name
FROM   employees e
       LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
SELECT e.last_name,
       d.department_name
FROM   employees e,
       departments d
WHERE  e.department_id = d.department_id(+);
SELECT e.last_name,
       d.department_name
FROM   employees e
       RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
SELECT e.last_name,
       d.department_name
FROM   employees e,
       departments d
WHERE  e.department_id(+) = d.department_id;
SELECT e.last_name,
       d.department_name
FROM   employees e
       FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
No Equivalent!

Extra filter conditions can be added to the join to using AND to form a complex join. These are often necessary when filter conditions are required to restrict an outer join. If these filter conditions are placed in the WHERE clause and the outer join returns a NULL value for the filter column the row would be thrown away. If the filter condition is coded as part of the join the situation can be avoided.

CASE Expressions

The case expression is a more flexible extension of the DECODE statement. In its simplest form it is used to return a value when a match is found.

SELECT last_name, commission_pct,
  (CASE commission_pct
     WHEN 0.1  THEN 'Low'
     WHEN 0.15 THEN 'Average'
     WHEN 0.2  THEN 'High'
     ELSE 'N/A'
   END) Commission
FROM employees
ORDER BY last_name;

A more complex version is the Searched case expression where a comparison expression is used to find a match.

SELECT last_name, job_id, salary,
  (CASE
     WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%'
     WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
     WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
     WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
     ELSE 'NOT APPLICABLE'
  END) pay_raise
FROM employees;

Return values cannot be the literal NULL.

NULLIF Function

The NULLIF function returns a NULL value if both parameters are equal in value. If the parameters are not equal, it returns the value of the first parameter. The following query would return NULL.

SELECT NULLIF(1,1) FROM dual;

COALESCE Function

The COALESCE function returns the first non-NULL value in an expression list. If all expressions are null it returns NULL. The following query would return '3'.

SELECT COALESCE(NULL, NULL, '3') FROM dual;

Scalar Subqueries

Scalar subqueries return a single value. They could be used in previous versions of Oracle in some parts of an SQL statement, but Oracle9i extends their use to almost any place where an expression can be used, including:

For example.

INSERT INTO my_table VALUES ((SELECT 1 FROM dual), NULL);

SELECT Substr((SELECT 'ABC' FROM dual), 1, 1) FROM dual;

Scalar subqueries can only return a single column and a single row. If more than one row is returned an error is produced. If no rows are returned the value of NULL is used. The datatype of the returned value must match the datatype it is being compared against. Scalar subqueries cannot be used for the following:

Explicit Defaults

The DEFAULT keyword can be used to explicit assign the columns default value during an INSERT or UPDATE statement.

INSERT INTO my_table VALUES (1, 'OWNER', DEFAULT, NULL);

UPDATE my_table SET column1 = DEFAULT;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.