back
loading skill details...
Comprehensive SQL cheatsheet with SELECT, JOINs, INSERT/UPDATE/DELETE, aggregation, window functions, subqueries, and best practices. Use when needing quick...
---
name: sql-cheatsheet
description: Comprehensive SQL cheatsheet with SELECT, JOINs, INSERT/UPDATE/DELETE, aggregation, window functions, subqueries, and best practices. Use when needing quick reference for SQL queries, database operations, or common query patterns.
---
# SQL Cheatsheet
Quick reference for SQL queries and database operations.
---
## 📖 Basic SELECT
### Retrieve Data
```sql
-- Select all columns
SELECT * FROM table_name;
-- Select specific columns
SELECT column1, column2 FROM table_name;
-- Select with alias
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;
-- Distinct values
SELECT DISTINCT column FROM table_name;
-- Limit results
SELECT * FROM table_name LIMIT 10;
-- MySQL/PostgreSQL LIMIT with offset
SELECT * FROM table_name LIMIT 10 OFFSET 20;
-- SQL Server TOP
SELECT TOP 10 * FROM table_name;
```
### WHERE Clause
```sql
-- Equality
SELECT * FROM table WHERE column = 'value';
-- Comparison
SELECT * FROM table WHERE column > 100;
SELECT * FROM table WHERE column <= 50;
-- Multiple conditions
SELECT * FROM table WHERE column1 = 'a' AND column2 > 10;
SELECT * FROM table WHERE column1 = 'a' OR column2 > 10;
-- IN clause
SELECT * FROM table WHERE column IN ('a', 'b', 'c');
-- BETWEEN
SELECT * FROM table WHERE column BETWEEN 1 AND 100;
-- LIKE (pattern matching)
SELECT * FROM table WHERE column LIKE 'prefix%'; -- Starts with
SELECT * FROM table WHERE column LIKE '%suffix'; -- Ends with
SELECT * FROM table WHERE column LIKE '%contains%'; -- Contains
SELECT * FROM table WHERE column LIKE '_attern'; -- Single char wildcard
-- IS NULL / IS NOT NULL
SELECT * FROM table WHERE column IS NULL;
SELECT * FROM table WHERE column IS NOT NULL;
-- NOT
SELECT * FROM table WHERE column NOT IN ('a', 'b');
```
### ORDER BY
```sql
-- Ascending (default)
SELECT * FROM table ORDER BY column ASC;
-- Descending
SELECT * FROM table ORDER BY column DESC;
-- Multiple columns
SELECT * FROM table ORDER BY column1 ASC, column2 DESC;
```
---
## 🔗 JOINs
### INNER JOIN (intersection)
```sql
SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;
```
### LEFT JOIN (all from left)
```sql
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id;
```
### RIGHT JOIN (all from right)
```sql
SELECT a.*, b.*
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.a_id;
```
### FULL OUTER JOIN (all from both)
```sql
-- PostgreSQL, SQL Server
SELECT a.*, b.*
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;
```
### CROSS JOIN (cartesian product)
```sql
SELECT a.*, b.*
FROM table_a a
CROSS JOIN table_b b;
```
### SELF JOIN
```sql
SELECT a.name, b.name AS manager_name
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.id;
```
### Multiple JOINs
```sql
SELECT o.*, c.name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;
```
---
## ➕ Aggregation
### COUNT, SUM, AVG, MIN, MAX
```sql
-- Count rows
SELECT COUNT(*) FROM table;
-- Count non-null values
SELECT COUNT(column) FROM table;
-- Sum
SELECT SUM(column) FROM table;
-- Average
SELECT AVG(column) FROM table;
-- Minimum
SELECT MIN(column) FROM table;
-- Maximum
SELECT MAX(column) FROM table;
```
### GROUP BY
```sql
-- Group by one column
SELECT category, COUNT(*)
FROM products
GROUP BY category;
-- Group by multiple columns
SELECT category, status, COUNT(*), SUM(price)
FROM products
GROUP BY category, status;
-- With WHERE (filter before grouping)
SELECT category, COUNT(*)
FROM products
WHERE price > 100
GROUP BY category;
```
### HAVING (filter after grouping)
```sql
SELECT category, COUNT(*) as product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;
-- HAVING with WHERE
SELECT category, COUNT(*)
FROM products
WHERE price > 100
GROUP BY category
HAVING COUNT(*) > 5;
```
### GROUP BY + ORDER BY
```sql
SELECT category, SUM(price) as total
FROM products
GROUP BY category
ORDER BY total DESC;
```
---
## ➡️ Window Functions
### Basic Window
```sql
-- Row number
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;
-- Partition by department
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
```
### Ranking Functions
```sql
-- ROW_NUMBER: unique number, no ties
ROW_NUMBER() OVER (ORDER BY salary DESC)
-- RANK: gaps in ranking for ties
RANK() OVER (ORDER BY salary DESC)
-- DENSE_RANK: no gaps for ties
DENSE_RANK() OVER (ORDER BY salary DESC)
-- NTILE: divide into buckets
NTILE(4) OVER (ORDER BY salary DESC) as quartile
```
### Aggregate Window Functions
```sql
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg,
SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;
```
### Running Total / Cumulative Sum
```sql
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;
```
---
## 📝 Subqueries & CTEs
### Subquery in WHERE
```sql
SELECT *
FROM products
WHERE price > (SELECT AVG(price) FROM products);
```
### Subquery in FROM
```sql
SELECT avg_price
FROM (
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
) AS category_avg
WHERE avg_price > 100;
```
### Subquery in SELECT
```sql
SELECT
name,
(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) as order_count
FROM customers;
```
### EXISTS / NOT EXISTS
```sql
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);
```
### Common Table Expressions (CTE)
```sql
WITH category_stats AS (
SELECT
category,
COUNT(*) as count,
AVG(price) as avg_price
FROM products
GROUP BY category
)
SELECT * FROM category_stats
WHERE count > 10;
```
### Recursive CTE
```sql
WITH RECURSIVE employee_hierarchy AS (
-- Anchor member
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
```
---
## ✏️ Modify Data
### INSERT
```sql
-- Insert single row
INSERT INTO table (column1, column2)
VALUES ('value1', 'value2');
-- Insert multiple rows
INSERT INTO table (column1, column2)
VALUES
('value1', 'value2'),
('value3', 'value4');
-- Insert from another table
INSERT INTO table2 (column1, column2)
SELECT column1, column2 FROM table1 WHERE condition;
```
### UPDATE
```sql
-- Update all rows (careful!)
UPDATE table
SET column1 = 'new_value';
-- Update specific rows
UPDATE table
SET column1 = 'new_value'
WHERE condition;
-- Update multiple columns
UPDATE table
SET column1 = 'value1', column2 = 'value2'
WHERE condition;
-- Update with join (PostgreSQL)
UPDATE products p
SET price = price * 1.1
FROM categories c
WHERE p.category_id = c.id AND c.name = 'Electronics';
```
### DELETE
```sql
-- Delete all rows (careful!)
DELETE FROM table;
-- Delete specific rows
DELETE FROM table WHERE condition;
-- Delete with subquery
DELETE FROM products
WHERE category_id NOT IN (SELECT id FROM categories);
```
### TRUNCATE (faster than DELETE for all rows)
```sql
TRUNCATE TABLE table_name;
```
---
## 🏗️ Schema Operations
### CREATE TABLE
```sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
-- With foreign key
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
```
### ALTER TABLE
```sql
-- Add column
ALTER TABLE table ADD COLUMN new_column VARCHAR(100);
-- Modify column
ALTER TABLE table ALTER COLUMN column TYPE VARCHAR(200);
-- Rename column
ALTER TABLE table RENAME COLUMN old_name TO new_name;
-- Drop column
ALTER TABLE table DROP COLUMN column;
-- Add constraint
ALTER TABLE table ADD CONSTRAINT constraint_name UNIQUE (column);
```
### DROP TABLE
```sql
DROP TABLE table_name;
-- Drop if exists
DROP TABLE IF EXISTS table_name;
```
### CREATE INDEX
```sql
-- Basic index
CREATE INDEX idx_table_column ON table(column);
-- Composite index
CREATE INDEX idx_table_col1_col2 ON table(column1, column2);
-- Unique index
CREATE UNIQUE INDEX idx_table_column ON table(column);
-- Drop index
DROP INDEX idx_table_column;
```
---
## 💡 Useful Patterns
### Pagination
```sql
-- PostgreSQL, MySQL
SELECT * FROM table
ORDER BY id
LIMIT 10 OFFSET 40; -- Page 5 (10 per page)
-- SQL Server
SELECT * FROM table
ORDER BY id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY;
```
### Top N Per Group
```sql
WITH ranked AS (
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;
```
### Find Duplicates
```sql
SELECT column, COUNT(*)
FROM table
GROUP BY column
HAVING COUNT(*) > 1;
```
### Date Functions
```sql
-- Current date/time
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;
SELECT NOW(); -- PostgreSQL
-- Extract parts
SELECT EXTRACT(YEAR FROM date_column) FROM table;
SELECT EXTRACT(MONTH FROM date_column) FROM table;
-- Date arithmetic
SELECT date_column + INTERVAL '7 days' FROM table;
SELECT DATE_ADD(date_column, INTERVAL 7 DAY) FROM table; -- MySQL
```
### CASE Statements
```sql
SELECT
name,
price,
CASE
WHEN price < 50 THEN 'Budget'
WHEN price < 100 THEN 'Mid-range'
ELSE 'Premium'
END as price_category
FROM products;
```
### COALESCE (handle NULL)
```sql
SELECT COALESCE(column, 'default_value') FROM table;
```
---
## ⚡ Performance Tips
1. **Use EXPLAIN** to understand query execution plans
2. **Index columns** used in WHERE, JOIN, and ORDER BY
3. **Avoid SELECT *** - select only needed columns
4. **Use LIMIT** to preview results
5. **Prefer UNION ALL** over UNION if duplicates don't matter
6. **Avoid functions on indexed columns** in WHERE clause
7. **Use EXISTS** instead of IN for large datasets
8. **Batch large operations** to avoid locking
**Test queries with EXPLAIN:**
```sql
EXPLAIN SELECT * FROM table WHERE condition;
EXPLAIN ANALYZE SELECT * FROM table WHERE condition; -- PostgreSQL
```
don't have the plugin yet? install it then click "run inline in claude" again.