Chapter 5: Using Multiple Tables


So far, we have been dealing with only one table at a time in our SQL query examples. But typically the information needed to satisfy a user query requires more than one table. For example, the EMPLOYEES table has a column with a department number, but not a department name; the department name must be retrieved from the DEPARTMENTS table. You can get this information by joining the two tables together on a common column, in this case, the DEPARTMENT_ID column. Two or more tables can also be joined in situations where the columns may not be equal.

The boss at Scott’s widget company has realized that data can be pulled from more than one table at a time. Now the application developer and DBA, Janice, has been busy trying to keep up with his requests for reports. Each of the join types will be discussed in this chapter, as we follow Janice’s work.

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 the current release 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.

Tip

All new SQL code should use the SQL:1999 standard syntax for readability and cross-platform compatibility.

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




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net