8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | Linux

Home » Articles » 9i » Here

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.

For more information see:

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. It is the equivalent of not specifying a join criteria.

ANSI/ISO Syntax Existing Syntax
SELECT e.employee_name,
       d.department_name
FROM   employees e
       CROSS JOIN departments d;
SELECT e.employee_name,
       d.department_name
FROM   employees e, departments d;

NATURAL JOIN

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

ANSI/ISO Syntax Existing Syntax
SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d;

Can't reference the join column in a filter, so this isn't allowed.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d
WHERE  d.department_id = 20

No equivalent syntax that doesn't reference columns.

[INNER] 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 e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id);

Can't reference the join column in a filter, so this isn't allowed.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id)
WHERE  d.department_id = 20;
SELECT d.department_name,
       e.employee_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

[INNER] 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,
       e.employee_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id;
SELECT d.department_name,
       e.employee_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_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;

{LEFT | RIGHT | FULL} [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 d.department_name,
       e.employee_name     
FROM   departments d
       LEFT OUTER JOIN employees e ON d.department_id = e.department_id;
SELECT d.department_name,
       e.employee_name
FROM   departments d,
       employees e
WHERE  d.department_id = e.department_id(+);

Table order is irrelevant for non-ANSI syntax. It is placement of "(+)" that counts.

SELECT d.department_name,
       e.employee_name     
FROM   employees e
       RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
SELECT d.department_name,
       e.employee_name
FROM   departments d,
       employees e
WHERE  d.department_id = e.department_id(+);

Table order is irrelevant for non-ANSI syntax. It is placement of "(+)" that counts.

SELECT d.department_name,
       e.employee_name     
FROM   employees e
       FULL OUTER JOIN departments d ON e.department_id = d.department_id;
SELECT d.department_name,
       e.employee_name      
FROM   employees e, departments d
WHERE  e.department_id = d.department_id (+)
UNION ALL
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id = e.department_id (+)
AND    e.employee_name IS NULL;

No equivalent as single join. Must use UNION or UNION ALL of two outer joins.

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 a 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.