Alex Rivera | Logout

SQL INNER JOIN syntax

Asked 2008-12-27T10:14:14.943
29

the two bits of SQL below get the same result

SELECT c.name, o.product  
FROM customer c, order o  
WHERE c.id = o.cust_id  
AND o.value = 150  

SELECT c.name, o.product  
FROM customer c  
INNER JOIN order o on c.id = o.cust_id  
WHERE o.value = 150

I've seen both styles used as standard at different companies. From what I've seen, the 2nd one is what most people recommend online. Is there any real reason for this other than style? Does using an Inner Join sometimes have better performance?

I've noticed Ingres and Oracle developers tend to use the first style, whereas Microsoft SQL Server users have tended to use the second, but that might just be a coincidence.

Thanks for any insight, I've wondered about this for a while.

Edit: I've changed the title from 'SQL Inner Join versus Cartesian Product' as I was using the incorrect terminlogy. Thanks for all the responses so far.

Edit
Report

1 Answer

4

Oracle was late in supporting the JOIN ... ON (ANSI) syntax (not until Oracle 9), that's why Oracle developers often don't use it.

Personally, I prefer using ANSI syntax when it is logically clear that one table is driving the query and the others are lookup tables. When tables are "equal", I tend to use the cartesian syntax.

The performance should not differ at all.

answered 2008-12-27T10:24:30.523

Your Answer