SQL Cheat Sheet: Complete Reference Guide for 2026

SQL (Structured Query Language) is the standard language for working with relational databases. Whether you're learning SQL basics or need a quick reference for complex queries, this comprehensive cheat sheet covers everything from simple SELECT statements to advanced window functions.

Basic SELECT Queries

Simple SELECT

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Select with alias
SELECT name AS full_name, email AS contact_email FROM users;

WHERE Clause (Filtering)

-- Exact match
SELECT * FROM users WHERE country = 'USA';

-- Not equal
SELECT * FROM users WHERE country != 'USA';
SELECT * FROM users WHERE country <> 'USA';

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 100;
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price <= 50;

-- Multiple conditions
SELECT * FROM users WHERE country = 'USA' AND age >= 18;
SELECT * FROM users WHERE country = 'USA' OR country = 'Canada';

-- IN operator
SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');

-- NOT IN
SELECT * FROM users WHERE country NOT IN ('USA', 'Canada');

-- BETWEEN
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

Pattern Matching (LIKE)

-- % matches any number of characters
SELECT * FROM users WHERE name LIKE 'John%';      -- Starts with John
SELECT * FROM users WHERE name LIKE '%Smith';     -- Ends with Smith
SELECT * FROM users WHERE name LIKE '%John%';     -- Contains John

-- _ matches exactly one character
SELECT * FROM users WHERE name LIKE 'J_hn';       -- J?hn (John, Jahn, etc.)

-- Case insensitive (depends on database)
SELECT * FROM users WHERE name ILIKE '%john%';    -- PostgreSQL
SELECT * FROM users WHERE LOWER(name) LIKE '%john%';  -- Any DB

DISTINCT (Unique Values)

-- Get unique values
SELECT DISTINCT country FROM users;

-- Count unique values
SELECT COUNT(DISTINCT country) FROM users;

LIMIT / TOP (Limit Results)

-- MySQL, PostgreSQL
SELECT * FROM users LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM users;

-- With offset (pagination)
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Skip 20, show next 10

Sorting (ORDER BY)

-- Ascending (default)
SELECT * FROM users ORDER BY name;
SELECT * FROM users ORDER BY name ASC;

-- Descending
SELECT * FROM users ORDER BY created_at DESC;

-- Multiple columns
SELECT * FROM users ORDER BY country ASC, name ASC;

-- By column number (not recommended)
SELECT name, email FROM users ORDER BY 1;

Aggregate Functions

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(id) FROM users;

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

-- Sum
SELECT SUM(price) FROM orders;

-- Average
SELECT AVG(price) FROM products;

-- Min / Max
SELECT MIN(price) FROM products;
SELECT MAX(price) FROM products;

-- Multiple aggregates
SELECT 
  COUNT(*) AS total_users,
  AVG(age) AS avg_age,
  MIN(age) AS youngest,
  MAX(age) AS oldest
FROM users;

GROUP BY

-- Group by single column
SELECT country, COUNT(*) AS user_count 
FROM users 
GROUP BY country;

-- Group by multiple columns
SELECT country, city, COUNT(*) AS user_count 
FROM users 
GROUP BY country, city;

-- With aggregate functions
SELECT 
  category,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM products 
GROUP BY category;

HAVING (Filter Groups)

-- Filter after grouping (WHERE filters before grouping)
SELECT country, COUNT(*) AS user_count 
FROM users 
GROUP BY country 
HAVING COUNT(*) > 100;

-- Multiple conditions
SELECT 
  category,
  AVG(price) AS avg_price
FROM products 
GROUP BY category 
HAVING AVG(price) > 50 AND COUNT(*) >= 10;

JOINS

INNER JOIN

Returns only rows with matches in both tables.

SELECT 
  users.name,
  orders.order_date,
  orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- Short syntax
SELECT u.name, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;  -- INNER is default

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from left table, matched rows from right (NULL if no match).

-- Get all users and their orders (including users with no orders)
SELECT u.name, o.order_date, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Find users with no orders
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from right table, matched rows from left.

SELECT u.name, o.order_date
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

FULL OUTER JOIN

Returns all rows from both tables (NULL where no match).

SELECT u.name, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

CROSS JOIN

Returns Cartesian product (every combination).

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

SELF JOIN

-- Find employees and their managers
SELECT 
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Multiple JOINS

SELECT 
  u.name AS customer,
  o.order_date,
  p.product_name,
  oi.quantity,
  oi.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

Subqueries

Subquery in WHERE

-- Users who have placed orders
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Products more expensive than average
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

Subquery in FROM

-- Average order value by user
SELECT 
  u.name,
  avg_orders.avg_total
FROM users u
JOIN (
  SELECT user_id, AVG(total) AS avg_total
  FROM orders
  GROUP BY user_id
) avg_orders ON u.id = avg_orders.user_id;

EXISTS

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

-- Users who have NOT placed orders
SELECT name FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

INSERT Data

-- Insert single row
INSERT INTO users (name, email, age) 
VALUES ('John Doe', '[email protected]', 30);

-- Insert multiple rows
INSERT INTO users (name, email, age) 
VALUES 
  ('Jane Smith', '[email protected]', 25),
  ('Bob Johnson', '[email protected]', 35);

-- Insert from SELECT
INSERT INTO archive_users (name, email)
SELECT name, email FROM users WHERE active = 0;

-- Insert with default values
INSERT INTO users (name) VALUES ('John');  -- Other columns get defaults

UPDATE Data

-- Update single column
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- Update multiple columns
UPDATE users 
SET email = '[email protected]', age = 31 
WHERE id = 1;

-- Update with calculation
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

-- Update with subquery
UPDATE users 
SET vip = 1 
WHERE id IN (
  SELECT user_id FROM orders GROUP BY user_id HAVING SUM(total) > 1000
);

-- Update all rows (dangerous!)
UPDATE users SET active = 1;

DELETE Data

-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM users WHERE created_at < '2020-01-01';

-- Delete with subquery
DELETE FROM users 
WHERE id IN (
  SELECT user_id FROM orders WHERE status = 'cancelled'
);

-- Delete all rows (dangerous!)
DELETE FROM users;

-- Faster way to delete all (cannot be rolled back)
TRUNCATE TABLE users;

CREATE Tables

-- Basic table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- With foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- With constraints
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  stock INT DEFAULT 0 CHECK (stock >= 0),
  category VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER Tables

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Change column type
ALTER TABLE users MODIFY COLUMN age SMALLINT;

-- Add constraint
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 18);

-- Add foreign key
ALTER TABLE orders 
ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Rename table
ALTER TABLE users RENAME TO customers;

DROP Tables

-- Drop table
DROP TABLE users;

-- Drop only if exists
DROP TABLE IF EXISTS users;

-- Drop multiple tables
DROP TABLE users, orders, products;

Indexes

-- Create index
CREATE INDEX idx_email ON users(email);

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

-- Composite index
CREATE INDEX idx_name_email ON users(name, email);

-- Drop index
DROP INDEX idx_email ON users;  -- MySQL
DROP INDEX idx_email;           -- PostgreSQL

-- Show indexes
SHOW INDEX FROM users;  -- MySQL
\d users                -- PostgreSQL

UNION

-- Combine results (removes duplicates)
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- Include duplicates
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

-- With ORDER BY (must be on final result)
SELECT name, 'customer' AS type FROM customers
UNION
SELECT name, 'supplier' AS type FROM suppliers
ORDER BY name;

Common Table Expressions (CTEs)

-- Simple CTE
WITH high_spenders AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, hs.total_spent
FROM users u
JOIN high_spenders hs ON u.id = hs.user_id;

-- Multiple CTEs
WITH 
  user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
  ),
  user_total AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
  )
SELECT 
  u.name,
  uo.order_count,
  ut.total_spent
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
JOIN user_total ut ON u.id = ut.user_id;

Window Functions

ROW_NUMBER, RANK, DENSE_RANK

-- Row number (unique numbers)
SELECT 
  name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM products;

-- Rank (same values get same rank, gaps after)
SELECT 
  name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank
FROM products;

-- Dense rank (same values get same rank, no gaps)
SELECT 
  name,
  price,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

PARTITION BY

-- Rank within each category
SELECT 
  name,
  category,
  price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;

-- Average price by category (for each row)
SELECT 
  name,
  category,
  price,
  AVG(price) OVER (PARTITION BY category) AS avg_category_price
FROM products;

Aggregate Window Functions

-- Running total
SELECT 
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average (last 3 rows)
SELECT 
  order_date,
  total,
  AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM orders;

String Functions

-- Concatenate
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Substring
SELECT SUBSTRING(name, 1, 3) FROM users;  -- First 3 characters

-- Uppercase / Lowercase
SELECT UPPER(name) FROM users;
SELECT LOWER(email) FROM users;

-- Trim whitespace
SELECT TRIM(name) FROM users;
SELECT LTRIM(name) FROM users;  -- Left only
SELECT RTRIM(name) FROM users;  -- Right only

-- Replace
SELECT REPLACE(email, '@', ' [at] ') FROM users;

-- Length
SELECT LENGTH(name) FROM users;

-- Position
SELECT POSITION('@' IN email) FROM users;  -- PostgreSQL
SELECT INSTR(email, '@') FROM users;       -- MySQL

Date Functions

-- Current date/time
SELECT NOW();           -- Current timestamp
SELECT CURDATE();       -- Current date
SELECT CURTIME();       -- Current time

-- Extract parts
SELECT YEAR(order_date) FROM orders;
SELECT MONTH(order_date) FROM orders;
SELECT DAY(order_date) FROM orders;

-- Date arithmetic
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;   -- MySQL
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH) FROM orders;
SELECT order_date + INTERVAL '7 days' FROM orders;         -- PostgreSQL

-- Date difference
SELECT DATEDIFF(NOW(), order_date) AS days_ago FROM orders;

-- Format date
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders;  -- MySQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders;    -- PostgreSQL

CASE Statement

-- Simple CASE
SELECT 
  name,
  age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

-- In WHERE clause
SELECT * FROM products
WHERE price > 
  CASE
    WHEN category = 'Electronics' THEN 1000
    WHEN category = 'Clothing' THEN 100
    ELSE 50
  END;

-- In UPDATE
UPDATE products
SET discount = 
  CASE
    WHEN stock > 100 THEN 0.20
    WHEN stock > 50 THEN 0.10
    ELSE 0.05
  END;

Transactions

-- Start transaction
START TRANSACTION;
-- or
BEGIN;

-- Make changes
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit changes
COMMIT;

-- Or rollback if error
ROLLBACK;

-- Savepoint
START TRANSACTION;
UPDATE users SET active = 1;
SAVEPOINT my_savepoint;
UPDATE users SET vip = 1;
ROLLBACK TO my_savepoint;  -- Undo last UPDATE only
COMMIT;

Views

-- Create view
CREATE VIEW active_users AS
SELECT id, name, email 
FROM users 
WHERE active = 1;

-- Use view
SELECT * FROM active_users;

-- Replace view
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, created_at
FROM users 
WHERE active = 1;

-- Drop view
DROP VIEW active_users;

Best Practices

• Always use WHERE in UPDATE/DELETE: Avoid accidental mass updates
• Use transactions: For multiple related operations
• Index frequently queried columns: Foreign keys, WHERE clause columns
• Avoid SELECT *: Specify columns you need
• Use EXPLAIN: Analyze query performance
• Normalize your database: Reduce data redundancy
• Use prepared statements: Prevent SQL injection
• Backup regularly: Always have a recovery plan

Quick Reference Table

Learning Resources

• PostgreSQL Documentation
• MySQL Documentation
• SQLZoo (Interactive Tutorials)
• DB Fiddle (Online SQL Editor)

Conclusion

SQL is the universal language of databases. Master these fundamentals and you'll be able to:

• Query any relational database
• Design efficient database schemas
• Write complex analytical queries
• Optimize database performance
• Work with MySQL, PostgreSQL, SQL Server, and more

Bookmark this cheat sheet and practice on real projects. SQL syntax becomes muscle memory with regular use. Start with simple SELECT queries and gradually master JOINs, subqueries, and window functions.

Related Tools & Posts

• SQL Formatter Tool
• Database Indexing Guide