SQL Reference Cheat Sheet

Choose your JOIN type based on which table's rows you want to keep: all from left, right, both, or a Cartesian product

// Basic SELECT
SELECT name, email FROM users;
SELECT * FROM users;

// Distinct
SELECT DISTINCT country FROM users;

// WHERE clause
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE status = 'active' AND country = 'India';
SELECT * FROM users WHERE country IN ('India', 'USA', 'UK');
SELECT * FROM users WHERE name LIKE 'Ra%';  -- Starts with Ra
SELECT * FROM users WHERE name LIKE '%kumar';  -- Ends with kumar
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE phone IS NOT NULL;

// ORDER BY
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY country ASC, name DESC;

// LIMIT & OFFSET
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- Skip first 20, return next 10
SELECT * FROM users LIMIT 10, 20;        -- Alternative syntax

// CASE statement
SELECT name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END as age_group
FROM users;

// INNER JOIN
SELECT u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

// LEFT JOIN (LEFT OUTER JOIN)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

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

// FULL OUTER JOIN (not all databases)
SELECT u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

// CROSS JOIN (Cartesian product)
SELECT * FROM colors
CROSS JOIN sizes;  -- All color-size combinations

// Self JOIN
SELECT u1.name, u2.name
FROM users u1
JOIN users u2 ON u1.department_id = u2.department_id
WHERE u1.id != u2.id;

// Multiple JOINs
SELECT u.name, p.title, o.amount
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN orders o ON u.id = o.user_id;

// Aggregate functions
COUNT(*) or COUNT(column)
SUM(amount)
AVG(price)
MIN(score)
MAX(salary)
GROUP_CONCAT(name) -- MySQL

// GROUP BY
SELECT country, COUNT(*) as count
FROM users
GROUP BY country;

// GROUP BY multiple columns
SELECT country, age, COUNT(*) as count
FROM users
GROUP BY country, age;

// HAVING clause (filter groups)
SELECT country, COUNT(*) as count
FROM users
GROUP BY country
HAVING COUNT(*) > 5;  -- Only countries with >5 users

// Complex grouping
SELECT
  YEAR(created_at) as year,
  MONTH(created_at) as month,
  COUNT(*) as orders,
  SUM(amount) as total
FROM orders
GROUP BY YEAR(created_at), MONTH(created_at)
HAVING SUM(amount) > 1000;

// GROUP_CONCAT (string aggregation)
SELECT user_id, GROUP_CONCAT(tag SEPARATOR ', ') as tags
FROM user_tags
GROUP BY user_id;

// Scalar subquery
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;

// IN subquery
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

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

// Derived table (FROM subquery)
SELECT * FROM (
  SELECT name, COUNT(*) as orders
  FROM users u
  JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
) user_stats
WHERE orders > 5;

// Common Table Expression (CTE) - WITH clause
WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
  SELECT user_id, COUNT(*) as count
  FROM orders
  GROUP BY user_id
)
SELECT au.name, uo.count
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

// Recursive CTE
WITH RECURSIVE numbers AS (
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

// ROW_NUMBER, RANK, DENSE_RANK
SELECT
  name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

// ROW_NUMBER: 1, 2, 3, 4, 5
// RANK: 1, 2, 2, 4, 5 (ties get same rank, skip next)
// DENSE_RANK: 1, 2, 2, 3, 4 (ties get same rank, no skip)

// PARTITION BY
SELECT
  name, department, salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

// Running totals
SELECT
  order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

// Moving average
SELECT
  date, close,
  AVG(close) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as moving_avg_5days
FROM stock_prices;

// LAG, LEAD (access previous/next row)
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  LEAD(revenue) OVER (ORDER BY month) as next_month
FROM monthly_sales;

// FIRST_VALUE, LAST_VALUE
SELECT
  name, salary, department,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary
FROM employees;

// Create index
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);
CREATE INDEX idx_name_email ON users(name, email);  -- Composite

// Drop index
DROP INDEX idx_email;

// EXPLAIN (query plan)
EXPLAIN SELECT * FROM users WHERE email = 'raj@example.com';

// Query optimization tips
- Index on WHERE columns: faster filtering
- Index on JOIN columns: faster joins
- Index on ORDER BY: avoid sorting
- Avoid functions in WHERE (breaks index): WHERE YEAR(date) = 2024 is bad
- Use LIKE carefully: LIKE 'prefix%' is OK, LIKE '%suffix' is slow
- Composite indexes: order matters (equality first, then range)

// Slow query
SELECT * FROM orders o
WHERE YEAR(o.created_at) = 2024
AND o.status = 'completed';

// Better: Use date range
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
AND status = 'completed';
-- Create index on (status, created_at)

// INSERT
INSERT INTO users (name, email, age)
VALUES ('Raj', 'raj@example.com', 16);

INSERT INTO users (name, email, age)
VALUES
('Raj', 'raj@example.com', 16),
('Priya', 'priya@example.com', 15);

// INSERT SELECT
INSERT INTO users_backup
SELECT * FROM users WHERE created_at < '2020-01-01';

// UPDATE
UPDATE users
SET status = 'inactive'
WHERE last_login < '2020-01-01';

UPDATE users
SET age = age + 1, updated_at = NOW()
WHERE id = 1;

// DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE status = 'inactive' AND created_at < '2020-01-01';

// TRUNCATE (fast delete all, can't rollback)
TRUNCATE TABLE users;

// Transactions (ensure all or nothing)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- Or ROLLBACK if error

// UPSERT (INSERT OR UPDATE)
INSERT INTO users (id, name, email) VALUES (1, 'Raj', 'raj@example.com')
ON DUPLICATE KEY UPDATE name = 'Raj', email = 'raj@example.com';