> For those working on INNER/OUTER Joins...any comments? :)
> > JOIN statement? I take it that this is different then:
> > SELECT a.field1, b.field2 from table1 a, table2 b where a.key = b.key
> ANSI92 supports the far better readable JOIN statement:
> select a.field1, b.field2
> from table1 a
> join table2 b on
> a.key = b.key
Don't know why one would consider this better or more readable;
depends on your past lives I guess...
SQL92 outer joins use this syntax, but other DBs (claiming SQL92
compliance, btw; they usually only meet the lowest defined level of
compliance) use a different syntax with no ill effects. We are
implementing the SQL92 syntax.
> It generally parses and optimizes faster too. For MS SQL Server I've seen
> improvements of up to 75% percent: execution time was the same, but the plan
> was calculated much faster.
I would guess that any speedup would be an indication of a bad
optimizer, which apparently skips work when given the "join syntax".
If the statements are equivalent, then one would hope that the
parser/optimizer would consider the same set of plans to satisfy it.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California