Introduction

Selecting rows whose IDs appear in another table is one of the most common SQL tasks. The exact query shape depends on what you want back: only matching rows, rows plus related columns, or rows that merely satisfy an existence condition. In practice, the main tools are JOIN, IN, and EXISTS.

A Simple Example Schema

Assume you have two tables:

1CREATE TABLE customers (
2    id INT PRIMARY KEY,
3    name VARCHAR(100)
4);
5
6CREATE TABLE orders (
7    id INT PRIMARY KEY,
8    customer_id INT,
9    total DECIMAL(10, 2)
10);

If you want orders whose customer_id matches an ID in customers, you are asking for rows linked by a foreign-key-style relationship.

Use JOIN When You Need Data From Both Tables

JOIN is usually the clearest choice when you also want columns from the related table.

1SELECT o.id, o.total, c.name
2FROM orders AS o
3JOIN customers AS c
4  ON o.customer_id = c.id;

This returns only orders that have a matching customer. It is a natural fit when the relationship itself matters and you want data from both sides.

You can add filters on either table:

1SELECT o.id, o.total, c.name
2FROM orders AS o
3JOIN customers AS c
4  ON o.customer_id = c.id
5WHERE c.name LIKE 'A%';

That is easier to read than a nested subquery when the relationship is central to the query.

Use IN When You Only Need Membership

If you only need rows from one table and do not care about the other table's columns, IN can be concise.

1SELECT id, total, customer_id
2FROM orders
3WHERE customer_id IN (
4    SELECT id
5    FROM customers
6);

This reads as, "Give me orders whose customer_id is one of the IDs returned by the subquery."

IN is often perfectly fine, especially when the subquery is simple and the intent is clearly membership-based.

Use EXISTS for Correlated Existence Checks

EXISTS is useful when you care about whether a related row exists, not about joining all of its columns.

1SELECT o.id, o.total
2FROM orders AS o
3WHERE EXISTS (
4    SELECT 1
5    FROM customers AS c
6    WHERE c.id = o.customer_id
7);

This is semantically close to the IN example, but EXISTS often expresses the intention more directly for correlated checks.

It becomes especially useful when the subquery has additional conditions:

1SELECT o.id, o.total
2FROM orders AS o
3WHERE EXISTS (
4    SELECT 1
5    FROM customers AS c
6    WHERE c.id = o.customer_id
7      AND c.name LIKE 'A%'
8);

Choosing Between Them

Use JOIN when:

• you need columns from both tables
• the relationship is central to the query result
• you want readable relational structure

Use IN when:

• you only need rows from one table
• the related table is just a source of IDs
• the membership logic is simple

Use EXISTS when:

• you want to check whether a related row exists
• the condition is naturally correlated
• you want to avoid thinking of the other table as a list of values

Modern query planners can optimize many of these forms similarly, so clarity of intent is often a better first decision criterion than folklore about one always being faster.

Indexes Still Matter

Whichever syntax you choose, indexes on the relevant ID columns matter for performance.

Typical helpful indexes are:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

If customers.id is the primary key, it is already indexed in most systems. Without proper indexing, even a logically correct query can become slow on large tables.

One Important Duplicate Detail

JOIN can duplicate rows if the relationship is one-to-many on the joined side. IN and EXISTS do not duplicate the outer rows in the same way. If duplicates appear unexpectedly after a JOIN, inspect the cardinality of the relationship rather than assuming SQL is behaving strangely.

Common Pitfalls

The most common mistake is using a JOIN when you only needed an existence check, then being surprised by duplicated rows from a one-to-many relationship.

Another mistake is writing an IN subquery and later needing related columns from the second table. At that point, a JOIN is usually clearer.

Developers also often forget indexes on foreign-key-style columns, which can make simple relationship queries slow on larger datasets.

Finally, avoid SELECT * in joined queries unless you truly need every column. Being explicit makes the result easier to understand and maintain.

Summary

• Use JOIN when you need related columns from another table.
• Use IN when you only need to filter by a set of IDs.
• Use EXISTS when the real question is whether a related row exists.
• Index the ID columns involved in the relationship.
• Watch for duplicate rows when joining across one-to-many relationships.