5.1. Join Syntax: Out with the Old and In with the New (SQL:1999)

Not only can you join two or more tables in a number of different ways, but you can also use two different syntax forms to perform these joins. As of Oracle9i, the full ANSI SQL:1999 standard for join syntax is supported. Prior to Oracle9i, Oracle used a proprietary syntax that wasn't always compatible with the ANSI standard.

join

To combine two or more tables in a query to produce rows as a result of a comparison between columns in the tables.

Oracle's proprietary syntax, which is still supported in Oracle9i and Oracle 10g for backward compatibility with existing code, put all of the join conditions in the SELECT statement's WHERE clause. It also relied on relatively obscure methods to indicate certain types of join operations. The newer syntax relies more heavily on concise yet descriptive keywords to clearly indicate what operation is being performed. We'll cover both the old and new syntax in this chapter; as a DBA or developer, you'll most likely see new applications using the new syntax and plenty of existing applications that use the old syntax.

There is no performance benefit to using one syntax over the other; the same kind of join using either syntax will translate into the same internal SQL engine operation. One of the biggest benefits is the ease with which the new syntax can be written and understood. The join conditions are now separated from the WHERE clause and placed in the FROM clause. The WHERE clause, if one even exists, ends up being much cleaner because it's used only for filtering the rows being returned from the query, instead of being intertwined with table join conditions.

In each section of this chapter, you'll see how the database analyst, Janice, uses both formats for each new query she develops for the boss.