what is nested and has join in SQL query execution

 

A join operation is used to combine two or more tables based on a related column between them. There are different types of join operations such as nested loop join, hash join, and merge join, and the optimizer will choose the best join type based on the size of the tables, the available indexes, and other factors.

Nested Loop Join:

A nested loop join is a join operation where the optimizer loops through each row in one table and matches it with each row in another table. This type of join is typically used when one of the tables is small or has an appropriate index.

For example, let's say we have two tables, "Sales" and "Customers", with the following columns and data:

Sales Table:

SaleID CustomerID Product Quantity

1 100 Widget 5

2 101 Gadget 3

3 102 Widget 2

4 103 Gadget 4

Customers Table:

CustomerID Name City

100 Alice New York

101 Bob Chicago

102 Charlie Boston

103 Dave Seattle

If we want to join these two tables based on the "CustomerID" column, the optimizer may choose to use a nested loop join if the "Customers" table is small or has an appropriate index. The execution plan for this join operation would look something like this:

  |--Nested Loops(Inner Join, WHERE:([Sales].[CustomerID]=[Customers].[CustomerID]))

       |--Clustered Index Scan(OBJECT:([Sales].[PK_Sales] AS [Sales]))

       |--Clustered Index Seek(OBJECT:([Customers].[PK_Customers] AS [Customers]), SEEK:([Customers].[CustomerID]=[Sales].[CustomerID]))

In this execution plan, the optimizer is using a nested loop join where it loops through each row in the Sales table and matches it with each row in the Customers table based on the CustomerID column. The clustered index scan and seek operations are used to retrieve the data from the tables.

Hash Join:

A hash join is a join operation where the optimizer builds a hash table from one of the tables and then loops through the other table to find matches. This type of join is typically used when both tables are large or the join condition involves non-indexed columns.

For example, let's say we have two tables, "Orders" and "Products", with the following columns and data:

Orders Table:

OrderID ProductID Quantity

1 100 5

2 101 3

3 102 2

4 103 4

Products Table:

ProductID ProductName Price

100 Widget 10.00

101 Gadget 15.00

102 Gizmo 20.00

103 Thingamajig 25.00

If we want to join these two tables based on the "ProductID" column, the optimizer may choose to use a hash join