Devs HiveDevs Hive
System DesignNew
AI InterviewNew
Interview Q&A
Roadmaps
Courses

Interview prep

  • QuizzesTest yourself against the clock
  • MentoringMock interviews and personal prep

Learning

  • Books for the AI eraBooks worth reading
Sign In
Menu
System DesignNewAI InterviewNewInterview Q&ARoadmapsCourses

Interview prep

QuizzesMentoring

Learning

Books for the AI era
© 2026 Devs Hive. All rights reserved.
Privacy policy·LinkedInYouTube
System DesignNew
AI InterviewNew
Interview Q&A
Roadmaps
Courses

Interview prep

  • QuizzesTest yourself against the clock
  • MentoringMock interviews and personal prep

Learning

  • Books for the AI eraBooks worth reading
System DesignNewAI InterviewNewInterview Q&ARoadmapsCourses

Interview prep

QuizzesMentoring

Learning

Books for the AI era

Join us

Sign in to access all features

Sign InCreate account
←Back to all questions

What is a SEMI JOIN and how does it differ from an INNER JOIN?

  • Databases
  • Junior
  • Middle
Devs Hive

Devs Hive

Seen on interview: 2 users

Short answer

A SEMI JOIN returns rows from one table that have at least one match in another. It does not include columns from the second table or duplicate rows when multiple matches exist. In PostgreSQL, this operation can be expressed using EXISTS.

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:

  1. users — the store’s users.
  2. 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:

SELECT u.id, u.name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.id
);

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?

  1. INNER JOIN returns all matching pairs of rows and lets you select columns from both tables.
  2. SEMI JOIN returns 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.”

Seen on interview?

Related Questions

  • DatabasesJunior/Middle/SeniorWhat Is SQL Partitioning and What Problems Does It Solve?Partitioning is a way to split one large table into several smaller pieces (partitions) so the database can work more easily with a large number of records. Imagine we are building a banking application that stores all user transactions in the transactions table. Hundreds of thousands of new transactions appear every day, and after a few years the table already contains several million records. At the same time, users most often view only the transactions for the last month. How does Partitioning help here? Instead of storing all transactions in one huge table, we can split it into parts by month: transactions_january — transactions for January. transactions_february — transactions for February. transactions_march — transactions for March. Now, when a user wants to view the transactions for February, the database can access only the corresponding part of the table without scanning the rest. This mechanism is called Partition Pruning. Meanwhile, to the developer it all looks like one regular transactions table. The database itself decides which partition to write new data to and where to read it from. What types of Partitioning are there? PostgreSQL has three main types: Range — splitting by ranges. For example, transactions by month or year. List — splitting by specific values. For example, orders by country: Ukraine, Poland, Romania. Hash — distributing records across partitions based on the hash of a particular column, for example user_id. Does Partitioning always speed up queries? No. If a query doesn't allow the database to determine which part of the table is needed, the database may still have to scan all partitions. In addition, Partitioning makes the database structure more complex, so it shouldn't be used unnecessarily (like any tool in programming). So, Partitioning is a way to organize the storage of large amounts of data that can speed up certain queries and simplify the maintenance of large tables.
  • DatabasesJunior/Middle/SeniorWhat is replica lag?Replica lag is the delay between changes being made in the primary database and those changes becoming available on its copy—a replica. Imagine we’re building an online store with two databases: The primary database accepts changes: new orders, price updates, and other writes. The read replica receives a copy of this data and handles read requests to reduce the load on the primary database. A user changes their delivery address and immediately refreshes the page. The new address has already been saved in the primary database, but the read request goes to a replica that hasn’t applied the change yet. As a result, the user sees the old address, even though the update was successful. Once the replica catches up with the primary database, the new address will appear. Why does this delay happen? With asynchronous replication, the primary database confirms a write without waiting for the replica to apply the changes. Transferring and processing those changes takes time. A slow network or a heavy load can increase the delay. How can we handle it? If a user needs to see newly saved data immediately, we can route that read request to the primary database. For example, after updating an address or placing an order. One important detail: replica lag doesn’t necessarily mean data has been lost. A replica may simply return outdated information temporarily, and we need to account for that when building an application.
  • JuniorMiddleDatabasesHow does FULL JOIN work?FULL JOIN is a type of JOIN that returns all rows from both tables. If the tables have a match, the rows are combined into a single result row. If there is no match, the missing values are filled with NULL. In other words, FULL JOIN combines the behavior of LEFT JOIN and RIGHT JOIN at the same time. Let's walk through an example We have two tables: users and subscriptions. Our goal is to get all users and all subscriptions, even if some users have no subscription, or if there is a subscription that is not linked to any user. -- users id | name ------------ 1 | Alice 2 | Bob 3 | John -- subscriptions id | user_id | plan_name ------------------------- 1 | 1 | Basic 2 | 2 | Premium 3 | 5 | Enterprise The query looks like this. Let's break it down in more detail: -- Вибираємо стовпці, які хочемо бачити в результаті: -- name - з таблиці users -- plan_name - з таблиці subscriptions SELECT users.name, subscriptions.plan_name FROM users -- Робимо FULL JOIN таблиці subscriptions -- по полю users.id та subscriptions.user_id FULL JOIN subscriptions ON users.id = subscriptions.user_id; After running the query, we get the following result: name | plan_name ------------------------- Alice | Basic Bob | Premium John | NULL NULL | Enterprise Key takeaways FULL JOIN returns all records from both tables. If there is a match, the data is combined. If there is no match, one side will contain NULL.

Comments (0)

Sign in to leave a comment

No comments yet. Be the first!