question

MonaliKamthe-9163 avatar image
0 Votes"
MonaliKamthe-9163 asked EchoLiu-msft commented

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

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

For Eg. -

Join Condition
on @Var = ColumnName

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What alternatives did you consider?


0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @MonaliKamthe-9163,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.