Interview Question of the Week #038 – What is Left Semi Join Showplan Operator?

Interviewers sometimes ask about operators that we don’t name in everyday SQL. Left Semi Join is one of those questions. It’s easier to explain by the result it produces than by memorizing a plan icon.

Question: What does a Left Semi Join showplan operation do?

Answer: It returns each row from the first input when at least one matching row exists in the second input. It doesn’t return columns from the second input or repeat the first row for every inner match. A Left Anti Semi Join keeps first-input rows with no match. Right Semi and Right Anti Semi describe the corresponding retained rows from the second input.

In SQL, EXISTS expresses the existence test, and NOT EXISTS expresses its absence. This small example makes the duplicate-match point visible:

CREATE TABLE #SemiOuterDemo (OuterId int PRIMARY KEY, ProductId int NULL);
CREATE TABLE #SemiInnerDemo (WorkId int PRIMARY KEY, ProductId int NULL);
INSERT #SemiOuterDemo VALUES (1, 10), (2, 20), (3, 10), (4, NULL);
INSERT #SemiInnerDemo VALUES (1, 10), (2, 10), (3, NULL);

SELECT o.OuterId, o.ProductId
FROM #SemiOuterDemo AS o
WHERE EXISTS
    (SELECT 1 FROM #SemiInnerDemo AS i WHERE i.ProductId = o.ProductId)
ORDER BY o.OuterId;

Both matching outer rows survive, each once. An ordinary inner join would return each of those outer rows twice, because there are two matching inner rows. In an actual execution plan, inspect the join operator’s Logical Operation property. The physical operator may be Nested Loops, Hash Match or Merge Join. Here it is on this query:

The Anti Semi Join Case

Flip the test with NOT IN and the plan shows the opposite operator. This text plan on AdventureWorks2025 lists products that have no work orders:

USE AdventureWorks2025;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT ProductID
FROM Production.Product
WHERE ProductID NOT IN
      (SELECT ProductID FROM Production.WorkOrder);
GO
SET SHOWPLAN_TEXT OFF;
GO

NOT IN and NOT EXISTS can differ when the subquery contains NULL. For an absence test, I normally express the intended match with NOT EXISTS and check nullability explicitly. The plan can still change with indexes and data; the logical result is the interview answer.

Want to try this yourself? Here is how to install AdventureWorks.

A semi join is not a join that returns more rows, it is an existence test that returns each outer row once.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.