SQL Query Cheat Sheet: Essential Queries and Patterns

 Each section includes the syntax, a practical example, and notes on database-specific differences where they matter. Bookmark this page and use it as your daily reference for writing SQL queries. 

 The foundation of every SQL query. `SELECT` specifies which columns to return, `FROM` specifies the table. 

-- Select all columns SELECT * FROM users; -- Select specific columns SELECT id, name, email FROM users; -- Select with alias SELECT u.id, u.name AS username FROM users AS u; -- Select distinct values SELECT DISTINCT status FROM orders; -- Select with LIMIT (MySQL/Postgres) SELECT * FROM products LIMIT 10; -- Select with TOP (SQL Server) SELECT TOP 10 * FROM products; -- Select with FETCH (standard SQL / Oracle) SELECT * FROM products OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; 

 Filter rows based on conditions. The WHERE clause is evaluated row-by-row before GROUP BY and aggregate functions. 

-- Comparison operators SELECT * FROM users WHERE age >= 18; SELECT * FROM orders WHERE total != 0; -- String matching SELECT * FROM users WHERE name LIKE 'A%'; -- starts with A SELECT * FROM users WHERE name LIKE '%son%'; -- contains 'son' SELECT * FROM users WHERE name LIKE '_oe'; -- exactly 3 chars, ends with 'oe' -- IN operator SELECT * FROM orders WHERE status IN ('shipped', 'delivered', 'pending'); -- BETWEEN SELECT * FROM products WHERE price BETWEEN 10 AND 50; -- NULL checks SELECT * FROM users WHERE email IS NULL; SELECT * FROM users WHERE email IS NOT NULL; -- Multiple conditions SELECT * FROM orders WHERE status = 'active' AND created_at >= '2024-01-01' AND (total > 100 OR priority = 'high'); -- Boolean expressions SELECT * FROM products WHERE NOT discontinued AND (category_id = 5 OR category_id = 8); 

 Combine rows from two or more tables based on related columns. Understanding JOINs is critical for working with normalized databases. 

 Returns rows where there is a match in **both** tables. 

SELECT u.name, o.id AS order_id, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id; -- Only returns users with at least one order 

 Returns all rows from the **left** table, with matching rows from the right table. Non-matching right-side columns are `NULL`. 

SELECT u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id = o.user_id; -- Returns ALL users, even if they have no orders 

 Returns all rows from the **right** table, with matching rows from the left table. Less common — usually you can rewrite as a LEFT JOIN by swapping table order. 

SELECT u.name, o.id AS order_id FROM users u RIGHT JOIN orders o ON u.id = o.user_id; -- All orders, even if user is missing 

 Returns all rows from **both** tables. Missing matches are `NULL` on the opposite side. 

SELECT u.name, o.id AS order_id FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id; -- All users and all orders, matched where possible 

 Cartesian product — every row from table A combined with every row from table B. Use sparingly. 

SELECT sizes.name, colors.name FROM sizes CROSS JOIN colors; -- Returns size × color combinations 

 Joining a table to itself — useful for hierarchies (e.g., employees and managers). 

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

 `GROUP BY` groups rows with the same values, then aggregate functions are applied per group. `HAVING` filters groups (like WHERE filters rows). 

-- Count users per status SELECT status, COUNT(*) AS user_count FROM users GROUP BY status; -- Multiple grouping columns SELECT category_id, YEAR(created_at) AS year, COUNT(*) AS count FROM products GROUP BY category_id, YEAR(created_at); -- Using HAVING (filter groups) SELECT category_id, AVG(price) AS avg_price FROM products GROUP BY category_id HAVING AVG(price) > 50; -- HAVING with multiple conditions SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent FROM orders GROUP BY user_id HAVING COUNT(*) >= 5 AND SUM(total) > 1000 ORDER BY total_spent DESC; 

 Sort results by one or more columns. Sorting is applied last, after GROUP BY and HAVING. 

-- Ascending (default) SELECT name, price FROM products ORDER BY price; -- Descending SELECT name, price FROM products ORDER BY price DESC; -- Multiple sort keys SELECT name, category_id, price FROM products ORDER BY category_id ASC, price DESC; -- Sort by aggregate alias SELECT category_id, COUNT(*) AS cnt FROM products GROUP BY category_id ORDER BY cnt DESC; -- Sort by column position (not recommended) SELECT name, price FROM products ORDER BY 2 DESC; 

 Subqueries (nested queries) can appear in SELECT, FROM, WHERE, or HAVING clauses. They let you break complex problems into logical steps. 

-- Subquery in WHERE SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products); -- Subquery with IN SELECT name, email FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 500); -- Subquery in SELECT (scalar subquery) SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u; -- Subquery in FROM (derived table / subquery as table) SELECT category, AVG(price) AS avg_price FROM ( SELECT p.*, c.name AS category FROM products p JOIN categories c ON p.category_id = c.id ) AS product_categories GROUP BY category; -- Correlated subquery (references outer query) SELECT p1.name, p1.price, p1.category_id FROM products p1 WHERE p1.price = ( SELECT MAX(p2.price) FROM products p2 WHERE p2.category_id = p1.category_id ); -- Gets the most expensive product in each category 

 Window functions perform calculations across a set of rows related to the current row — **without collapsing** the result set like GROUP BY does. They are among the most powerful SQL features for analytics. 

-- ROW_NUMBER: unique sequential number per partition SELECT name, salary, department_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; -- Within each department, rank employees by salary descending -- RANK: same values get same rank, next skips numbers SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; -- 3rd place if 2 tied: 100, 95, 95, 90 → ranks 1, 2, 2, 4 -- DENSE_RANK: no gaps in ranking SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees; -- 100, 95, 95, 90 → ranks 1, 2, 2, 3 

 Access data from previous or next rows without self-joins. 

-- LAG: previous row value SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue, LAG(revenue, 7) OVER (ORDER BY date) AS prev_week_revenue FROM daily_revenue; -- LEAD: next row value SELECT date, revenue, LEAD(revenue, 1) OVER (ORDER BY date) AS next_day_revenue FROM daily_revenue; -- Difference from previous value SELECT employee_id, salary, salary - LAG(salary, 1, 0) OVER (ORDER BY employee_id) AS salary_change FROM salaries; 

-- Running total SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM transactions; -- Moving average (3-day) SELECT date, revenue, AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day FROM daily_revenue; -- Partitioned aggregates SELECT department_id, employee_name, salary, AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary, salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_dept_avg FROM employees; 

-- Single row INSERT INTO users (name, email, status) VALUES ('Alice', 'alice@example.com', 'active'); -- Multiple rows INSERT INTO products (name, price, category_id) VALUES ('Widget', 9.99, 1), ('Gadget', 24.99, 1), ('Doohickey', 14.99, 2); -- Insert from query INSERT INTO archived_orders (id, user_id, total, created_at) SELECT id, user_id, total, created_at FROM orders WHERE created_at < '2023-01-01'; 

-- Simple update UPDATE users SET status = 'inactive' WHERE last_login < '2024-01-01'; -- Update multiple columns UPDATE products SET price = price * 1.10, updated_at = NOW() WHERE category_id = 5; -- Update with subquery UPDATE products SET category_id = ( SELECT id FROM categories WHERE name = 'Clearance' ) WHERE category_id IS NULL; 

-- Delete specific rows DELETE FROM logs WHERE created_at < '2020-01-01'; -- Delete all rows (truncate is faster) DELETE FROM temp_data; -- Delete with subquery DELETE FROM users WHERE id NOT IN (SELECT DISTINCT user_id FROM orders); 

-- Basic table CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, status VARCHAR(20) DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Table with foreign key CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id), total DECIMAL(10, 2) NOT NULL CHECK (total >= 0), status VARCHAR(20) DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Table with composite primary key CREATE TABLE order_items ( order_id INTEGER REFERENCES orders(id), product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, PRIMARY KEY (order_id, product_id) ); 

 Indexes speed up queries at the cost of slower writes. Use them on columns used in WHERE, JOIN, and ORDER BY. 

-- Single column index CREATE INDEX idx_users_email ON users(email); -- Composite index (column order matters!) CREATE INDEX idx_orders_user_status ON orders(user_id, status); -- Unique index CREATE UNIQUE INDEX idx_products_sku ON products(sku); -- Partial index (Postgres) CREATE INDEX idx_active_users ON users(email) WHERE status = 'active'; -- Full-text index (MySQL/Postgres) CREATE INDEX idx_products_name ON products USING GIN(to_tsvector('english', name)); -- Drop index DROP INDEX idx_users_email; 

 CTEs let you name a subquery and reference it like a table. They improve readability and enable recursive queries. 

-- Basic CTE WITH high_value_orders AS ( SELECT user_id, SUM(total) AS total_spent FROM orders WHERE status = 'delivered' GROUP BY user_id HAVING SUM(total) > 1000 ) SELECT u.name, h.total_spent FROM users u JOIN high_value_orders h ON u.id = h.user_id; -- Recursive CTE (hierarchy) WITH RECURSIVE org_chart AS ( -- Base: top-level managers SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- Recursive: direct reports SELECT e.id, e.name, e.manager_id, oc.level + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id ) SELECT * FROM org_chart ORDER BY level, name; 

 These five mistakes cause most of the slow queries and wrong results
 I've debugged in production. Each one has a simple fix once you
 know what to look for. 
 
 - **Comparing to NULL with `=`.** `WHERE email = NULL` returns zero rows because NULL comparisons evaluate to UNKNOWN, not TRUE. Always use `IS NULL` / `IS NOT NULL` — and remember that `NULL &lt...
Source：https://dev.to/kingfujing/sql-query-cheat-sheet-essential-queries-and-patterns-3nda
My favorites