Issue with SQL join that resolves when the order of clauses is altered

Data and Databases
1

I encountered an error message that says:

Multi-part identifier cannot be bound
during the initial join, specifically relating to PatientSurvey.PatientSurveyId. I’m working with SQL Server. Here’s the query I ran:
SELECT PatientSurvey., CompletedItem.

FROM PatientSurvey, CompletedItem

JOIN SurveyResponseIteration ON SurveyResponseIteration.PatientSurveyId = PatientSurvey.PatientSurveyId

AND SurveyResponseIteration.IterationId = 4

WHERE PatientSurvey.CompletedItemId = CompletedItem.CompletedItemId
Interestingly, when I change up the order of the clauses, the error disappears. For instance, this query works fine:
SELECT PatientSurvey., CompletedItem.

FROM PatientSurvey, CompletedItem

JOIN SurveyResponseIteration ON SurveyResponseIteration.IterationId = 4

WHERE PatientSurvey.CompletedItemId = CompletedItem.CompletedItemId

AND PatientSurvey.PatientSurveyId = SurveyResponseIteration.PatientSurveyId
I’m puzzled as to why the identifier PatientSurvey.PatientSurveyId is not recognized in the first query, but it is in the second. Can anyone explain this?

2

The issue stems from the syntax used in the SQL join. When mixing implicit and explicit joins, SQL Server can interpret the order of operations differently, causing some identifiers to not be bound correctly. In your first query, the comma-separated lists rely on older implicit join syntax, which can complicate things when combined with explicit JOIN. Specifically, the join condition places restrictions that affect the sequence in which the database engine processes the tables. By structuring it as explicit JOIN and aligning conditions in a logical order, the query optimizer can efficiently resolve and bind all identifiers properly. It’s often recommended to use a consistent join style, typically using explicit JOINs to avoid such confusion.