Starting with Oracle 9i, Oracle recommends that SQL developers use the ANSI join syntax instead of the Oracle proprietary (+) syntax. There are several reasons for this recommendation, including:
- Easier to segregate and read (without mixing up join versus restriction code)
- Easier to construct join code correctly (especially in the case of “outer” joins)
- Portable syntax will work on all other ANSI compliant databases, such as MS SQL Server, DB2, MySQL, PostgreSQL, et al
- Since it’s the universally accepted standard, it’s the general target for all future database and third party vendors’ tools
- The proprietary Oracle outer-join (+) syntax can only be used in one direction at a time, it cannot perform a full outer join
- Plus these additional limitations from the Oracle documentation:
- The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.
- A condition containing the (+) operator cannot be combined with another condition using the OR logical operator.
- A condition cannot use the IN comparison condition to compare a column marked with the (+) operator with an expression.
- A condition cannot compare any column marked with the (+) operator with a sub-query.
Thus it’s time to embrace the ANSI join syntax – and move into the 21st century J
So let’s build a relatively simple business oriented query using the “MOVIES” demo database shown in Toad® Data Modeler below.
Suppose the video store business wants to find out which movie category, movie year, movie title, movie copy format, inventory of copies and rental count for just the movies from the 70’s, 80’s and 90’s are in “OLD” shape and have been rented very few times. Below is the Oracle proprietary outer join syntax for such a SELECT statement.
OK – so this probably looks quite a lot like code you write in your shop, especially if you’ve been doing Oracle coding for a long time.
But there are several places where coders could very easily make a mistake:
-
Did they remember to do an outer join to get the rental count?
-
Did they remember to do an outer join with categories for those without titles?
-
Did they remember to do an outer join with movie copies for those without tapes?
-
Did they remember to include (+) in non-outer-join WHERE clause conditions?
The point is that our old “tried and true” Oracle proprietary syntax is just too much of a headache to risk all the coding problems that can so easily occur. Plus, we have Oracle’s recommendation to switch to the ANSI syntax.
So how can we leverage Toad to make this transition (i.e. learning) process easier?
The answer is simple: Toad’s Query Builder (formerly called the SQL Modeler) can import our SQL text file and parse it into a diagram as shown:
Then we simply press the Query Builders “A” toolbar icon to ask it to be converted to ANSI join syntax. And Voilà – we’re done.
OK – looks like there are still a few bugs with Toad’s reverse engineering parser and its “join code” translation. But Toad still did 80+% of the SQL rewrite for us automatically. We just need to send it back over to the editor and spruce it up as shown:
Wow – even though I tried very hard to carefully construct an originally correct example using Oracle’s syntax, I nonetheless missed a case (see, final SELECT returns seven rows instead of only six). Just goes to show you that it’s true – ANSI join syntax makes coding mistakes less likely to occur J
The final proof is shown below, as we can now run the exact same query in Toad for SQL Server and get the very same results!!!
