Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle simplifies SQL with ISO 99 Syntax

Oracle Tips by Burleson Consulting
August 2, 2002

Note: For more complete working details, see Laurent Schneider's book "Advanced Oracle SQL Programming: The Expert Guide to Writing Complex Queries".

 

Oracle has made some important enhancements to Oracle9i SQL, including a host of exciting new execution plans, support for scalar subqueries, and support for the ISO 99 SQL standard. As implemented by Oracle, this includes the following new TABLE JOIN syntax: 
  • CROSS JOIN:  This creates a Cartesian product of the rows in both tables, just like in Oracle8i when the WHERE clause is forgotten.
      
  • NATURAL JOIN:  This is a useful Oracle9i syntax feature that improves the readability of SQL by removing join criteria from the WHERE clause.
     
  • The USING clause:  This allows you to specify the join key by name.
      
  • The ON clause:  This syntax allows you to specify the column names for join keys in both tables.
     
  • LEFT OUTER JOIN:  This 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.
     
  • RIGHT OUTER JOIN:  This returns all the rows from the table on the right side of the join, along with the values from the left-hand side, or nulls if a matching row doesn't exist.
      
  • FULL OUTER JOIN:  This returns all rows from both tables, filling in any blanks with nulls. There is no equivalent for this in Oracle8i.

Most of these enhancements were introduced to allow non-Oracle applications to quickly port onto an Oracle database, and it's important to remember that these are just syntax differences. The ISO 99 standard doesn?t bring any new functionality to Oracle9i SQL.
 


The CROSS JOIN

In Oracle, the CROSS JOIN syntax produces a Cartesian product, very much the same as forgetting to add a WHERE clause when joining two tables:

select last_name, d.department_id
from employees e, departments d;


In Oracle9i, we can use the CROSS JOIN syntax to achieve the same result:

select last_name, d.department_id
from employees e
CROSS JOIN departments d;



 

The NATURAL JOIN

I like the NATURAL JOIN syntax because it automatically detects the join keys, based on the name of the matching column in both tables. This simplifies Oracle9i SQL because the WHERE clause will only contain filtering predicates. Of course, the use of NATURAL JOIN requires that both columns have identical names in each table. It?s interesting to note that this feature works even without primary or foreign key referential integrity.

Oracle8i

Select book_title, sum(quantity)

From book, sales
Where book.book_id = sales.book_id
group by book_title;


Oracle9i

Select book_title, sum(quantity)

from book
natural join sales
group by book_title;


The USING clause

The USING clause is used if several columns share the same name but you don?t want to join using all of these common columns. The columns listed in the USING clause can?t have any qualifiers in the statement, including the WHERE clause:

Oracle8i

select department_name, city
from departments, locations
where departments.location_id = locations.location_id
;

Oracle9i and beyond
 

select department_name, city

from departments
JOIN locations
USING (location_id);

 

The ON clause

The ON clause is used to join tables where the column names don?t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause:

Oracle8i

select department_name, city
from departments, locations
where department.location_id = locations.location_id;



 

Oracle9i

select department_name, city
from departments d
join locations l
on (d.location_id = l.location_id);


Mutable joins

Mutable joins are those where more than two tables are joined. The ISO SQL 1999 standard always 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:

Oracle8i

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;


Oracle9i

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);


New OUTER JOIN syntax

The ISO 99 standard removes the onerous plus sign (+) from Oracle outer joins and makes outer join SQL easier to understand.

LEFT OUTER JOIN

In a LEFT OUTER JOIN, all rows in the left-hand table are returned, even if there is no matching column in the joined tables. In this example, all employee last names are returned, even those employees who are not yet assigned to a department:

Oracle8i

select last_name, d.department_id
from employees e, departments d
where e.department_id = d.department_id(+);


Oracle9i

select last_name, d.department_id
from employees e
LEFT OUTER JOIN Departments d
ON e.department_id = d.department_id;


RIGHT OUTER JOIN

In a RIGHT OUTER JOIN, all rows in the right-hand table are returned, even if there is no matching column in the joined tables. In this example, all department IDs are returned, even for those departments without any employees:

Oracle8i

select last_name, d.department_id
from employees e, departments d
where e.department_id (+) = d.department_id;


Oracle9i

select last_name, d.department_id
from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);


Join the party

The ISO 99 standard is another example of Oracle?s commitment to enhancing its implementation of SQL. The most popular of these enhancements will be the NATURAL JOIN, which simplifies SQL syntax, and the LEFT OUTER JOIN and RIGHT OUTER JOIN, which eliminate the need for the clumsy (+) syntax.

SQL 99 References:

See my related  notes on the ISO SQL 99 Syntax:


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.