Predicate relations or join on clause

Some of my friends vehemently prefer the SQL-89 predicate relations definition of joins.

Some of my friend vehemently perfer the SQL-92 “join on” clause, aka ANSI joins (which always puzzles me since I think both are defined in ANSI standards.)


Some folks (and this probably doesn’t include my friends) have called the Oracle join specification (+) “an abomination.”

I think each has strong points and drawbacks. In Oracle both are still legal.

In which syntax is it quicker to discover all the tables involved in a particular query? (hint: the one where you list all the tables immediately after the from clause.) Of course inline views and “with” common table expressions have diluted that old advantage of ’89.

In re: (+) being “an abomination” that holds only if you were never taught to read it correctly. It distinctively and succinctly documents the table having null tuples “added” to it to equalize the join.

Neither syntax is inherently “better” and just as perfectly structured programs can always be written in BASIC6 and spaghetti can be written in Pascal, perfectly clear syntax can be had in either 89 or 92. (notice I didn’t say Wirth was stupid. When he wrote his article there were MANY amateur programmers creating spaghetti in bad ports of Basic who didn’t get the proper schooling of Kemeny and Kurtz to write structured code. Wirth noticed that it was extremely easy to use goto poorly and harmfully.)


If you want an easy to read query, you can have an easy to read query!

In 89, put the tables in an order. Add the join clauses in that same order and always put the (+) denoting sets to be expanded on the right.

Then put all the filters in, in the table order of the from clause and the column order of the columns of each table.

Do that and you’ve got a complete, well-formed, repeatable and easy to digest query.

There are a few equally clear variants, and you can also write spaghetti. If you start like this (and especially if you have a defined order for your tables through-out your application suite) your code will be probably be extremely easy to read.

Likewise 92. In 92 you might have to look through more code than you’d like to find all the tables involved in the query, but it is difficult for even the creatively obtuse to disguise the join criteria.

I count myself amongst those who rue that they stuck in “LEFT” and “RIGHT” and didn’t just make it all earlier to later implicitly. And others have already pointed out that using the shorthand NATURAL means you have to look in the dictionary to see which columns participate in the join.

This shouldn’t be a religious war. Bad code should be re-written to be readable because sooner or later some human will have to support it. But that is a distinct issue from re-writing clear 89 syntax just because you don’t like 89 syntax.

Now one more question: Why haven’t we re-written the SQL standard to include the few little bits that are missing to make it an orthogonal representation of relations?


About rsiz

Father and Husband, Oracle Technology Scientist and Consultant, planning to end poverty for citizens and legal US residents Lebanon, NH · See my wife's puzzles at
This entry was posted in Oracle, Thinking Clearly and tagged . Bookmark the permalink.

2 Responses to Predicate relations or join on clause

  1. Mark Zellers says:

    There are a few cases that the Oracle legacy (+) syntax does not cover: notably non equal-joins or outer joins with conditions that involve columns from more than one table.

    That leads me to strongly prefer the ANSI join, although I don’t have much use for the RiGHT OUTER JOIN. I prefer my queries to express the order I want Oracle to use them. I love the /*+ ORDERED */ hint.

    • rsiz says:

      That is an excellent point that there are joins that can be done with the ’92 specification that cannot be done (at least not simply in relation predicate) via the ’89 specification. So that would be new code (compared to re-writing ’89 code just because it is ’89 code.)

      As regards join hints, if you know your data set and you know the context of your application, I don’t have a problem with trying to get Oracle to solve queries according to your preferred plan. Even if it is not the 100% cheapest actual solution, you might inject some stability that way. On the other hand, applications and utility packages that often are employed against wildly varying data sets with different textures are unlikely to be solved well for all with specific hints. And of course that probably prevents age old options such as bushy joins we used to have to do “by hand” that folks like Oracle have started to implement and cost out in the optimizers. Mere binary join patterns are so 1977. But if you do implement code for a known data set in a known context, I’m completely sympathetic to ordered hints. Stability has a high value. Which gets back to why I think it is a mistake to change clearly written existing code that functions well. If it is not clear or it does not function well, by all means re-write it your favorite way.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s