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

Monali Kamthe 1 Reputation point
2021-10-06T14:03:04.64+00:00

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

For Eg. -

Join Condition
on @VAR = ColumnName

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,758 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-10-06T21:43:37.253+00:00

    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.

    0 comments
  2. EchoLiu-MSFT 14,571 Reputation points
    2021-10-07T03:24:02.037+00:00

    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.

    0 comments