SQL INNER JOIN: matches, keys and duplicate rows

INNER JOIN can make an incomplete report look convincing. The query runs, the columns have sensible names and missing records quietly disappear. Without defining who belongs in the result, you may never notice the omission. Before joining, I write down what a row means in each source and which matches I want to keep.

I will use employees and departments to show the approach. All data is synthetic and unrelated to a particular business. The examples use PostgreSQL 18. I first derive a small result manually, then introduce repeated keys, missing assignments and a second matching condition. This makes it easier to see why valid syntax alone cannot establish a correct analysis.

INNER JOIN returns matching pairs

INNER JOIN is a join operation. For each row in one source, it returns every row in the other source for which the ON condition is true. Rows without a matching partner are absent from the result. In PostgreSQL, JOIN without another qualification means INNER JOIN. The join documentation provides the definition.

The useful word here is pairs. One employee matching three department records produces three result rows. An employee matching none produces zero. The operation does not automatically select the first matching record or check whether the department key ought to be unique.

I also avoid treating INNER JOIN as the intersection of two lists of names. It combines rows under a condition and then lets you select display columns. Two different matches can look identical after you hide their identifiers. They remain different matches unless another operation combines or removes them.

A small example: employee and department name

The employees source has four rows, each representing one employee. The department_id field records the assignment, while NULL represents an unknown assignment.

The departments source contains three departments with unique identifiers. Notice that department 30 is absent, while department 40 exists without a corresponding employee in our list.

I want to show only employees whose department name can be matched. That restriction is part of the question. If the recipient needs every employee, including incomplete assignments, I need a different join. A missing relationship should not silently determine the scope of the report.

A query you can read in full

WITH employees(employee_id, employee_name, department_id) AS (
    VALUES
        (1, 'Ada', 10),
        (2, 'Ben', 20),
        (3, 'Celina', 30),
        (4, 'Dan', NULL)
), departments(department_id, department_name) AS (
    VALUES
        (10, 'Analysis'),
        (20, 'Operations'),
        (40, 'Sales')
)
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d
    ON d.department_id = e.department_id
ORDER BY e.employee_id;

WITH names the small sources constructed with VALUES. The VALUES documentation describes supplying a table of values without creating a permanent table. Later examples assume the same sources. Retain this WITH section and replace the final SELECT when practising with the dataset.

The aliases e and d identify the source of each field. I write e.department_id because both sources contain that column name. Explicit prefixes become particularly useful as a query grows and somebody adds another column with a familiar name.

Why only two rows remain

Ada matches department 10 and Ben matches department 20. There is no department 30 for Celina, so she is absent. Dan has NULL in the key, and ordinary equality involving that missing value does not produce true. Sales is absent because no employee matches it.

I therefore cannot label these two rows all company employees. They are employees matched to the supplied department lookup. That distinction can affect a metric denominator, a cost allocation or the interpretation of missing data. A tidy result table can easily disguise the loss of population.

Swapping the sources would preserve the set of matching pairs for this equality condition. That does not promise the same column arrangement under SELECT star or an identical execution plan. I use an explicit column list and ORDER BY to specify how the result should be presented.

An absent match can expose a quality issue

Celina and Dan disappear for different reasons. Celina has a specific department number missing from the lookup. Dan has no known number. INNER JOIN removes both rows, but the appropriate corrective action may differ. I separate these cases when assessing source quality.

A department missing from a lookup is not automatically a typing error. The lookup might come from a different date, include only active units or be an incomplete export. I first check the scope and origin of each source before declaring a record invalid.

For a quality report, I would use LEFT JOIN to retain employees and expose an absent right-side identifier. If I only need people without a relationship, I would consider NOT EXISTS. INNER JOIN shows matches, but does not show what it discarded.

Repeated lookup keys multiply rows

Consider a separate variation of the dataset. Department identifier 10 appears twice: once as Analysis and once as Analytics. All other records remain unchanged. Ada now matches both department rows, while Ben still matches one. The result grows from two rows to three.

This does not mean the database is behaving randomly. The condition received two matches that satisfy its logic. The question is whether both lookup records should exist. They might be an import duplicate, or legitimate historical versions requiring an additional date condition.

A primary key can enforce uniqueness and exclude NULL. A foreign key can enforce the existence of a referenced record, while still permitting NULL unless its column also has a NOT NULL restriction. The constraint documentation distinguishes these roles. Writing a JOIN creates none of those guarantees by itself.

I can inspect a proposed lookup key before using it:

SELECT department_id, COUNT(*) AS row_count
FROM departments
GROUP BY department_id
HAVING COUNT(*) > 1;

For the original lookup, this result is empty. For the variation, it shows department 10 with count two. That is a lead for investigating the source, rather than permission to delete an arbitrary record. The meaning of the data must justify which version should be selected.

Many-to-many relationships need an explicit interpretation

Imagine two separate assignment sources. One has two records for project A, and the other has three records for that project. Joining only by project produces six pairs. Each of the two records on one side matches each of the three on the other.

That can be correct when you want every combination. If you are trying to attach hours and a budget to the project, however, six rows may multiply both measurements. Before applying SUM, establish the level at which each measurement is stored.

I aggregate independent sources to a common grain before joining when that grain answers the question. A project report might need one hours total and one budget amount per project. Detailed tasks and detailed invoices may deserve separate views rather than one wide result with ambiguous measures.

DISTINCT does not replace this decision. It removes identical selected rows and may hide a mistake or remove legitimate events with identical visible values. I first keep both source identifiers and explain where each matching pair comes from. The output should be understandable before any cosmetic cleanup.

Sometimes a key contains two fields

An order number might be unique only within a company or source system. Joining by order_id alone could match order 7 in company A to order 7 in company B. SQL cannot infer the numbering rules in the author’s head.

In that model, both key components matter. The following fragment uses separate order and item sources containing company_id and order_id.

SELECT o.company_id, o.order_id, i.item_id
FROM orders AS o
INNER JOIN order_items AS i
    ON i.company_id = o.company_id
    AND i.order_id = o.order_id;

This is a separate scenario from the employee dataset. An item must agree with its order on both the company and the order number. In a real database, inspect the relevant composite constraints rather than assuming that written business documentation prevents invalid records from being stored.

A similar issue arises with periods and versions. A business identifier repeated across history may be insufficient on its own. Define which source version should accompany an event. A table named current is not a replacement for a documented rule about the dates or versions it actually contains.

ON, USING and the risk of NATURAL JOIN

When both sides use the same key name, I can write USING (department_id). In our example this compares department identifiers. USING also presents one common key column when selecting all columns, whereas ON leaves both key columns available in that output.

NATURAL JOIN automatically chooses all column names shared by the sources. Adding a new column with the same name on both sides can therefore change its meaning. The PostgreSQL join documentation explains both the mechanism and the risk from schema changes.

For a maintained report, I prefer explicit ON or a deliberately selected USING list. The relationship contract is visible. An additional status column in both tables should not accidentally become another matching requirement. A few extra characters can make a query much easier to review.

Understand where filters belong

For an ordinary INNER JOIN, a condition can be placed in ON or WHERE without changing the matching result if the Boolean logic remains the same. I usually put key relationships in ON and report restrictions in WHERE. This makes the intention easier to read.

Filtering to department 10 would keep Ada and remove Ben. That is a deliberate population restriction applied to matched records. It belongs in the report definition, because a later reader might otherwise assume the output includes every employee with a valid lookup match.

Do not transfer that habit mechanically to LEFT JOIN. Moving a condition from ON to WHERE can remove rows retained by the outer join. When changing a join type during maintenance, review all right-side filters again. Replacing one keyword does not finish the analytical work.

Use EXISTS when you only need a match to exist

SELECT e.employee_id, e.employee_name
FROM employees AS e
WHERE EXISTS (
    SELECT 1
    FROM departments AS d
    WHERE d.department_id = e.department_id
)
ORDER BY e.employee_id;

The original dataset returns Ada and Ben. In the variation with two department 10 records, Ada still appears once because EXISTS tests whether at least one match exists. No department columns are attached. The EXISTS documentation distinguishes this effect from producing every pair.

I do not claim EXISTS is always faster. I choose it because it expresses the question precisely. Performance depends on the plan and data. If I need the department names from every match, INNER JOIN still has the appropriate role. The subquery guide explores the alternatives in more detail.

A join condition can describe a range

Equality is common, but ON can contain other Boolean conditions. Consider a separate example assigning measurements to bands. The measurements source contains an identifier and value. The bands source contains lower_bound, upper_bound and band_name.

SELECT m.measurement_id, m.value, b.band_name
FROM measurements AS m
INNER JOIN bands AS b
    ON m.value >= b.lower_bound
    AND m.value < b.upper_bound;

Suppose one synthetic band starts at zero inclusive and ends at one hundred exclusive. The next starts at one hundred inclusive and ends at two hundred exclusive. A measurement of one hundred matches only the second band. Including the lower boundary and excluding the upper boundary prevents this shared edge from creating two matches.

Using BETWEEN for both bands would include one hundred in both because BETWEEN includes both endpoints. The comparison documentation describes that rule. Range notation therefore belongs to the category definition, rather than being a cosmetic SQL choice.

For range joins, I inspect both coverage and overlap. A gap can leave a measurement unmatched; overlapping intervals can give it two categories. INNER JOIN follows the condition in both cases, even when the recipient expects exactly one category per measurement.

The same issue appears with historical validity periods. When one version ends as another begins, define which version includes an event at the boundary. Adding dates to ON does not fix ambiguous source intervals. The rule used to construct those intervals needs to be clear first.

Empty sources and unknown keys

If either source of an ordinary INNER JOIN is empty, no pairs are produced. This is useful when predicting a report for a period without events. An empty result can be correct, but should not be confused with a measured value of zero.

Ordinary equality also does not match two NULL keys. Two unknown assignments do not establish a common identity. I therefore avoid replacing all missing identifiers with one shared number before joining. That could connect unrelated objects whose only shared property is an absent identifier.

How I review a completed join

I first predict the result for a matched record, an unmatched record and a record with several matches. Then I compare the number of distinct input and output keys. Losing employees may be intentional, but the loss should have a clear explanation.

Next I inspect measurements. If a total grows after attaching a descriptive lookup, I check lookup uniqueness. If it falls, I investigate discarded records. Row count alone cannot detect a situation in which some events disappear while other events are multiplied at the same time.

Finally, I separate correctness from performance. Following matching rows manually explains the result without specifying the database’s physical algorithm. EXPLAIN exposes the chosen plan. I do not infer speed from the written order of the two tables.

Exercise: predict the output first

Add department 30 named Support to the original lookup. INNER JOIN should now return Ada, Ben and Celina. Dan still has no matching department because his assignment is unknown. Department 40 still produces no pair.

Then add a second record for department 10. Predict four output rows: two for Ada, one for Ben and one for Celina. Compare that with EXISTS, which still returns three people. Which query fits the request to list employees whose department exists in the lookup?

If you can explain why every employee is included or excluded, you have a useful foundation for further work. Continue with preserving unmatched rows using LEFT JOIN and the logical order of SQL clauses. Whenever you add another relationship, return to the question of what one result row represents.