Database ReferenceANSI SQL & PostgreSQL / MySQL

SQL Joins & Query Syntax Cheat Sheet

Visual syntax reference for relational SQL joins, Venn diagrams, CTEs, and window aggregation functions. Reference tables load statically without tracking cookies.

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;