Hash match (Inner Join) operator in SQL Server execution plan

The Hash Join operator is a physical join operator that is commonly used to join large, unsorted tables. This operator works by building an in-memory hash table of one input and then using it to probe the second input for matching rows. The Hash Join is efficient for large tables where neither side has a sorted key nor index on the join column, making it a go-to for operations where sorting or indexed joins (like Merge Joins) aren’t feasible.

Icon of Hash Join Operator

See the example

Creating table for the demo

CREATE TABLE customers   (      customerid   INT,      customername VARCHAR(50)   ); CREATE TABLE orders   (      orderid     INT,      customerid  INT,      orderamount DECIMAL(10, 2)   );

Inserting some records in both table

INSERT INTO Customers (CustomerID, CustomerName) VALUES     (1, 'Bagesh'),     (2, 'Rajesh'),     (3, 'Ganesh'),     (4, 'Mahesh');  INSERT INTO Orders (OrderID, CustomerID, OrderAmount) VALUES     (101, 1, 250.00),     (102, 1, 300.00),     (103, 3, 700.00),     (104, 5, 150.00);

Run the below query

SELECT c.customerid,        c.customername,        o.orderid,        o.orderamount FROM   customers c        JOIN orders o          ON c.customerid = o.customerid;

See the execution plan