Difference between Join in FROM clause and WHERE clause

Question

What will be the exact difference between following two statements?

select t1.RecId,t2.City from MyTable t1
join JoinTable t2
on t1.RecId=t2.RecId

select t1.RecId,City
from MyTable t1,JoinTable t2
where t1.RecId=t2.RecId

Could you also provide some practical situations for their usage. 

Answer 1

These 2 queries are semantically identical.  With an inner join, predicates can be specified in either the JOIN or WHERE clauses.  Personally, I prefer the ANSI/ISO syntax (explict JOIN clause).  See Join Fundamentals in the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms191517.aspx) for details.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

As Dan mentions his response with an inner join they are virtually the same,. The difference however is when using outer joins, as you can specify join criteria without filtering the rows returned as would happen if teh criteria were in a where clause.

e.g.

select t1.RecId,t2.City 
from Table1 t1 LEFT JOIN  Table2 t2
on t1.RecId=t2.RecId
and t2.typeId = 2
WHERE RecordDate > GETDATE()

In the above example we only get back City values with a typeid of 2 from the table2 however all records satisfying the where clause are returned from Table1. (The City values would just be Null in the cases where statusid <> 2).

This can be a useful approach in some situations.

Answer 3

If we have two ways to use JOINs, then the most efficient way will be to write condition in FROM clause. And as J M has explained, for above situation WHERE clause will be used.
Am I right ? Please suggest.

Answer 4

Anyways SQL server engine will flatten the query into a join condition even it is mentioned as a WHERE CLAUSE. This time for flatening the query can be avoided by using joins

Answer 5

Thanx Sorna for the reply. Now it is more clear.

Answer 6

If J M's example were an INNER JOIN, then I would expect identical execution plans (and performance) between the JOIN and WHERE clause queries.  Keep in mind that SQL is a declaritive language rather than a procedural one.  There are some aspects of query formulation that can affect the execution plan but it's the job of the SQL Server optimizer to generate the best plan regardless of how the desired results are expressed.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/