ANSI/ISO SQL Support In Oracle9i

• Joins In Oracle 9i
• CASE Statements In Oracle 9i
• NULLIF Function In Oracle 9i
• COALESCE Function In Oracle 9i
• Scalar Subqueries In Oracle 9i
• Explicit Defaults In Oracle 9i

Joins In Oracle 9i

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

The CROSS JOIN produces a cartesian product:

SELECT first_name,
       last_name,
       department_name
FROM   employees
       CROSS JOIN departments;

SELECT first_name,
       last_name,
       department_name
FROM   employees,
       departments;

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

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;

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:

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;

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:

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;

Mutable 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:

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;

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:

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 Statements In Oracle 9i

The case statement 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 In Oracle 9i

The NULLIF function returns a NULL value if both parameters are equal in value. The following query would return NULL:
 

SELECT NULLIF(1,1) FROM dual;

COALESCE Function In Oracle 9i

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 In Oracle 9i

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:
 

• CASE expressions
• SELECT statement
• VALUES clause of an INSERT statement
• WHERE clause
• ORDER BY clause
• As a parameter of a function

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:
 

• Default values for columns
• RETURNING clauses
• Hash expressions for clusters
• Functional index expressions
• CHECK constraints on columns
• WHEN condition of triggers
• GROUP BY and HAVING clauses
• START WITH and CONNECT BY clauses

Explicit Defaults In Oracle 9i

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;

Hope this helps. Regards Tim...