Re: Performance Optimization for Dummies 2 - the SQL

On 10/8/06, Jim C. Nasby <jim@nasby.net> wrote:
> On Thu, Oct 05, 2006 at 09:30:45AM -0400, Merlin Moncure wrote:
> > I personally only use explicit joins when doing outer joins and even
> > them push them out as far as possible.
>
> I used to be like that too, until I actually started using join syntax.
> I now find it's *way* easier to identify what the join conditions are,
> and to seperate them from the rest of the where clause. It also makes it
> pretty much impossible to mess up a join clause and get a cartesian
> product.
>
> If you are going to put the join clauses in the WHERE clause, at least
> put a space between the join stuff and the rest of the WHERE clause.

I use the row constructor to define key relationships for non trivial
queries i.e.
select foo.*, bar.* from foo f, bar b
  where (f.a, f.b, f.c) = (b.a, b.b, b.c) -- etc

I am a really big fan of the row constructor, especially since we can
do proper key ordering in 8.2.

by convention I do relating first, filtering second.  for really
complex queries I will inline comment each line of the where clause:

where
  (p.a) = (pd.b) and -- match part to part description
  pd.type != 'A' -- not using archived parts

as to unwanted cartesian products, I test all prodution queries in the
shell first.  The really complex ones are somewhat trial and error
process after all these years :)

being something of a mathematical guy, I love sql for its (mostly)
functional nature but hate the grammar.  reminds me a little bit too
much of cobol.  the join syntax is just too much for me, although with
left/right/natural joins there is no other way, and I very much agree
with Carlo wrt oracle's nonstandard join syntax being more elegant.

merlin