Basic Queries (SELECT)

Basic SELECT Standard

Retrieve, filter, sort, and paginate data from a single table.

sql

SELECT id, name, created_at
FROM users
WHERE status = 'active'
  AND age >= 18
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

SELECT DISTINCT Standard

Return only distinct (different) values.

sql

-- Get unique countries
SELECT DISTINCT country
FROM customers;

-- Count unique combinations
SELECT COUNT(DISTINCT country)
FROM customers;

Filtering Operators Standard

Common operators used in the WHERE clause.

sql

SELECT * FROM products WHERE:
-- IN list of values
category IN ('Electronics', 'Books')

-- Range inclusive
price BETWEEN 10 AND 50

-- Pattern matching (% = any chars)
name LIKE 'Pro%'
name ILIKE 'pro%' -- Postgres CI

-- Null checks
description IS NOT NULL

JOINs

INNER JOIN Standard

Returns records that have matching values in both tables.

sql

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.id;

Often just written as JOIN.

LEFT / RIGHT JOIN Standard

Returns all records from the left (or right) table, and matched records from the other. Missing matches are NULL.

sql

-- Find customers with NO orders
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
WHERE o.id IS NULL;

FULL OUTER JOIN Standard

Returns all records when there is a match in either left or right table.

sql

SELECT
  COALESCE(a.date, b.date) AS date,
  a.sales, b.costs
FROM daily_sales a
FULL OUTER JOIN daily_costs b
  ON a.date = b.date;

MySQL does not support FULL OUTER JOIN natively (requires UNION of LEFT and RIGHT joins).

Aggregation & GROUP BY

Aggregations Standard

Group rows sharing a property and apply aggregate functions.

sql

SELECT
  department_id,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary,
  MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

HAVING Clause Standard

Filter results after aggregations are applied. (WHERE filters before).

sql

SELECT category, SUM(sales) AS total
FROM products
GROUP BY category
-- Only show categories over 10k
HAVING SUM(sales) > 10000;

Subqueries & CTEs

Basic Subqueries Standard

Queries nested inside another query.

sql

-- Subquery in WHERE
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

-- Subquery in FROM (Derived Table)
SELECT dept, MAX(salary)
FROM (
  SELECT e.salary, d.name AS dept
  FROM employees e JOIN depts d ON...
) AS sub_query
GROUP BY dept;

CTEs (WITH clause) Modern

Common Table Expressions make complex queries more readable than nested subqueries.

sql

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total
  FROM orders
  GROUP BY region
),
top_regions AS (
  SELECT region FROM regional_sales
  WHERE total > 100000
)
SELECT * FROM top_regions;

Window Functions

ROW_NUMBER() & RANK() Analytics

Perform calculations across a set of table rows related to the current row, without grouping them into a single output row.

sql

SELECT
  name,
  department,
  salary,
  -- Rank employees by salary within dept
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) as dept_rank,
  -- Unique row number even if tied
  ROW_NUMBER() OVER (
    ORDER BY salary DESC
  ) as global_row_id
FROM employees;

LAG() & LEAD() Analytics

Access data from a previous or subsequent row in the same result set. Great for calculating week-over-week changes.

sql

SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (
    ORDER BY date
  ) AS prev_day_revenue,
  revenue - LAG(revenue, 1) OVER (
    ORDER BY date
  ) AS daily_change
FROM daily_sales;

PostgreSQL vs MySQL Specifics

Common Differences Dialects

Operation	PostgreSQL	MySQL
String Concat	`'a' \|\| 'b'`	`CONCAT('a','b')`
Current Date	`CURRENT_DATE`	`CURDATE()`
Cast to Text	`col::text`	`CAST(col AS CHAR)`
If Null	`COALESCE(col, 0)`	`IFNULL(col, 0)`

Postgres Superpowers PostgreSQL

Useful features specific to Postgres.

sql

-- Return inserted data instantly
INSERT INTO users (name)
VALUES ('Alice')
RETURNING id, name;

-- Upsert (Insert or Update)
INSERT INTO metrics (date, hits)
VALUES (CURRENT_DATE, 1)
ON CONFLICT (date)
DO UPDATE SET hits = metrics.hits + 1;

SQL Quick Reference

Basic Queries (SELECT)

JOINs

Aggregation & GROUP BY

Subqueries & CTEs

Window Functions

PostgreSQL vs MySQL Specifics

Why Use This SQL Cheat Sheet?

Essential SQL Concepts

PostgreSQL vs MySQL Considerations

SQL Quick Reference

Basic Queries (SELECT)

JOINs

Aggregation & GROUP BY

Subqueries & CTEs

Window Functions

PostgreSQL vs MySQL Specifics

Why Use This SQL Cheat Sheet?

Essential SQL Concepts

PostgreSQL vs MySQL Considerations

🔗 Related Tools

Related Resources