SQL Server查询引擎问询：两种内连接写法是否始终等效？

Great question—this is a common point of confusion, and your observation is totally correct: for INNER JOIN operations, these two query styles will always return identical results. Let’s break down why, and cover any edge cases you might be wondering about:

Why They’re 100% Equivalent #

• INNER JOIN Logic Doesn’t Care About Condition Placement: Unlike outer joins (LEFT/RIGHT/FULL), INNER JOIN only keeps rows where all join/filter conditions are satisfied. Whether you write those conditions in a WHERE clause (with the old comma-separated table syntax) or directly in the ON clause of an explicit INNER JOIN, the end result is the same—only rows that match all your criteria will be included.
• Query Optimizers Treat Them the Same: Modern database engines (like SQL Server, which your syntax hints at with dbo. and temporary tables #) parse both queries into identical execution plans. The optimizer sees the core logic (match sites to new info, filter mismatched dates, exclude null open dates) regardless of how you structure the syntax.

When Would They Not Match? #

The only time ON vs. WHERE makes a difference is with outer joins. For example, if you swapped INNER JOIN for LEFT JOIN:

• Putting new.OpenDate IS NOT NULL in the ON clause would still return every row from dbo.site (even if there’s no matching #newSiteInfo row, those would show null values for the new info columns).
• Moving that condition to the WHERE clause would filter out those null rows, effectively turning the LEFT JOIN into an INNER JOIN.

But since you’re using INNER JOIN here, this edge case doesn’t apply to your queries.

Quick Style Notes #

While the results are identical, there are tradeoffs to each syntax:

• The explicit INNER JOIN style is generally preferred for complex queries because it separates join logic (how tables relate) from filter logic (which rows to keep), making the code easier to read and debug. It also follows ANSI SQL standards, which helps with cross-database compatibility.
• That said, for simple queries like yours, the WHERE-clause style can feel more concise and readable—there’s no hard rule here, just preference and team conventions.

Your Queries for Reference #

First query (comma-separated with WHERE):

SELECT s.Name, new.OpenDate, s.StoreOpenDate 
FROM dbo.site s, #newSiteInfo new 
WHERE s.Id = new.SiteId 
  AND s.StoreOpenDate <> new.OpenDate 
  AND new.OpenDate IS NOT NULL;

Second query (explicit INNER JOIN):

SELECT s.Name, new.OpenDate, s.StoreOpenDate 
FROM dbo.site s 
INNER JOIN #newSiteInfo new 
  ON s.Id = new.SiteId 
  AND s.StoreOpenDate <> new.OpenDate 
  AND new.OpenDate IS NOT NULL;

内容的提问来源于stack exchange，提问作者nosnevel