Condition on main table in inner join vs in where clause

Question

Is there any reason on an INNER JOIN to have a condition on the main table vs in the WHERE clause?

Example in INNER JOIN:

SELECT 
   (various columns here from each table)
FROM dbo.MainTable AS m
INNER JOIN dbo.JohnDataRecord AS jdr
    ON m.ibID = jdr.ibID
    AND m.MainID = @MainId -- question here
    AND jdr.SentDate IS NULL
LEFT JOIN dbo.PTable AS p1
    ON jdr.RecordID = p1.RecordID
LEFT JOIN dbo.DataRecipient AS dr
    ON jdr.RecipientID = dr.RecipientID
(more left joins here)
WHERE
    dr.lastRecordID IS NOT NULL;

Query with condition in WHERE clause:

SELECT 
   (various columns here from each table)
FROM dbo.MainTable AS m
INNER JOIN dbo.JohnDataRecord AS jdr
    ON m.ibID = jdr.ibID
    AND jdr.SentDate IS NULL
LEFT JOIN dbo.PTable AS p1
    ON jdr.RecordID = p1.RecordID
LEFT JOIN dbo.DataRecipient AS dr
    ON jdr.RecipientID = dr.RecipientID
(more left joins here)
WHERE
    m.MainID = @MainId -- question here
    AND dr.lastRecordID IS NOT NULL;

Difference in other similar questions that are more general whereas this is specific to SQL Server.

@GordonLinoff what I thought but sometimes I don't know everything hence why I asked. I find the first "style" to be less clear to me. — Mark Schultheiss, May 07 '18 at 15:37
@Mark You and me both. It's an odd place to stick the condition. Totally valid, but totally odd. — JNevill, May 07 '18 at 15:40
https://stackoverflow.com/a/25957600/3404097 Anyway this is a duplicate/faq, google your title, see [ask] & read the downvote arrow mouseover text. — philipxy, May 10 '18 at 03:32
Possible duplicate of [SQL join: where clause vs. on clause](https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) — philipxy, May 10 '18 at 03:45
@philipxy - agree it is generally the same subject however that other question is just that (general) and not specific to SQL SERVER and version. — Mark Schultheiss, May 10 '18 at 12:30
Do you think you are the first person to ask this? (Rhetorical.) Beware of a lot of highly-upvoted nonsense posts on SQL semantics https://stackoverflow.com/a/25957600/3404097 https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join?rq=1#comment41531068_38549 https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join?noredirect=1&lq=1#comment85420636_6188334 https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram#comment51529331_13997365 — philipxy, May 10 '18 at 18:49

score 0 · Accepted Answer · answered May 10 '18 at 12:17

In the scope of the question,

Is there any reason on an INNER JOIN to have a condition on the main table vs in the WHERE clause?

This is a STYLE choice for the INNER JOIN.

From a pure style reflection point of view:

While there is no hard and fast rule for STYLE, it is generally observed that this is a less often used style choice. For example that might generally lead to more challenging maintenance such as if someone where to remove the INNER JOIN and all the subsequent ON clause conditions, it would effect the primary table result set, OR make the query more difficult to debug/understand when it is a very complex set of joins.

It might also be noted that this line might be placed on many INNER JOINS further adding to the confusion.

Condition on main table in inner join vs in where clause

1 Answers1

From a pure style reflection point of view: