LLM Best Practices

SQL Joins Cheatsheet

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

The join type decides what happens to rows with no match, and a wrong choice silently changes row counts. Examples use Postgres syntax. Index advice for join keys is in postgres-indexes.

Join types

Choose the join by which side’s rows you must keep.

JoinRows returnedUse for
INNER JOINMatches on both sides.Both entities must exist.
LEFT JOINAll left rows; NULL for no right match.Optional related rows.
RIGHT JOINAll right rows.Rarely; swap the tables and use LEFT JOIN.
FULL OUTER JOINAll rows from both sides.Reconciling two sources.
CROSS JOINEvery left row with every right row.Generating combinations; no ON.
JOIN LATERALRight side is a subquery that sees each left row.Top N per group.
-- LEFT JOIN: users with and without orders
SELECT u.name, count(o.id) AS orders
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;
 
-- Top 3 orders per user
SELECT u.name, r.created_at
FROM users u
CROSS JOIN LATERAL (SELECT created_at FROM orders o
                    WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3) r;
 
-- Self join: employee and manager
SELECT e.name, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;

Pick the row set

Prefer EXISTS and NOT EXISTS for presence checks.

WantWrite
Rows matching on both sidesINNER JOIN ... ON ...
Left rows with no right matchLEFT JOIN ... WHERE r.id IS NULL, or WHERE NOT EXISTS (...)
Left rows that have a right match, once eachWHERE EXISTS (SELECT 1 FROM r WHERE r.l_id = l.id)
Rows in only one of two sourcesFULL OUTER JOIN ... WHERE l.id IS NULL OR r.id IS NULL

Bugs that return wrong counts

Check join keys and filters first when counts look wrong.

BugSymptomFix
WHERE a.id = NULLNever matches.IS NULL or IS NOT NULL.
Filtering the right table of a LEFT JOIN in WHEREBecomes an inner join; unmatched rows vanish.Move the condition into ON.
One-to-many join before aggregatingSums and counts inflate.Aggregate the many side in a subquery or CTE first.
Comma join (FROM a, b) with a missing conditionCartesian product.Use explicit JOIN ... ON.
NOT IN (subquery) where the subquery returns a NULLNo rows at all.Use NOT EXISTS.
SELECT * over joined tablesDuplicate column names.List columns and alias tables.
No index on the foreign keySlow joins; Postgres does not index foreign keys automatically.Add a B-tree index on the referencing column.
-- Wrong: drops users with no paid orders
SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid';
-- Right: keep every user, join only paid orders
SELECT u.name FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

Gotchas

Check engine differences when porting queries.

GotchaFix
MySQL has no FULL OUTER JOIN.Union a LEFT JOIN and a RIGHT JOIN.
LATERAL is standard SQL, not Postgres-only (MySQL 8.0.14 and later accept it).Check the engine version before porting.
USING (col) merges the join column in SELECT *.List columns explicitly when ORMs expect both.

Backlinks