DataTerminology.comNEW
Also known as: Left Semi Join
A join that returns rows from one table if a match exists in the other, without duplicating left rows.
DESCRIPTION
A semi join filters the left table to rows that have at least one match in the right table but returns only left-side columns and no duplication per multiple matches. It is semantically equivalent to an EXISTS subquery and is common in query optimizations for distributed systems. Many engines expose left semi join explicitly (e.g., Spark SQL) or implement it internally. Semi joins are crucial for selective filtering, de-duplication of matches, and reducing data movement in distributed joins.
ALTERNATIVE DEFINITIONS
Apache Spark
A left semi join returns only the columns from the left side for the rows for which there is at least one match on the right side.
Explicitly contrasts with inner join by returning only left-side columns and one row per match.
Oracle
In a semijoin, Oracle returns one copy of each row from one table for which at least one match is found in the other table.
Emphasizes deduplication behavior on the preserved side.
RELATED TERMS
Inner Join EXISTS Subquery Relational Algebra SQL Join Selectivity Broadcast Join
COMPETING TERMS
RELATIONS
- Broader: Join
KNOWLEDGE AREAS
Programming Languages Data Storage & Operations Data Integration & Interoperability Data Engineering
TARGET AUDIENCE
Data Engineer Analytics Engineer Data Scientist Software Developer
EXTERNAL SOURCES
- Wikipedia - Semijoin
- Apache Spark - Join reference including semi joins.
- Oracle - Optimizer semijoin transformation details.
- Microsoft - EXISTS semantics equivalent to semi join.
TOP QUESTIONS
What is a Semi Join in SQL?
A semi join returns rows from the left table that have at least one matching row in the right table, without duplicating left rows. It often corresponds to EXISTS subqueries.
Category: Definition
How does a Semi Join differ from an Inner Join?
Inner joins return columns from both sides and can duplicate left rows for multiple matches, while semi joins return only left columns and no duplicates.
Category: Misunderstanding
Why do distributed systems like Spark use Semi Joins?
Semi joins reduce data movement by filtering early, sending fewer rows to subsequent stages. This improves performance on large-scale joins.
Category: Performance
When should I prefer a Semi Join over EXISTS?
They are equivalent semantically; choose the one your engine optimizes best or that improves readability in your codebase.
Category: Alternatives
What is the relationship between Semi Join and Anti Join?
An anti join returns rows from the left with no matches on the right, the logical complement of a semi join’s matching behavior.
Category: Theory
What are common mistakes using Semi Joins?
Expecting right-side columns to be returned or assuming duplicates can appear. Semi joins only project left-side columns once per left row.
Category: Mistakes
How do I implement a Semi Join in SQL dialects without direct syntax?
Use EXISTS with a correlated subquery or IN over a distinct right-side key set. Both express semi join logic.
Category: Implementation
Is a Semi Join useful for de-duplication?
Yes, because it returns at most one copy of each left row that has a match, even if multiple matches exist on the right.
Category: Relevancy
What is a hot topic around Semi Joins?
Cost-based optimizers increasingly rewrite subqueries into semi joins automatically, improving performance without changing query semantics.
Category: Topic