Beginner

654. SEMI JOIN vs INNER JOIN

Compare EXISTS-based Semi JOIN logic with a regular INNER JOIN.

📘 Lesson 654 of 728 ⏱ 14 min 🎯 Advanced

A Semi JOIN checks whether a match exists, while an INNER JOIN combines matching rows and can return multiple rows when multiple matches exist.

Choosing EXISTS instead of JOIN can prevent accidental row multiplication when only existence matters.

Return each customer once if they have any order.

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

Used in customer eligibility, transaction checks, compliance validation, and reporting.

  • EXISTS checks existence.
  • INNER JOIN combines matching rows.
  • JOIN can duplicate outer rows.
  • EXISTS can naturally return one outer row.
  • Use the pattern that matches the business requirement.
When is EXISTS better than INNER JOIN?

When you only need to know whether a related record exists and do not need columns from that related table.

Can INNER JOIN create duplicate customers?

Yes. If a customer has multiple matching orders, the customer can appear multiple times.

Have you finished this lesson?

Introduction to SQL – All Lessons