---
title: "SQL JOINs: inner, left, right, full, cross"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source: https://www.postgresql.org/docs/current/queries-table-expressions.html
source_license: "PostgreSQL License (permissive); restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Source: PostgreSQL Global Development Group, \"Table Expressions: Joined Tables (PostgreSQL documentation, section 7.2.1.1)\" (PostgreSQL License (permissive); restated in our own words)."
condensed_by: "iinventedlight (Grok Bot, AI-assisted)"
fact_checked: 2026-11-02
date: 2026-11-02
status: reviewed
reviewed_on: 2026-11-02
category: programming
checked_on: 2026-11-02
license_url: https://creativecommons.org/licenses/by/4.0/
source: https://www.postgresql.org/docs/current/queries-table-expressions.html
source_license: "PostgreSQL License (docs); restated in our own words, no copied text"
attribution: "Summary by iinventedlight (AI-assisted), condensed and paraphrased; sources: The PostgreSQL Global Development Group, PostgreSQL documentation, Table Expressions (PostgreSQL License); restated in our own words"
condensed_by: "iinventedlight (Grok Bot, AI-assisted)"
fact_checked: 2026-11-02
date: 2026-11-02
category: programming
tags: programming, sql, postgresql, joins
checked_on: 2026-11-02
sources:
  - id: S1
    title: "Table Expressions: Joined Tables (PostgreSQL documentation, section 7.2.1.1)"
    publisher: "PostgreSQL Global Development Group"
    url: https://www.postgresql.org/docs/current/queries-table-expressions.html
    accessed: 2026-11-02
    license: "PostgreSQL License (permissive); restated in our own words"
    attribution: "The PostgreSQL Global Development Group, PostgreSQL documentation, Table Expressions (PostgreSQL License); restated in our own words"
---
# SQL JOINs: inner, left, right, full, cross

# SQL JOINs: inner, left, right, full, cross

## Summary
A JOIN combines rows from two tables. INNER JOIN keeps only the rows that match. LEFT, RIGHT and FULL OUTER JOIN also keep unmatched rows from one side or both, and fill the missing columns with NULL. CROSS JOIN pairs every row with every row. With outer joins, a filter in ON gives a different result than the same filter in WHERE.

*Draft. License: CC BY 4.0 (this summary). Sources: [S1] PostgreSQL Global Development Group, "Table Expressions: Joined Tables (PostgreSQL documentation, section 7.2.1.1)" (PostgreSQL License (permissive); restated in our own words).*

## Example
```sql
-- customers with their orders, including customers with none
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
```

## Facts
- id: F1
  claim: T1 CROSS JOIN T2 returns every combination of rows (N x M rows).
  confidence: medium
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02
- id: F2
  claim: INNER JOIN returns, for each row of T1, the rows of T2 that satisfy the join condition; unmatched rows are dropped. INNER is the default for JOIN.
  confidence: medium
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02
- id: F3
  claim: LEFT OUTER JOIN also keeps T1 rows with no match, filling T2's columns with NULL; the result always has at least one row per T1 row.
  confidence: medium
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02
- id: F4
  claim: RIGHT OUTER JOIN is the mirror image, keeping all T2 rows; FULL OUTER JOIN keeps unmatched rows from both sides.
  confidence: medium
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02
- id: F5
  claim: The join condition can be ON <boolean expression>, USING (shared column list), or NATURAL (all same-named columns).
  confidence: medium
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02
- id: F6
  claim: USING outputs each shared column only once, while ON outputs columns from both tables.
  confidence: medium
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02
- id: F7
  claim: For outer joins, a condition in ON is applied before the join (unmatched rows still appear with NULLs), whereas the same condition in WHERE filters after the join and can remove those rows.
  confidence: high
  date: 2026-11-02
  sources: [S1]
  license: CC BY 4.0
  checked_on: 2026-11-02

## Caveats
- NATURAL JOIN is fragile: adding a same-named column to either table silently changes the join.
- Exact syntax for FULL/RIGHT joins varies by database; older SQLite versions (before 3.39) lacked them.

## Pointers
- id: P1
  answers: Full details and updates
  where: https://www.postgresql.org/docs/current/queries-table-expressions.html
  access: free, public web page