api: YAML JSON TOON Database
version: 1.0.0
format: toon
dataset:
  id: 2492
  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[2]: You need the exact behavior of a join type or a syntax example.,You are choosing between joining and filtering with EXISTS.
  caveats[2]: "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[5]: sql-query-types,database-comparison,acid-vs-base,sql-window-functions,sql-index-patterns
  sources[1]{title,url}:
    PostgreSQL table expressions,"https://www.postgresql.org/docs/current/queries-table-expressions.html"
  reading[1]: 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[3]{section,field,type,example}:
    data,type,string,INNER JOIN
    data,description,string,Returns rows where there is a match in both tab…
    data,syntax,string,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: 38
  created_at: 1792832553
  updated_at: 1791799373
data[15]{type,description,syntax}:
  INNER JOIN,Returns rows where there is a match in both tables.,SELECT * FROM a INNER JOIN b ON a.id = b.a_id
  LEFT JOIN,Returns all rows from the left table and matched rows from the right.,SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
  RIGHT JOIN,Returns all rows from the right table and matched rows from the left.,SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id
  FULL OUTER JOIN,"Returns all rows from both tables, with NULLs for non-matches.",SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id
  CROSS JOIN,Returns the Cartesian product of both tables.,SELECT * FROM a CROSS JOIN b
  SELF JOIN,A table joined with itself. Useful for hierarchical data.,SELECT * FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id
  NATURAL JOIN,Joins on all columns with the same name in both tables; convenient but fragile when schemas change.,SELECT * FROM a NATURAL JOIN b
  SEMI JOIN,"Returns rows from the left table that have at least one match on the right, without duplicating them; written with EXISTS or IN.",SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)
  ANTI JOIN,Returns rows from the left table that have no match on the right; written with NOT EXISTS or LEFT JOIN ... IS NULL.,SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)
  LATERAL JOIN,"Lets a subquery on the right refer to columns of tables on the left, evaluated per row.","SELECT * FROM a, LATERAL (SELECT * FROM b WHERE b.a_id = a.id ORDER BY b.ts DESC LIMIT 1) x"
  JOIN ... USING,"Shorthand for an equality join on one or more columns that share a name, returning each shared column once.",SELECT * FROM a JOIN b USING (id)
  EXPLICIT JOIN CONDITION,Qualify both column names to make the relationship clear. Missing or incomplete keys can multiply rows.,"SELECT a.id, b.amount FROM a JOIN b ON b.a_id = a.id AND b.tenant_id = a.tenant_id"
  LEFT JOIN WITH MATCH FILTER,Put a right-side eligibility filter in ON to preserve unmatched left rows. A WHERE predicate on that side can remove them.,"SELECT a.id, b.amount FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'paid'"
  JOIN WITH PRE-AGGREGATION,Aggregate a many-side to the intended grain before joining when the question needs one row per left entity.,"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"
  EXISTS FOR MEMBERSHIP,Use a membership test when no right-side columns are needed. This avoids accidental duplication from several matches.,SELECT a.id FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id)