SQL Cheatsheet

Essential SQL commands, with a focus on modern PostgreSQL features. From basic queries to advanced data types.

Basics

Key / Code	Description
SELECT * FROM users	Select all columns from a table.
WHERE age > 18	Filter rows.
ORDER BY created_at DESC	Sort results.
LIMIT 10 OFFSET 5	Pagination.

Joins

Key / Code	Description
INNER JOIN	Returns records that have matching values in both tables.
LEFT JOIN	Returns all records from the left table, and the matched records from the right table.
RIGHT JOIN	Returns all records from the right table, and the matched records from the left table.
FULL OUTER JOIN	Returns all records when there is a match in either left or right table.

PostgreSQL JSONB

Modern Postgres allows semi-structured data.

-- Select a value from a JSON column
SELECT data->>'name' FROM events;

-- Filter by JSON value
SELECT * FROM events WHERE data->>'status' = 'completed';

Common Table Expressions (CTE)

Use `WITH` to create temporary result sets. Makes complex queries readable.

WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM orders
    GROUP BY region
)
SELECT * FROM regional_sales WHERE total_sales > 10000;

Knowledge is power.