What are the differences between these two SQL queries - SQL Server

Question

Possible Duplicate:
Is it better to do an equi join in the from clause or where clause

I have following two SQL queries. Can you please explain me the differences between these two ?

QUERY: 1

SELECT a.*, b.*
FROM Table1 a
INNER JOIN Table2 b
ON a.id = b.id
AND a.col = 'value'

QUERY: 2

SELECT a.*, b.*
FROM Table1 a
INNER JOIN Table2 b
ON a.id = b.id
WHERE a.col = 'value'

Thanks

Answer 1

Nothing unless case sensitivity is turned on on the database

Answer 2

For an INNER JOIN, filtering on the JOIN condition vs filtering in the WHERE clause should give identical results.

If this were an OUTER JOIN, the results would be different since the first one pre-filters the results in the source table before the JOIN condition.

Answer 3

in this particular case it's the same result, but it's not the same thing. the WHERE applies to the whole select, while the ON ... AND is only for the inner join (actually probably the same thing, but not for outer joins)

Answer 4

Yes, everything is pretty much the same. Case sensitive database will return error if your ID fields is not spelled correctly.

Answer 5

The difference is purely stylistic. Personally, I like to put the clauses that are key to performing the table joins up under the 'JOIN ... ON a=b' section, and put the clauses that have to do with my specific query filtering under the "WHERE" clause. There is no technical reason to do it one way or the other though, so long as you're talking about inner joins.