Is it a good practice to use variable in join condition.?

Question

Is it a good practice to use variable in join condition.?

For Eg. -

Join Condition
on @VAR = ColumnName

Answer 1

That particular example looks funny. But say that I only want to see order rows for a certain product. I could certainly write:

SELECT ...
FROM  Orders O
JOIN   OrderDetails OD ON OD.OrderID = O.OrderID
                  AND OD.ProductID = @ProductID

I might prefer to have the condition in the WHERE clause, but if you have lots of joins coming later, you may find it easier to read by having the condition in the join.

And if it is a outer join, this is where you need to have condition on the variable.

Answer 2

Hi @Monali Kamthe ,

Welcome to the microsoft TSQL Q&A forum!

For the writing habits of sql code, there are three main considerations:

1. Correct sql syntax.
2. Easy to read.
3. Have high execution efficiency.

If the connection condition is a variable, you can only put it after the JOIN. Regarding the filter condition, whether it is a variable or not, I usually put it in the WHERE clause. Of course, the filter condition is easier to read after the JOIN.
Finally, putting variables after JOIN or in the WHERE clause does not seem to have a big impact on the efficiency of code execution.

In short, putting variables in JOIN or WHERE is actually only related to personal habits, and there is no obvious difference.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.