Newbie question on sql clause

  • Thread starter Thread starter Lurc
  • Start date Start date
L

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.
 
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.
 
there is no difference between two

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


bineesh
 
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
 
Thanks for all your gurus.

--
Best regards,
RL
Bineesh AV said:
there is no difference between two

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


bineesh
Click to expand...
 
Back
Top