'Where' clause at the end of the whole connection
Assuming I have 2 sql query like below, will the query result matter?
SELECT * FROM A
INNER JOIN B ON A.ID = B.ID
WHERE B.IsActive = 1
INNER JOIN C ON B.ID = C.ID
WHERE C.IsActive = 1
SELECT * FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON B.ID = C.ID
WHERE B.IsActive = 1 AND C.IsActive = 1
+3
source to share
1 answer
Your first request is syntactically incorrect. It needs to be rewritten:
SELECT * FROM A
INNER JOIN B ON A.ID = B.ID
WHERE B.IsActive = 1
INNER JOIN C ON B.ID = C.ID
WHERE C.IsActive = 1
Now after correction if I compare below two queries:
SELECT * FROM A
INNER JOIN B ON A.ID = B.ID AND B.IsActive = 1
INNER JOIN C ON B.ID = C.ID AND C.IsActive = 1
Estimated execution plan:
Vs
SELECT * FROM A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON B.ID = C.ID
WHERE B.IsActive = 1 AND C.IsActive = 1
Estimated execution plan:
Then they are exactly the same:
- In terms of end result / outcome
- In terms of performance and query execution plan
Note : There is no advantage to using one syntax over another. It's all about personal preference that suits you and your team. The SQL Server execution engine does the job for you to generate the same execution plan regardless of how you write your query.
+4
source to share