Semi Join

Overview

A Semi Join returns rows from the left table that have at least one match in the right table, without duplicating left-table rows or adding right-table columns.

Learning Objectives

• Understand Semi Join execution logic.
• Implement Semi Joins using EXISTS or IN.
• Prevent row multiplication caused by standard INNER JOINs.

Detailed Concept Explanation

When you use a standard INNER JOIN to check for matching records in a child table, if a parent row matches multiple child rows, the parent row is duplicated in the output.

A Semi Join avoids this duplication. It evaluates whether a match exists, and as soon as it finds the first match, it includes the left row and moves on to the next one.

In SQL, Semi Joins are implemented using EXISTS or IN.

Code Examples

SQL

-- Standard INNER JOIN can duplicate customers if they have multiple orders
-- SEMI JOIN using EXISTS ensures each customer is listed EXACTLY ONCE
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Alternative SEMI JOIN using IN
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (
  SELECT o.customer_id FROM orders o
);

Best Practices

• Prevent Row Duplication: When you only want to test whether matching records exist in a secondary table without retrieving data from it, use a Semi Join (EXISTS) instead of an INNER JOIN.

Interview Perspective

Interactive Challenges

Summary

Semi Joins return outer rows that have at least one match in a secondary table, preventing duplicate output rows.