Database ReferenceANSI SQL & PostgreSQL / MySQL

SQL Joins & Query Syntax Cheat Sheet

Visual reference and copy-paste syntax for SQL joins, Venn diagrams, CTEs, and window aggregation functions.

Visual SQL Join Types

INNER JOINOverlap only (A ∩ B)

Returns only records where there is a match in BOTH Table A and Table B.

SELECT A.id, A.name, B.order_id
FROM Customers A
INNER JOIN Orders B ON A.id = B.customer_id;

LEFT JOIN (LEFT OUTER JOIN)All of A + matching B

Returns ALL records from Table A, plus matched records from Table B. Nulls if no match.

SELECT A.id, A.name, B.order_id
FROM Customers A
LEFT JOIN Orders B ON A.id = B.customer_id;

RIGHT JOIN (RIGHT OUTER JOIN)All of B + matching A

Returns ALL records from Table B, plus matched records from Table A. Nulls if no match.

SELECT A.id, B.order_id, B.amount
FROM Customers A
RIGHT JOIN Orders B ON A.id = B.customer_id;

FULL OUTER JOINEverything (A ∪ B)

Returns ALL records when there is a match in either left (A) or right (B) table.

SELECT A.name, B.order_id
FROM Customers A
FULL OUTER JOIN Orders B ON A.id = B.customer_id;

LEFT EXCLUSIVE JOIN (A only without B)A only (A - B)

Returns records in Table A that have NO match in Table B.

SELECT A.id, A.name
FROM Customers A
LEFT JOIN Orders B ON A.id = B.customer_id
WHERE B.customer_id IS NULL;

Advanced SQL Query Patterns

Aggregate Functions & GROUP BY

Groups employees by department, filters departments with >5 people, orders by salary.

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_sal
FROM Employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_sal DESC;

Common Table Expressions (CTE)

Improves query readability and modularizes complex subqueries.

WITH HighEarners AS (
  SELECT id, name, salary
  FROM Employees
  WHERE salary > 100000
)
SELECT * FROM HighEarners WHERE id IN (SELECT manager_id FROM Teams);

Window Functions (ROW_NUMBER & DENSE_RANK)

Assigns ranks to employees within their department partition without collapsing rows.

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM Employees;