Physical Join Operator 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

Chosen in OLTP systems with small transactions.

When the outer input is small and the inner input has an index on the join key.

No restrictions on operators.

Input need not be sorted.

• Merge Join

Chosen when a medium to large transactions.

Equality operator should be specified in the query.

Input should be sorted.

• Hash Join

Chosen when large data set is required and many types of set operators are involved.

Equality operator should be specified in the query.

Input need not be sorted.

Demo Output

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

2. Forcing same where clause with different Join operators.