[thelist] basic SQL question...

Joshua Olson joshua at waetech.com
Fri Aug 22 23:11:46 CDT 2003

----- Original Message ----- 
From: "jsWalter" <jsWalter at torres.ws>
Sent: Friday, August 22, 2003 11:51 PM

> Can you tell me why 'A' is better or worse than 'B' ?
> Just trying to find out what is best to use and when.
> SQL sample 'A'
>    SELECT *
>     FROM img_items, img_cat
>     WHERE img_id = 20
>     AND img_items.cat_id = img_cat.cat_id;
> SQL sample 'B'
>    SELECT *
>     FROM img_items
>     NATURAL JOIN img_cat
>     WHERE img_items.img_id = 20;


Sample A is using the old-standard notation.  Sample B is using new-standard
notation.  You actually have more control of the join with B.  With B you
can technically tell it to filter each table before the join or after the
join depending on where you put the conditionals (though for INNER JOINS it
doesn't make a difference whereas in OUTER JOINS the order of the filtering
can make huge differences)

A big problem with sample A is that it makes a mess with OUTER JOINS.
Almost every database uses a different syntax for these with sample A.  Some
use *= or =* (left and right outer joins respectively) and some use (+) = or
= (+).  I'm sure there are a mess of other syntaxes.  This makes your sql
hard to port.

You should probably just stick with version B and completely discard version
A keeping in mind that there are other types of JOINS.

NATURAL JOIN is basically a shortcut to "figure out which keys the tables
are joined on based on which two have the same name and are the same type or
have an established relationship"  Other than the fact the database has got
to figure it out, there's really no difference between a NATURAL JOIN and an
INNER JOIN on the same keys.

You could rewrite the NATURAL JOIN as an INNER JOIN and explicitly tell the
db which keys to JOIN on and it would essentially be the same and may even
be a slight bit faster.

Keep in mind some databases don't support NATURAL JOIN whereas almost all
support INNER, OUTER, CROSS, and FULL (basically a CROSS join with no WHERE
clause)  joins.

Good luck and HTH,

Joshua Olson
Web Application Engineer
WAE Tech Inc.

More information about the thelist mailing list