Physical Join Operators in SQL Server

                   Physical Joins are chosen by the Optimizer. SQL Server is designed well enough to identify the optimal plan for any query executed.

Physical Joins Operators

• Nested Loop Join: 

1. Chosen in OLTP systems with small transactions. 
2. When the outer input is small and the inner input has an index on the join key. No restrictions on operators. 
3. Input need not be sorted.

• Merge Join: 

1. Chosen when a medium to large transactions.
2. Equality operators should be specified in the query.
3. Input should be sorted.

• Hash Join: 

1. Chosen, when large data set, is required and many types of set operators are involved.
2. Equality operators should be specified in the query.
3. Input need not be sorted.

Demo Output:

1. Optimizer picking the plan depending on the where clauses :

2. Forcing the same where clause with different Join operators.

Mohammed Adil