SQL Join Types
Reference for all SQL JOIN types and their use cases.
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 it when
- You need the exact behavior of a join type or a syntax example.
- You are choosing between joining and filtering with EXISTS.
Watch out for
- 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.
The data
15 rows
| 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) |
Fields
The shape of this dataset, read from the data itself: every column, its type and a real value. Agents can use this to plan a query without fetching the whole document.
| 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 |
Fetch the same bytes
The static files contain the same reference rows as the API, with no API rate limit. Live metadata, such as view counts, may differ. Use the API for content negotiation; use the static files for a cacheable snapshot.
curl "https://yjtoon.com/api/dataset/sql-join-types?format=toon"
const res = await fetch( "https://yjtoon.com/static-data/dataset/sql-join-types.toon" ); const toon = await res.text();
Rate limit: 120 requests per minute per IP, no key and no signup. API reference →
Sources and editorial notes
Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses.
Editorial review: . Source documents keep their own licenses.