SQL JOINs vs Subqueries: When to Use Each (and Why)

Find customers who have placed at least one order

A support engineer needs a list of paying customers. In Chinook, a completed purchase is represented by an invoice. The question sounds simple: “Which customers have at least one invoice?” But should the report contain one row per customer, or one row per invoice? That choice—the result grain—matters more than choosing a favourite SQL keyword.

A JOIN combines rows from two table expressions using a relationship. A subquery is a query nested inside another SQL statement. They are not opposing categories: a JOIN can join a subquery. Use a join when you need related columns, and consider EXISTS when you only need to know whether a related row exists.

The examples show why three queries identify the same customers while another returns 412 invoice matches for 59 customers. They also show why “joins are always faster” is not a useful rule. SQL semantics determine correctness; the engine’s plan and measurements determine performance.

The Chinook sample database

These examples use the complete Chinook music-store database available in the DataTug demo: 59 customers and 412 invoices, with every original table and value retained. All 59 customers have invoices. Queries that focus on one customer or qualifying invoices show that condition explicitly in the SQL.

The relationship is Customer.CustomerId → Invoice.CustomerId: one customer can have many invoices; each invoice belongs to one customer. InvoiceId is the invoice’s unique key. InvoiceDate is ISO-sortable text; Total is a numeric amount in the dataset’s units. This source uses dates from 2021–2025; other Chinook releases differ. Invoice IDs are not necessarily chronological.

Results are shown as a preview for clarity. Tables with more than 10 records initially show the first 10. Choose Show all to reveal every matching record, or Run to execute the exact displayed SQL in the DataTug editor against the same Chinook database. No query is saved to the project. The preview does not add a WHERE or LIMIT clause. Synthetic examples define their own rows with VALUES in the displayed query.

Download the complete Chinook SQLite database and open it with SQLite 3.25.0 or later, then run any query below. No account or network database is required. Query downloads contain every displayed example and exercise solution. Frozen result tables are checked against this database; each code header states its minimum SQLite version.

Chinook upstream and schema · Chinook licence · Source attribution. SQLite uses binary floating-point for many decimal operations; examples round money for display. Production finance needs an explicit exact-decimal or integer-minor-unit policy.

JOINs describe relationships, not deduplication

An INNER JOIN emits a row for each pair satisfying its condition. A customer with seven invoices participates in seven matching pairs. A customer with none participates in none. Selecting only the customer’s name does not change how many pairs exist.

SELECT c.CustomerId, c.FirstName, i.InvoiceId
FROM Customer AS c
INNER JOIN Invoice AS i ON i.CustomerId = c.CustomerId
ORDER BY c.CustomerId, i.InvoiceId;

A LEFT JOIN also keeps a left-side row when no right-side match exists. Right-side columns become NULL. It does not promise one row per left record: multiple matches still multiply rows. Use it when unmatched records belong in the report.

SELECT c.CustomerId, c.FirstName, i.InvoiceId, i.Total
FROM Customer AS c
LEFT JOIN Invoice AS i
 ON i.CustomerId = c.CustomerId AND i.Total > 20
ORDER BY c.CustomerId, i.InvoiceId;

Conditions in ON decide which invoice can match. A condition on i.Total in the outer WHERE runs after null extension; unknown comparisons are discarded. Moving that condition can remove customers without qualifying invoices. That is a correctness change, not a formatting preference.

A subquery can return a value, a set, or a table

A scalar subquery supplies one value, usable in a selected expression or comparison. A non-correlated subquery does not refer to outer-query columns. A correlated subquery does: its meaning depends on the current outer row. These describe dependencies, not a guarantee that the engine physically reruns a query for every row.

SELECT c.CustomerId, c.FirstName
FROM Customer AS c
WHERE EXISTS (
 SELECT 1 FROM Invoice AS i
 WHERE i.CustomerId = c.CustomerId
)
ORDER BY c.CustomerId;

EXISTS ignores selected values: even a selected NULL establishes existence if a row exists. The correlation predicate does the essential work. IN tests whether an expression belongs to a single-column subquery result. Both are useful in WHERE; choose the one making the relationship clearest.

SELECT CustomerId, FirstName
FROM Customer
WHERE CustomerId IN (SELECT CustomerId FROM Invoice)
ORDER BY CustomerId;

For “show each customer and their spending”, a scalar aggregate expresses one summary per customer. The aggregate guarantees one result row. Beware scalar queries simply selecting an invoice amount: a customer can have many invoices, and engines differ in handling multi-row scalar results.

SELECT c.CustomerId, c.FirstName,
 COALESCE((SELECT ROUND(SUM(i.Total), 2)
 FROM Invoice AS i WHERE i.CustomerId = c.CustomerId), 0) AS Spending
FROM Customer AS c
ORDER BY c.CustomerId;

A subquery in FROM, a derived table, can establish the grain before a join. The inner grouping creates one row per customer, so the outer join preserves that summary grain. This is often clearer when you need several aggregates from the same input.

SELECT c.CustomerId, c.FirstName, COALESCE(s.Spending, 0) AS Spending
FROM Customer AS c
LEFT JOIN (
 SELECT CustomerId, ROUND(SUM(Total), 2) AS Spending
 FROM Invoice GROUP BY CustomerId
) AS s ON s.CustomerId = c.CustomerId
ORDER BY c.CustomerId;

Compare equivalent lists and different row counts

The customer-only DISTINCT JOIN, EXISTS and positive IN return all 59 customer IDs. Their equivalence depends on selected columns and the question. The raw join includes InvoiceId and has 412 rows. Switch approaches and highlight a customer to follow multiplication. Without JavaScript, all alternatives remain visible.

SELECT c.CustomerId, c.FirstName, i.InvoiceId
FROM Customer AS c
INNER JOIN Invoice AS i ON i.CustomerId = c.CustomerId
ORDER BY c.CustomerId, i.InvoiceId;

SELECT DISTINCT c.CustomerId, c.FirstName
FROM Customer AS c
JOIN Invoice AS i ON i.CustomerId = c.CustomerId
ORDER BY c.CustomerId;

SELECT c.CustomerId, c.FirstName
FROM Customer AS c
WHERE EXISTS (
 SELECT 1 FROM Invoice AS i
 WHERE i.CustomerId = c.CustomerId
)
ORDER BY c.CustomerId;

SELECT CustomerId, FirstName
FROM Customer
WHERE CustomerId IN (SELECT CustomerId FROM Invoice)
ORDER BY CustomerId;

DISTINCT is appropriate for a distinct set of projected values. It is a poor emergency fix for an unexplained join: it can hide missing conditions or merge different customers sharing a name. Decide whether you need invoice detail, customer membership or a customer summary before removing repeats.

NULL, missing conditions and hidden multiplication

For “customers with no invoices”, NOT EXISTS states absence directly. Another approach is a left join followed by checking a non-nullable right-side key for NULL. Avoid checking an optional attribute: a real matching row might have a null attribute too.

SELECT c.CustomerId, c.FirstName
FROM Customer AS c
WHERE NOT EXISTS (
 SELECT 1 FROM Invoice AS i WHERE i.CustomerId = c.CustomerId
)
ORDER BY c.CustomerId;

Here every customer has an invoice, so the absence query returns no records. The LEFT JOIN example above uses the visible condition i.Total > 20 to demonstrate customers without a qualifying invoice. A missing correlation can also be hidden by a dataset where every customer already has a match.

NOT IN is not generally interchangeable with NOT EXISTS when the subquery can produce NULL. SQL has three-valued logic: true, false or unknown. To say “2 is not in this set”, every equality must be false. A null comparison leaves uncertainty, so the predicate does not pass WHERE.

WITH candidates(value) AS (VALUES (1), (NULL))
SELECT 2 AS Candidate
WHERE 2 NOT IN (SELECT value FROM candidates);

WITH candidates(value) AS (VALUES (1), (NULL))
SELECT 2 AS Candidate
WHERE NOT EXISTS (SELECT 1 FROM candidates WHERE value = 2);

If your rule excludes unknown values, explicitly filter nulls from the membership set and consider nullable outer values separately. If your rule is “no equal record exists”, equality-based NOT EXISTS expresses it directly. Neither form decides your business policy for missing identifiers.

SELECT c.CustomerId, c.FirstName
FROM Customer AS c
WHERE EXISTS (SELECT 1 FROM Invoice AS i)
ORDER BY c.CustomerId;

SELECT COUNT(*) AS Combinations
FROM Customer AS c CROSS JOIN Invoice AS i;

Two independent one-to-many joins can be worse: joining a customer’s invoices and support tickets produces every invoice–ticket pair for that customer. Aggregates may overcount both. Aggregate each relationship to the desired grain first, or use independent existence checks. More nesting is not automatically safer: it should clarify a real dependency.

Which is faster? Inspect the actual plan

There is no universal winner. Optimisers can transform some membership and existence tests into semi-join strategies: preserve a left row if a match exists without outputting every matching pair. Correlation can become an efficient indexed lookup; it can also cause repeated scans. Available rewrites depend on the engine, query shape, nullability and statistics.

An index beginning with Invoice.CustomerId may help equality probes or grouped summaries. Selectivity matters: if nearly all customers qualify, scanning may be cheaper. JOIN plus DISTINCT may require sorting or a temporary structure, but another engine may avoid it. Derived aggregates may materialize or flatten. Read the plan rather than inferring execution from indentation.

1. Verify identical semantics, including duplicates and NULL.
2. Inspect SQLite EXPLAIN QUERY PLAN or your engine’s equivalent for scans, index searches and temporary structures.
3. Measure representative row counts, skew, indexes and current statistics. Record engine/version and relevant warm/cold cache conditions.
4. Recheck correctness after tuning. A faster query dropping unmatched customers answers a different question.

This sample database teaches semantics; it is not a production benchmark. No timing claims are made. Adding an index here would not establish production performance.

A practical decision guide

For the opening question, EXISTS communicates intent directly. If you next need invoice dates and amounts, a join is natural. To preserve invoice details while adding customer totals, a window function may fit better than a grouped join or repeated scalar expression.

Practise: state the grain first

Use the complete database unchanged. Predict which customers have invoices over 20 and which should receive a zero count before revealing a solution.

SELECT c.CustomerId, c.FirstName
FROM Customer AS c
WHERE EXISTS (
 SELECT 1 FROM Invoice AS i
 WHERE i.CustomerId = c.CustomerId AND i.Total > 20
)
ORDER BY c.CustomerId;

SELECT c.CustomerId, COUNT(i.InvoiceId) AS InvoiceCount
FROM Customer AS c
LEFT JOIN Invoice AS i ON i.CustomerId = c.CustomerId AND i.Total > 20
GROUP BY c.CustomerId
ORDER BY c.CustomerId;

WITH spending AS (
 SELECT CustomerId, SUM(Total) AS Amount
 FROM Invoice GROUP BY CustomerId
)
SELECT CustomerId, ROUND(Amount, 2) AS Spending
FROM spending
WHERE Amount > (SELECT AVG(Amount) FROM spending)
ORDER BY CustomerId;

References and next steps

• SQLite SELECT and joins
• SQLite IN, NOT IN, EXISTS and subqueries
• SQLite optimiser overview
• PostgreSQL subquery-expression semantics

Download every example and solution · Next: Window Functions Explained · SQL & Data Skills.

Corrections: report an article issue with the query, SQLite version and expected result. AI-assisted writing with automated SQL checks and independent technical review.