Beginner

534. SEMI JOIN

Learn the concept of SEMI JOIN and how EXISTS can return rows when a related record exists.

📘 Lesson 534 of 728 ⏱ 12 min 🎯 Advanced

A SEMI JOIN returns rows from one table when at least one matching row exists in another table. SQL commonly expresses this logic using EXISTS.

Sometimes you only need to know whether a relationship exists rather than retrieve columns from the related table.

Find customers who have at least one order without returning the order details.

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

Used in customer segmentation, validation, eligibility checks, compliance, and existence testing.

  • SEMI JOIN returns rows when a match exists.
  • EXISTS is commonly used.
  • The related table does not need to appear in the final output.
  • It avoids unnecessary duplicate rows from multiple matches.
  • It is useful for existence checks.
What is a SEMI JOIN?

A SEMI JOIN returns rows from one table when at least one matching row exists in another table.

Which SQL operator commonly implements SEMI JOIN logic?

The EXISTS operator is commonly used to implement SEMI JOIN logic.

Have you finished this lesson?

Introduction to SQL – All Lessons