AI Glossary / Data engineering and architecture / Intermediate
Outer Join
Definition
An outer join combines matching rows from its inputs while also preserving specified unmatched rows. A left outer join preserves every row from the left input, a right outer join preserves every row from the right, and a full outer join preserves unmatched rows from both. In SQL, missing values from the unmatched side are represented by NULL in the result. The matching condition remains a separate part of the query.
What it means
An inner join answers a question about matching pairs. An outer join can also answer questions about entities without related records, such as customers with no orders. That distinction is important because absence may itself be meaningful. Preserving an unmatched row does not invent a related order or establish why it is missing; it makes the absence visible in the result under the query's conditions.
How it works
Evaluate the join condition to form matching row combinations. For the preserved side, include rows that found no match and fill the other side's output columns with NULL. Additional filtering can change the final result, especially when a WHERE condition refers to the nullable side. Conditions in ON determine what counts as a match; conditions in WHERE filter the result afterward in the logical query meaning. Duplicate matching keys can still multiply rows, so preservation does not imply one output row per preserved entity.
Three practical examples
- Sales reporting: a left join lists all customers, including those with no orders in the selected period.
- Data reconciliation: a full outer join helps compare identifiers from two systems and expose records present on only one side.
- AI review: source documents are left-joined to annotations so documents with no completed annotation remain visible in the work queue.
Think of it this way
Think of comparing a guest register with an attendance sheet while keeping every registered guest in the report. Guests without an attendance entry still appear with an empty attendance section. The analogy is limited because database NULL has specific logic, multiple attendance entries can produce several rows, and an empty result field may have several causes. The report must distinguish an absent match from a matched record whose own value is missing.
Worked example
A toy Customer table contains C1, C2, and C3. Orders contains O1 for C1 and O2 for C1; C2 and C3 have none. A left join from Customer to Orders produces four rows: two for C1 and one NULL-extended row each for C2 and C3. Counting OrderID per customer gives 2, 0, and 0 when OrderID is non-null for real orders. Counting all joined rows with COUNT(*) instead gives 2, 1, and 1, which is not the order count. Now suppose only paid orders should match. Placing the paid condition in the join condition preserves customers with no paid orders. Filtering the nullable order status in WHERE can remove those preserved rows. The right placement follows the reporting question, not a stylistic preference.
Illustrative case study
Fictional example: Birch Review uses an inner join to list articles and their editorial reviews. Unreviewed articles disappear, making the queue look complete. The team changes the query to preserve articles and labels unmatched reviews explicitly, while checking for multiple reviews per article. It also distinguishes unfinished reviews from no review record at all. This invented case illustrates the meaning of preservation; it does not claim that outer joins automatically produce a complete or correct workflow report.
Common misconceptions and mistakes
Do not assume a left join returns exactly as many rows as its left input. Multiple matches can increase the count. Do not replace every NULL with zero without understanding whether zero is a meaningful value. Avoid moving conditions between ON and WHERE casually, because their effect can differ for outer joins. Also distinguish a missing match from missing source data or a wrong join key: the result alone does not establish which explanation is correct.
Business use
Outer joins are valuable for completeness checks, reconciliation, and reporting on absent relationships. Define the population that must be preserved, the conditions that establish a match, and the intended handling of multiple matches. Use reliable non-null keys to identify absent matches where appropriate. For AI datasets, avoid silently dropping unlabeled or unmatched source records when their absence needs review. Preserve exception counts and investigate whether gaps arise from genuine missing work, incompatible identifiers, or an incomplete join condition.
People also ask
Does a left join keep all rows from both tables?
It preserves all left rows, but unmatched right rows are not included solely because it is a left join.
Can an outer join produce duplicate-looking rows?
Yes. Multiple matches and repeated values can create several result rows.
Is a full outer join the same as a Cartesian product?
No. It uses a matching condition and preserves unmatched rows; a Cartesian product combines every pair.
Check your understanding
A customer with no orders appears once in a left-join result. Does COUNT(*) = 1 prove the customer has one order?
Answer: No. That row may be the NULL-extended preserved customer row. Count an appropriate non-null order identifier when the intended measure is the number of matching orders.
Related terms
- inner join
- left join
- right join
- full outer join
- NULL
- join predicate
- anti-join
- reconciliation
- join cardinality
- aggregate function
Learn next
join a three-row parent table to a child table containing two matches for one parent and none for another. Compare inner, left, and full joins. Then move a child-side condition between ON and WHERE and explain the difference using the intended preserved population.
More in Data engineering and architecture
- Optical Multi-Tree with Shuffle Exchange - Optical Multi-Tree with Shuffle Exchange, usually abbreviated OMTSE, is a research interconnection architecture for parallel computers.
- Ordered key - value store - An ordered key - value store is a data store that associates keys with values while maintaining a defined ordering of the keys.
- Ordinal tree - An ordinal tree is a rooted tree in which each node's children have a defined order.
- PACELC design principle - PACELC is a design principle for reasoning about replicated distributed databases.
- Parallel algorithm - A parallel algorithm is a method for solving a problem that allows multiple operations to proceed at the same time.
- Parallel breadth-first search - Parallel breadth-first search is a graph-search method that uses simultaneous work to explore vertices in increasing distance from a starting vertex.
Sources
Sources: PostgreSQL 18, Joins Between Tables, https://www.postgresql.org/docs/18/tutorial-join.html ; PostgreSQL 18, Table Expressions, https://www.postgresql.org/docs/18/queries-table-expressions.html ; PostgreSQL 18, SELECT, https://www.postgresql.org/docs/18/sql-select.html . Primary join documentation and excerpts consulted for row preservation and NULL extension. Counting and filtering examples are original illustrations of SQL semantics, not observed application results.