All articles

KajoData / SQL

FULL JOIN and SELF JOIN in SQL: gaps, matches and hierarchies

I show how to find missing records on both sides of a comparison and look up each employee's manager. We will also examine duplicates, filters and the limits of a single join.

Kajo RudzińskiPublished Updated 12 min read

Two conveyors carry crates to a platform that matches pairs and preserves unmatched items

Two systems contain different lists of orders. You want to see what agrees, what is missing from the first source and what is missing from the second. Searching through separate exports by hand soon becomes tedious. This is the kind of comparison where I reach for FULL JOIN.

A second task has a different shape. I have an employee list with a manager identifier on each row, but the manager’s name lives in the very same table. Here I need a SELF JOIN. Both names concern joins, yet they describe different choices: which unmatched rows survive, and which roles the sources play.

I will walk through both tasks using small synthetic datasets. The syntax targets PostgreSQL 18, and the expected results follow from its documented rules. If you need a refresher first, read about LEFT JOIN and missing matches.

Start by defining what one row represents

Before writing JOIN, I ask about the grain of each source. Does a row represent an order, an order line, a payment or an individual return? One order number may legitimately appear several times. Two product lines do not become duplicate data merely because they belong to the same shopping basket.

For the first example, I compare two order lists. Each identifier appears at most once in either list. The amount field holds an order value in the same arbitrary unit. I am not mixing currencies, accounting dates or definitions of sales. That assumption matters: identical numbers can still refer to different business quantities.

The operational list, called orders, contains:

order_id amount
1 100
2 200
3 300

The list from a second system, reported_orders, contains:

order_id amount
2 200
3 280
4 400

I can already identify four cases on paper. Order 1 exists only in the first source. Order 2 agrees, including its amount. Order 3 has conflicting amounts. Order 4 exists only in the second source. This is the distinction I expect my query to preserve.

Writing down that expectation takes little time. It also gives me a way to challenge a plausible result. A query returning only orders 2 and 3 may look tidy, but it has failed the comparison by hiding both types of missing record.

FULL JOIN preserves unmatched rows from both sides

The PostgreSQL join documentation defines FULL OUTER JOIN as preserving matched rows and unmatched rows from either side. Columns belonging to an absent partner receive NULL. The word OUTER is optional.

Here is a complete example for reading or reproducing yourself. VALUES stands in for stored tables, keeping all the input visible:

WITH orders(order_id, amount) AS (
    VALUES (1, 100), (2, 200), (3, 300)
), reported_orders(order_id, amount) AS (
    VALUES (2, 200), (3, 280), (4, 400)
)
SELECT
    o.order_id AS operational_id,
    r.order_id AS reported_id,
    o.amount AS operational_amount,
    r.amount AS reported_amount
FROM orders AS o
FULL OUTER JOIN reported_orders AS r
    ON o.order_id = r.order_id
ORDER BY COALESCE(o.order_id, r.order_id);
operational_id reported_id operational_amount reported_amount
1 NULL 100 NULL
2 2 200 200
3 3 300 280
NULL 4 NULL 400

The ON condition compares identifiers. Amounts are values I want to evaluate afterwards, so I keep them out of the matching key. If I also required amounts to agree in ON, order 3 would become two unmatched rows. The clear picture of one conflicting order would disappear.

I avoid teaching FULL JOIN as adding the outputs of LEFT JOIN and RIGHT JOIN. It is too easy to translate that slogan into UNION ALL, which includes shared matches twice. Plain UNION removes identical output rows and can conceal legitimate multiplicity. Understanding the matching rule comes before considering an emulation.

Distinguish an absent record from a missing value

In this example, every source row has an order_id. A NULL identifier after the join therefore indicates an absent partner. A missing amount offers no such guarantee: a record may exist while its value remains unknown. I use a field whose source contract excludes NULL to detect presence.

I can add the following expression to the previous SELECT list:

CASE
    WHEN o.order_id IS NULL THEN 'reported_only'
    WHEN r.order_id IS NULL THEN 'operational_only'
    WHEN o.amount IS DISTINCT FROM r.amount THEN 'amount_difference'
    ELSE 'equal'
END AS comparison_status

This is a fragment of the column list, using the same sources and join condition. In order, the four statuses are operational_only, equal, amount_difference and reported_only. A reader can understand the comparison without inferring meaning from the arrangement of empty fields.

The IS DISTINCT FROM operator handles comparisons involving NULL. Two missing values are not distinct; a number and a missing value are distinct. That is a useful technical rule, but I still have to decide whether two unknown amounts are acceptable in the business process. An equal label is no certificate of data quality.

I also examine missing keys. Ordinary NULL = NULL does not evaluate to true, so missing identifiers will not match under the shown condition. Replacing all of them with one invented number can connect unrelated anonymous records and create many accidental pairs. I keep that problem visible for separate investigation.

Duplicates change the arithmetic of a join

Suppose order 2 now appears twice in the first list and three times in the second. Each of the two rows matches each of the three rows. That produces six pairs. FULL JOIN has followed the condition correctly, even though the report author expected one row for one order.

If every matching row carries the full order amount, summing after the join inflates the result. Adding DISTINCT at the end does not explain the multiplicity. It might conceal it today and fail with tomorrow’s data. I would rather inspect the key and establish why it repeats.

For real tables, a small diagnostic query helps:

SELECT order_id, COUNT(*) AS rows_per_order
FROM reported_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

If the rows represent order lines, I may aggregate them to order level before comparing. If they represent successive export versions, I need a rule for selecting the applicable version. If they are accidental repetitions, the source needs repair or a documented removal rule. These are different fixes for different causes.

I also compare totals before and after joining. In the small example, the first source totals 600 and the second totals 880. The comparison retains both amounts in separate columns. Adding them into one revenue figure would count shared orders twice and answer a question nobody asked.

This is why a row count alone cannot approve a reconciliation. Four output rows look reassuring, but the amounts, source coverage and status counts still need to match the stated purpose. A short explanation beside the result is often more useful than another decorative chart.

Filters and sorting can change the picture of missing data

FULL JOIN does not promise to list common records first and missing records last. PostgreSQL requires ORDER BY when a particular order matters. In the example, COALESCE gives me the identifier available from either side for sorting.

In PostgreSQL, ascending order places NULL last by default, while descending order places it first. When that placement matters to the report, I write NULLS FIRST or NULLS LAST explicitly. I do not assume that every other database shares these defaults.

A filter presents a bigger trap. Appending WHERE o.amount > 0 removes order 4 because its o columns contain NULL. Comparing NULL with zero does not produce true. The result therefore stops showing all missing records from the second source, despite still containing the words FULL JOIN.

If I want to compare a particular month, I generally prepare two sources filtered to that month and join those inputs. Moving a filter into ON is not equivalent: a row that fails the matching condition can still survive a FULL JOIN as an unmatched row. I write down the intended scope before moving SQL conditions around.

SELF JOIN gives one table two roles

Now I want to display each employee alongside their direct manager. These records are synthetic, and employee identifiers are unique:

id employee_name manager_id
1 Ada NULL
2 Ben 1
3 Celina 1
4 Daniel 2
5 Ewa 99

Ada has no manager reference. Ben and Celina report to Ada. Daniel reports to Ben. Ewa refers to identifier 99, which does not exist in the table. I deliberately include that broken reference so it remains distinct from a valid absence of a manager.

A self join refers to the same table using separate aliases. Here e means employee and m means manager. An alias changes the role of a reference; it does not create another physical copy of the stored data:

WITH employees(id, employee_name, manager_id) AS (
    VALUES
        (1, 'Ada', NULL::integer),
        (2, 'Ben', 1),
        (3, 'Celina', 1),
        (4, 'Daniel', 2),
        (5, 'Ewa', 99)
)
SELECT
    e.id,
    e.employee_name,
    e.manager_id,
    m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;
id employee_name manager_id manager_name
1 Ada NULL NULL
2 Ben 1 Ada
3 Celina 1 Ada
4 Daniel 2 Ben
5 Ewa 99 NULL

A branching arrangement of blocks and a second view of the same board connected by a bridge

Choose relationship direction separately from join type

LEFT JOIN retains every employee from the e role. INNER JOIN would retain only employees with a matching manager: Ben, Celina and Daniel. Both Ada and Ewa would disappear. For a review of relationship quality, I therefore choose the version preserving the employee side.

I can distinguish the two missing-manager cases by inspecting both references. e.manager_id IS NULL means that no reference was recorded. e.manager_id IS NOT NULL AND m.id IS NULL means a recorded reference has no partner. Ada illustrates the first case and Ewa the second. Again, an identifier tells me more than an empty name.

Reversing the roles gives a different question: who reports to each person? I then join boss.id = subordinate.manager_id. Ada appears twice, once with Ben and once with Celina. That is a correct one-to-many result. It does not mean the source contains two people called Ada.

FULL JOIN and SELF JOIN are not mutually exclusive categories. A self join can use different join operators, including FULL JOIN. Referring to one source twice establishes its roles. Choosing LEFT, INNER or FULL determines what happens to unmatched rows. Keeping these decisions separate makes unfamiliar queries easier to read.

One self join covers one step in a hierarchy

Daniel’s direct manager is Ben, while Ada sits another level above. The single self join shown here will not automatically display the whole path from Daniel through Ben to Ada. Another reference can cover an additional fixed level; recursive CTEs address hierarchies whose depth varies.

Larger hierarchies introduce cycles and changes over time. Someone may incorrectly refer to themselves, or two people may form a loop. An employee may also change manager during a month. A simple identifier match cannot decide whether a report should show the current team or the organisation as it stood on the event date.

I take the same care when joining successive order statuses. Two records with the same order number do not automatically mean “previous” and “next”. I need a definition of sequence and a rule for tied timestamps. Depending on the task, a window function or a carefully constrained self join may be more appropriate.

Turn a comparison into questions someone can resolve

Once I have the four output rows, I do more than send a spreadsheet. The source owner needs a concrete question. For order 1, I ask whether the second system should already have received it. For order 4, I check whether both exports use the same cutoff. For order 3, I need the amount definition and any correction history. Three similar warning labels can require three different investigations.

The second source exceeds the first by 280. I can explain that difference directly: add 400 for the order present only on the right, subtract 100 for the order present only on the left, then subtract 20 for the conflicting amount on order 3. The result is 280. This checks whether the listed exceptions account for the entire gap between the reports.

A difference between grand totals would not tell me whether the problem involved missing orders or conflicting values. Two opposite mistakes might even cancel out and produce matching totals. I therefore compare key coverage and values separately, keeping the aggregate total as an additional check.

I include the date scope, extraction time and row definition when handing over the report. Sources refreshed at different intervals can have expected temporary gaps. I establish when such an exception should disappear and who can inspect its history before treating it as an input error.

For many exceptions, I keep both a detailed list and counts by status. The counts show whether the problem is growing. The list lets somebody trace and resolve an individual record. If systems use different identifiers, I retain both rather than replacing them with a single presentation label that nobody can search in the source application.

Choose a narrower join when the task calls for it

FULL JOIN helps when unmatched records from both sides matter. If I am enriching a complete employee list with department names, LEFT JOIN usually expresses the intended scope more directly. A department with no employees does not necessarily need its own row in that particular report.

When the task requires only common records, I choose INNER JOIN and state that unmatched rows are excluded. Their absence from the result does not prove absence from the source: a matching condition might have removed them. Investigating a missing order therefore means tracing the inputs and conditions, not merely searching the final export.

Before matching on customer names, I look for a stable identifier. A typo, an extra space or a renamed company can create apparent gaps. Two separate companies sharing a shortened name can create an apparent match. A join only applies the identity rule I actually supplied.

In a portfolio project, I document these choices beside the result. A few specific sentences about grain, key uniqueness and preserved gaps let another person judge the query’s purpose. Lengthy SQL alone cannot establish that the author understands the figures.

Exercise: predict the result before building a report

In the first dataset, change order 3 in the second source from 280 to 300. Its status becomes equal, while the total row count remains four. Next, add another order 2 row on the right. That order now has two matches, producing five rows overall. Explain what happens if you sum amounts from the left side.

In the employee table, change Ewa’s manager reference to 2. Her manager becomes Ben. Then consider replacing LEFT JOIN with INNER JOIN and explain why Ada still disappears. These changes examine relationship direction, missing matches and multiplicity together, using data small enough to reason through without a computer.

For my own work, I write a short statement of the row grain, matching key, missing records to preserve and expected row count. Then I choose the syntax. When a comparison needs several preparation steps, I organise them using CTEs and the WITH clause.

When handing over a comparison, I also ask for an explanation of unresolved exceptions. A useful report lets someone trace each flagged row back to its source and understand why it was marked.

Key takeaways

Does FULL JOIN remove duplicates?

No. It returns every pair satisfying the join condition, plus unmatched rows. Two rows on one side and three on the other can produce six matches.

Does SELF JOIN have its own syntax?

No. It means using the same table in two roles with separate aliases. You can use INNER JOIN, LEFT JOIN or another appropriate join operator.

Does FULL JOIN work in MySQL?

MySQL 8.4 has no native FULL OUTER JOIN. The examples in this article target PostgreSQL 18.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.