A SEMI JOIN is an operation that returns rows from one table if at least one matching row exists in another table.
Columns from the second table are not included in the result, and multiple matches do not produce additional copies of the rows.
What problem does it solve?
Imagine an online store with two tables:
users— the store’s users.orders— their orders.
We need to retrieve all users who have placed at least one order.
With a regular INNER JOIN, a user with five orders appears five times in the result — once for each order. But we only need a list of users.
A SEMI JOIN solves this problem: it checks whether a match exists without multiplying rows when a user has several orders.
How do you write this query?
PostgreSQL does not have a dedicated SEMI JOIN keyword. You can express this operation using EXISTS:
For each user, we check: does at least one order belong to this user? If so, the user is included in the result.
It does not matter whether they have one, five, or a hundred orders. Each row from users appears at most once.
SELECT 1 does not mean “return one order.” For EXISTS, only the presence of a matching row matters, not the value selected by the subquery.
How does a SEMI JOIN differ from an INNER JOIN?
INNER JOINreturns all matching pairs of rows and lets you select columns from both tables.SEMI JOINreturns only rows from the first table that have a match in the second.
A SEMI JOIN is useful for queries such as “users who have orders,” “products that have been purchased at least once,” or “authors who have published at least one article.”