SQL Quick Reference Cheat Sheet

SELECT Basics

Select all columns from a table SELECT * FROM users;

Select specific columns SELECT name, email FROM users;

Filter rows with a condition SELECT * FROM users WHERE age > 25;

Multiple conditions SELECT * FROM users WHERE age > 25 AND active = true;

Pattern matching SELECT * FROM users WHERE name LIKE 'J%';

Check for NULL values SELECT * FROM users WHERE email IS NOT NULL;

Match against a list SELECT * FROM users WHERE role IN ('admin', 'editor');

Range filter SELECT * FROM users WHERE age BETWEEN 18 AND 65;

Remove duplicate rows SELECT DISTINCT city FROM users;

Sort results ascending SELECT * FROM users ORDER BY name ASC;

Sort results descending SELECT * FROM users ORDER BY created_at DESC;

Limit number of results SELECT * FROM users LIMIT 10;

Pagination with offset SELECT * FROM users LIMIT 10 OFFSET 20;

Column alias SELECT name AS full_name FROM users;

JOINs

Returns rows with matching values in both tables

SELECT * FROM orders
  INNER JOIN users ON orders.user_id = users.id;

Returns all rows from left table + matched rows from right

SELECT * FROM users
  LEFT JOIN orders ON users.id = orders.user_id;

Returns all rows from right table + matched rows from left

SELECT * FROM orders
  RIGHT JOIN users ON orders.user_id = users.id;

Returns all rows when there is a match in either table

SELECT * FROM users
  FULL OUTER JOIN orders ON users.id = orders.user_id;

Returns the Cartesian product of both tables

SELECT * FROM sizes
  CROSS JOIN colors;

Join a table with itself

SELECT e.name, m.name AS manager
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id;

Aggregation

Count the number of rows SELECT COUNT(*) FROM users;

Count non-NULL values SELECT COUNT(email) FROM users;

Sum of a column SELECT SUM(amount) FROM orders;

Average of a column SELECT AVG(price) FROM products;

Minimum value SELECT MIN(price) FROM products;

Maximum value SELECT MAX(price) FROM products;

Group results by a column

SELECT city, COUNT(*) FROM users
  GROUP BY city;

Filter grouped results

SELECT city, COUNT(*) AS cnt FROM users
  GROUP BY city HAVING cnt > 10;

Aggregate with multiple groups

SELECT country, city, COUNT(*)
  FROM users GROUP BY country, city;

Subqueries & CTEs

Subquery in WHERE clause

SELECT * FROM users
  WHERE id IN (SELECT user_id FROM orders);

Subquery as derived table

SELECT avg_total FROM (
  SELECT AVG(total) AS avg_total
  FROM orders
) AS sub;

Check if subquery returns rows

SELECT * FROM users u
  WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
  );

Common Table Expression (CTE)

WITH active_users AS (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users;

Multiple CTEs

WITH
  cte1 AS (SELECT ...),
  cte2 AS (SELECT ...)
SELECT * FROM cte1 JOIN cte2 ...;

Recursive CTE

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Data Modification

Insert a single row

INSERT INTO users (name, email)
  VALUES ('Alice', '[email protected]');

Insert multiple rows

INSERT INTO users (name, email) VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]');

Insert from a select

INSERT INTO archive
  SELECT * FROM orders
  WHERE created_at < '2024-01-01';

Update rows

UPDATE users SET name = 'Bob'
  WHERE id = 1;

Update with join (PostgreSQL)

UPDATE orders SET status = 'shipped'
  FROM users
  WHERE orders.user_id = users.id
  AND users.premium = true;

Delete rows DELETE FROM users WHERE active = false;

Upsert (PostgreSQL)

INSERT INTO users (email, name)
  VALUES ('[email protected]', 'Alice')
  ON CONFLICT (email)
  DO UPDATE SET name = EXCLUDED.name;

Upsert (MySQL)

INSERT INTO users (email, name)
  VALUES ('[email protected]', 'Alice')
  ON DUPLICATE KEY UPDATE
  name = VALUES(name);

Truncate a table (fast delete all) TRUNCATE TABLE logs;

Constraints & Indexes

Primary key

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Foreign key

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id)
);

Unique constraint

ALTER TABLE users
  ADD CONSTRAINT uq_email UNIQUE (email);

NOT NULL constraint

ALTER TABLE users
  ALTER COLUMN name SET NOT NULL;

Check constraint

ALTER TABLE users
  ADD CONSTRAINT chk_age CHECK (age >= 0);

Create an index

CREATE INDEX idx_users_email
  ON users (email);

Create a unique index

CREATE UNIQUE INDEX idx_users_email
  ON users (email);

Composite index

CREATE INDEX idx_name_city
  ON users (last_name, city);

Drop an index DROP INDEX idx_users_email;