Re: to optimize a select join

Mladen Gogala wrote:
On Sat, 29 Apr 2006 17:55:36 +0200, Jean-Claude wrote:


1/
select *
from file1 a join file2 b on b.key=a.key
where b.data=123
and b.name='TEST'


I cannot understand why do people use that idiotic ANSI join syntax.
Relational databases model naive set theory. That means defining subsets
by setting rules on elements. Your query should be best written like
this:

select a.*,b.*
from file1 a,file2 b
where a.key=b.key and
b.data=123 and
b.name='TEST'

That way, the database optimizer doesn't have to contend with
idiotic things like "join". You should define your data and your
selection properly, index the proper columns and enjoy.


Maybe because the "rules of elements" don't allow all possibilities?

For instance - try this. List all records in table A, along with their matching records in table B if those records exist. Impossible without a Union, but very easy with a LEFT OUTER JOIN.

There is minimal additional overhead to the JOIN syntax method. And much more flexibility. Additionally, you only need to learn one basic syntax for all JOIN cases.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================
.