Left Anti semi Join operator in SQL Server execution plan

The Left Anti Semi Join operator is used in an execution plan to return rows from the left table (or outer input) that do not have matching rows in the right table (or inner input).When we are using the Not In or NOT EXISTS in our query then Left Anti Semi Join seen in the execution plan.

See the example

For the demo we are creating tables and inserted some records.

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);

Let’s run the below query and see the execution plan

SELECT customerid,        customername FROM   customers c WHERE  NOT EXISTS (SELECT 1                    FROM   orders o                    WHERE  o.customerid = c.customerid);

See the execution plan

If we are using NOT IN 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 and see the execution plan