Cross Join difference question [duplicate]

This question already has answers here: Closed 11 years ago.

P开发者_JAVA百科ossible Duplicates:

WHERE clause better execute before IN and JOIN or after

INNER JOIN versus WHERE clause — any difference?

What, if any, are the differences between the following?

Select 
  col1, 
  col2 
from TableA A, TableB B
where A.ID = B.ID

and

Select 
  col1, 
  col2
From TableA A
Inner Join TableB B on A.ID = B.ID

They seem to have the same behaviors in SQL,

---

They will likely be optimized to the same thing by the RDBMS. They both JOIN the tables on the A.ID = B.ID criteria.

However, the JOIN syntax is explicit and considered correct.

---

The former is ANSI-89 syntax, and the latter is ANSI-92 syntax. The latter should almost always be used due to the fact that it's much clearer when you start to use outer joins when expressed in ANSI-92 syntax.

---

The first syntax is (as you pointed out) a cross join or Cartesian product of the two tables. In a system with no optimizer (or a poor optimizer) this will produce a combination of every record in the first table combined with every record in the second table, then filter them down to just those matching the WHERE clause.

The output from both statements will be the same, and if the system you are using has a good optimizer than the performance will be the same as well.

Two comments I would offer:

1) I find it better to be explicit about your intent when writing statements. If you intended to perform an INNER JOIN then use the INNER JOIN syntax. Future you 6 months form now will be thankful.

2) The optimizer in SQL Server will perform an INNER JOIN in this situation (at least recent versions, can't guarantee all versions), but how well it guesses that path is going to depend on the version of the SQL Server engine and is not guaranteed to remain the same in the future (I doubt it will change in this situation, but is the cost of a few more characters of typing really that high?)

---

@ypercube correctly pointed out your question is about two different INNER JOIN syntaxes. You don't have any outer join syntax. As @Matt Whitfield pointed out, the first syntax is ANSI-92 and the second one is ANSI-89 style. I agree with matt entirely that in more complicated queries the ANSI-92 syntax is way way more readable.

Furthermore, depending on your version of SQL Server THE ANSI-89 syntax is DEPRECATED and can give you problems. See SR0010: Avoid using deprecated syntax when you join tables or views In fact, in the next version SQL 2011, or Denali, or whatever we're calling it, the ANSI-89 syntax will not be supported. See: Features Not Supported in the Next Version of SQL Server (search for the word "join").