Newbie question on sql clause

[Lurc]

Dear all,

1.
SELECT list.ID, list.NAME, grade.grade
FROM list,grade
WHERE (list.id=grade.id);

2.
SELECT list.ID, list.NAME, grade.grade
FROM list INNER JOIN grade ON list.ID = grade.id


A newbie question on above sql statements, is there any difference between
them? and which one has a better efficiency?

Thanks.

 

[A.J]

Wrong group to ask such questions.

 

[Lurc]

Thanks, so where should i go?

 

[A.J]

Try this newsgroup "comp.databases.oracle.server".

 

[Adrian Moore]

RL,

2 is more efficient as the source tables can be sorted and a merge join can
be used.

1.is really inefficient. It's a cross join, list rows * grade rows, which
could be in the millions of rows only to have the where list.id=grade.id
filter 99% of them out.

Having said this; most modern databases will recognize that 1 is actually
equivalent to 2. The optimizer will convert it to give you the same
performance anyway.

Hope this helps
Ad.

 

[Bineesh AV]

there is no difference between two

but its recommented approch is the latter one
because it is asi standard


bineesh

 

[Guest]

1. does not produce a cross join - that's non-ansi join syntax - the 2
statements are functionally equivelant, however syntax 2 (INNER JOIN) is
generally recommended over non-ansi syntax

 

[Lurc]

Thanks for all your gurus.

--
Best regards,
RL

there is no difference between two

but its recommented approch is the latter one
because it is asi standard


bineesh