Link to home
Start Free TrialLog in
Avatar of Ann K
Ann K

asked on

Join vs where

Is JOIN is better than WHERE efficiency wise and low memory consumption.
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image
There is no impact to the generated execution plan for an inner join.  The query optimizer is smart enough to recognize that they are effectively the same.

However, here is a logical difference if you are performing an outer join.

SELECT *
FROM Table1
LEFT OUTER JOIN Table2
   ON Table1.ID = Table2.ID
   AND Table2.ColumnA = @ColumnA

Select *
FROM Table1
LEFT OUTER JOIN Table2
   ON Table1.ID = Table2.ID
WHERE Table2.ColumnA = @ColumnA