SQL Inner Join syntax

Question

Hi, 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.

Answer 1

Both queries are performing an inner join, just different syntax.

Answer 2

Actually these examples are equivalent and neither is a cartesian product. A cartesian product is returned when you join two tables without specifying a join condition, such as in

select *
from t1,t2

There is a good discussion of this on Wikipedia.

Answer 3

To answer part of your question, I think early bugs in the JOIN ... ON syntax in Oracle discouraged Oracle users away from that syntax. I don't think there are any particular problems now.

They are equivalent and should be parsed into the same internal representation for optimization.

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.

Answer 5

Both queries are an inner joins and equivalent. The first is the older method of doing things, whereas the use of the JOIN syntax only became common after the introduction of the SQL-92 standard (I believe it's in the older definitions, just wasn't particularly widely used before then).

The use of the JOIN syntax is strongly preferred as it separates the join logic from the filtering logic in the WHERE clause. Whilst the JOIN syntax is really syntactic sugar for inner joins it's strength lies with outer joins where the old * syntax can produce situations where it is impossible to unambiguously describe the join and the interpretation is implementation-dependent. The [LEFT | RIGHT] JOIN syntax avoids these pitfalls, and hence for consistency the use of the JOIN clause is preferable in all circumstances.

Note that neither of these two examples are Cartesian products. For that you'd use either

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

or

SELECT c.name, o.product
FROM customer c CROSS JOIN order o WHERE o.value = 150

Answer 6

The JOIN... ON... syntax is a more recent addition to ANSI and ISO specs for SQL. The JOIN... ON... syntax is generally preferred because it 1) moves the join criteria out of the WHERE clause making the WHERE clause just for filtering and 2) makes it more obvious if you are creating a dreaded Cartesian product since each JOIN must be accompanied by at least one ON clause. If all the join criteria are just ANDed in the WHERE clause, it's not as obvious when one or more is missing.