Search

Top 60 Oracle Blogs

Recent comments

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?