{
    "api": "YAML JSON TOON Database",
    "version": "1.0.0",
    "format": "json",
    "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": [
            "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\u2026"
            },
            {
                "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": 37,
        "created_at": 1792832553,
        "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)"
        }
    ]
}