SQL Cheat Sheet

SELECT Queries

Query	Description
`SELECT * FROM users`	Select all columns
`SELECT name, email FROM users`	Select specific columns
`SELECT DISTINCT city FROM users`	Select unique values only
`SELECT name AS full_name FROM users`	Column alias
`SELECT , price qty AS total FROM orders`	Computed column
`SELECT COUNT(*) FROM users`	Count all rows

-- Query execution order
FROM      -- 1. Source tables
JOIN      -- 2. Join tables
WHERE     -- 3. Filter rows
GROUP BY  -- 4. Group rows
HAVING    -- 5. Filter groups
SELECT    -- 6. Select columns
DISTINCT  -- 7. Remove duplicates
ORDER BY  -- 8. Sort results
LIMIT     -- 9. Limit rows

Filtering & Sorting

Clause	Description
`WHERE age > 18`	Filter rows by condition
`WHERE name = 'Alice'`	Exact match (strings use single quotes)
`WHERE age BETWEEN 18 AND 30`	Range (inclusive)
`WHERE city IN ('NYC', 'LA', 'SF')`	Match any value in list
`WHERE city NOT IN ('NYC')`	Exclude values in list
`WHERE name LIKE 'A%'`	Pattern match (% = any chars)
`WHERE name LIKE '_ob'`	Pattern match (_ = single char)
`WHERE name ILIKE '%alice%'`	Case-insensitive LIKE (PostgreSQL)
`WHERE email IS NULL`	Check for NULL
`WHERE email IS NOT NULL`	Check for non-NULL
`WHERE age > 18 AND city = 'NYC'`	Multiple conditions (AND)
`WHERE age > 65 OR age < 18`	Either condition (OR)
`WHERE NOT active`	Negate condition
`ORDER BY name ASC`	Sort ascending (default)
`ORDER BY age DESC`	Sort descending
`ORDER BY city, name`	Sort by multiple columns
`LIMIT 10`	Return first 10 rows
`LIMIT 10 OFFSET 20`	Skip 20, return next 10 (pagination)

JOINs

Type	Description
`INNER JOIN`	Only matching rows from both tables
`LEFT JOIN`	All rows from left + matching from right (NULL if no match)
`RIGHT JOIN`	All rows from right + matching from left
`FULL OUTER JOIN`	All rows from both tables (NULL where no match)
`CROSS JOIN`	Cartesian product (every combination)
`SELF JOIN`	Table joined with itself

-- INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (keep all users, even without orders)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Multiple JOINs
SELECT u.name, o.id, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

-- Self JOIN (find employees and their managers)
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Aggregation

Function	Description
`COUNT(*)`	Count all rows
`COUNT(column)`	Count non-NULL values
`COUNT(DISTINCT column)`	Count unique non-NULL values
`SUM(column)`	Sum of values
`AVG(column)`	Average value
`MIN(column)`	Minimum value
`MAX(column)`	Maximum value

-- GROUP BY with aggregate
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- HAVING (filter groups, not rows)
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

-- GROUP BY with multiple aggregates
SELECT department,
  COUNT(*) AS employees,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Tip: WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You can't use aggregate functions in WHERE.

Subqueries & CTEs

-- Subquery in WHERE
SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE total > 100
);

-- Subquery in FROM (derived table)
SELECT avg_total
FROM (
  SELECT user_id, AVG(total) AS avg_total
  FROM orders
  GROUP BY user_id
) AS user_avgs
WHERE avg_total > 50;

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

-- CTE (Common Table Expression)
WITH top_customers AS (
  SELECT user_id, SUM(total) AS lifetime
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, tc.lifetime
FROM users u
JOIN top_customers tc ON u.id = tc.user_id;

-- Window functions
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

INSERT / UPDATE / DELETE

-- INSERT single row
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

-- INSERT multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

-- INSERT from SELECT
INSERT INTO archive (name, email)
SELECT name, email FROM users WHERE active = false;

-- UPDATE
UPDATE users
SET email = 'new@example.com', updated_at = NOW()
WHERE id = 1;

-- UPDATE with JOIN (PostgreSQL)
UPDATE orders o
SET status = 'vip'
FROM users u
WHERE o.user_id = u.id AND u.tier = 'gold';

-- DELETE
DELETE FROM users WHERE id = 1;

-- DELETE all rows (keep table structure)
DELETE FROM logs;
TRUNCATE TABLE logs;  -- faster, resets auto-increment

-- UPSERT (INSERT or UPDATE on conflict)
-- PostgreSQL:
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- MySQL:
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

Warning: Always use WHERE with UPDATE and DELETE. Without it, you'll modify or delete every row in the table.

Table Management

-- CREATE TABLE
CREATE TABLE users (
  id        SERIAL PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  email     VARCHAR(255) UNIQUE NOT NULL,
  age       INTEGER,
  active    BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ALTER TABLE
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE old_name RENAME TO new_name;

-- DROP TABLE
DROP TABLE users;
DROP TABLE IF EXISTS users;

Common Data Types

Type	Description
`INTEGER / INT`	Whole numbers
`BIGINT`	Large whole numbers
`SERIAL`	Auto-incrementing integer (PostgreSQL)
`DECIMAL(p,s)`	Exact decimal (precision, scale)
`FLOAT / REAL`	Floating point number
`VARCHAR(n)`	Variable-length string (max n chars)
`TEXT`	Unlimited-length string
`BOOLEAN`	True / false
`DATE`	Date only (YYYY-MM-DD)
`TIMESTAMP`	Date and time
`JSON / JSONB`	JSON data (JSONB is binary, faster)
`UUID`	Universally unique identifier

Constraints & Indexes

Constraint	Description
`PRIMARY KEY`	Unique identifier for each row
`FOREIGN KEY`	References a primary key in another table
`UNIQUE`	All values must be different
`NOT NULL`	Column cannot contain NULL
`DEFAULT value`	Default value if none provided
`CHECK (condition)`	Values must satisfy condition

-- Foreign key
CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  user_id  INTEGER NOT NULL REFERENCES users(id),
  total    DECIMAL(10,2)
);

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Drop index
DROP INDEX idx_users_email;

Common Functions

Function	Description
`COALESCE(a, b, c)`	First non-NULL value
`NULLIF(a, b)`	NULL if a = b, otherwise a
`CASE WHEN ... THEN ... ELSE ... END`	Conditional expression
`CAST(x AS type)`	Convert data type
`UPPER(str) / LOWER(str)`	Change case
`TRIM(str)`	Remove whitespace
`LENGTH(str)`	String length
`SUBSTRING(str, pos, len)`	Extract substring
`CONCAT(a, b)`	Concatenate strings
`REPLACE(str, old, new)`	Replace in string
`NOW()`	Current date and time
`CURRENT_DATE`	Current date
`EXTRACT(YEAR FROM date)`	Extract part of date
`DATE_TRUNC('month', date)`	Truncate to month (PostgreSQL)
`ABS(x)`	Absolute value
`ROUND(x, n)`	Round to n decimals
`CEIL(x) / FLOOR(x)`	Round up / down

-- CASE expression
SELECT name,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS category
FROM users;

Useful Patterns

-- Pagination
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;  -- page 3 (20 per page)

-- Find duplicates
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Top N per group (window function)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS rn
  FROM employees
) t WHERE rn <= 3;

-- UNION (combine results, remove duplicates)
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- UNION ALL (keep duplicates, faster)
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;