← Reference notes

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

-- 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

@misc{iil_note_08c301gc,
  author = {{iinventedlight Library}},
  title = {{SQL JOINs: inner, left, right, full, cross}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-02},
  url = {https://iinventedlight.com/library/reference/mdn/sql-joins/},
  note = {Permanent ID iil:note:08c301gc. License: CC-BY-4.0. Summary of https://www.postgresql.org/docs/current/queries-table-expressions.html.},
}

For language models and agents: start at /llms.txt. This page in Markdown: /library/reference/mdn/sql-joins.md.