api: YAML JSON TOON Database
version: 1.0.0
format: yaml
dataset:
  id: 2902
  slug: sql-join-types
  title: SQL Join Types
  description: Reference for all SQL JOIN types and their use cases.
  summary: "The SQL join types with a description and example syntax: inner, left, right, full outer, cross and self joins, plus semi and anti joins, lateral joins and natural joins that appear in real queries."
  use_when:
    - You need the exact behavior of a join type or a syntax example.
    - You are choosing between joining and filtering with EXISTS.
  caveats:
    - Join semantics are standard, but syntax varies slightly across databases (for example CROSS APPLY in SQL Server).
    - NULLs never match in join conditions; use IS NOT DISTINCT FROM or handle them explicitly where needed.
  related:
    - sql-query-types
    - database-comparison
    - acid-vs-base
    - sql-window-functions
    - sql-index-patterns
  sources:
    - title: PostgreSQL table expressions
      url: "https://www.postgresql.org/docs/current/queries-table-expressions.html"
  reading:
    - sql-window-functions-running-totals-and-ranks
  reviewed_at: "2026-10-12"
  provenance: Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses.
  entry_count: 15
  fields:
    - section: data
      field: type
      type: string
      example: INNER JOIN
    - section: data
      field: description
      type: string
      example: Returns rows where there is a match in both tab…
    - section: data
      field: syntax
      type: string
      example: SELECT * FROM a INNER JOIN b ON a.id = b.a_id
  category: Database Types
  category_slug: database-types
  tags: database,sql,joins,reference
  view_count: 42
  created_at: 1793403093
  updated_at: 1791799373
data:
  - type: INNER JOIN
    description: Returns rows where there is a match in both tables.
    syntax: SELECT * FROM a INNER JOIN b ON a.id = b.a_id
  - type: LEFT JOIN
    description: Returns all rows from the left table and matched rows from the right.
    syntax: SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
  - type: RIGHT JOIN
    description: Returns all rows from the right table and matched rows from the left.
    syntax: SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id
  - type: FULL OUTER JOIN
    description: Returns all rows from both tables, with NULLs for non-matches.
    syntax: SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id
  - type: CROSS JOIN
    description: Returns the Cartesian product of both tables.
    syntax: SELECT * FROM a CROSS JOIN b
  - type: SELF JOIN
    description: A table joined with itself. Useful for hierarchical data.
    syntax: SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id
  - type: NATURAL JOIN
    description: Joins on all columns with the same name in both tables; convenient but fragile when schemas change.
    syntax: SELECT * FROM a NATURAL JOIN b
  - type: SEMI JOIN
    description: Returns rows from the left table that have at least one match on the right, without duplicating them; written with EXISTS or IN.
    syntax: SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)
  - type: ANTI JOIN
    description: Returns rows from the left table that have no match on the right; written with NOT EXISTS or LEFT JOIN ... IS NULL.
    syntax: SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)
  - type: LATERAL JOIN
    description: Lets a subquery on the right refer to columns of tables on the left, evaluated per row.
    syntax: SELECT * FROM a, LATERAL (SELECT * FROM b WHERE b.a_id = a.id ORDER BY b.ts DESC LIMIT 1) x
  - type: JOIN ... USING
    description: Shorthand for an equality join on one or more columns that share a name, returning each shared column once.
    syntax: SELECT * FROM a JOIN b USING (id)
  - type: EXPLICIT JOIN CONDITION
    description: Qualify both column names to make the relationship clear. Missing or incomplete keys can multiply rows.
    syntax: SELECT a.id, b.amount FROM a JOIN b ON b.a_id = a.id AND b.tenant_id = a.tenant_id
  - type: LEFT JOIN WITH MATCH FILTER
    description: Put a right-side eligibility filter in ON to preserve unmatched left rows. A WHERE predicate on that side can remove them.
    syntax: SELECT a.id, b.amount FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'paid'
  - type: JOIN WITH PRE-AGGREGATION
    description: Aggregate a many-side to the intended grain before joining when the question needs one row per left entity.
    syntax: SELECT a.id, b.total FROM a LEFT JOIN (SELECT a_id, SUM(amount) AS total FROM b GROUP BY a_id) b ON b.a_id = a.id
  - type: EXISTS FOR MEMBERSHIP
    description: Use a membership test when no right-side columns are needed. This avoids accidental duplication from several matches.
    syntax: SELECT a.id FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)