Left semi Join operator in SQL Server execution plan

The Left Semi Join operator in an execution plan is used to return rows from the left table (or outer input) where there is a match in the right table (or inner input).  This type of join is typically employed when using EXISTS or IN conditions to filter data based on the presence of related records in another table.

See the example

CREATE TABLE customers   (      customerid   INT PRIMARY KEY,      customername VARCHAR(50)   ); CREATE TABLE orders   (      orderid     INT PRIMARY KEY,      customerid  INT,      orderamount DECIMAL(10, 2),      FOREIGN KEY (customerid) REFERENCES customers(customerid)   );   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);

Run the below query and see the execution plan

SELECT CustomerID, CustomerName FROM Customers c WHERE  EXISTS (     SELECT 1     FROM Orders o     WHERE o.CustomerID = c.CustomerID );

If we are using IN operator  then also we can see this operator.

SELECT CustomerID, CustomerName FROM Customers c WHERE  c.CustomerID Not in (     SELECT o.CustomerID     FROM Orders o  );

Run this script and see the execution plan