SQL Cheat Sheet 2026 — Queries, JOINs, and Performance

SELECT * FROM users;                          -- all columns
SELECT name, email FROM users;                -- specific columns
SELECT DISTINCT city FROM users;              -- unique values
SELECT name AS full_name FROM users;          -- column alias
SELECT * FROM users LIMIT 10;                 -- first 10 rows
SELECT * FROM users LIMIT 10 OFFSET 20;      -- rows 21-30 (pagination)

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE city = 'Berlin';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE city IN ('Berlin', 'Munich', 'Hamburg');
SELECT * FROM users WHERE name LIKE 'A%';         -- starts with A
SELECT * FROM users WHERE name LIKE '%son';        -- ends with son
SELECT * FROM users WHERE name LIKE '%alex%';      -- contains alex
SELECT * FROM users WHERE email IS NULL;           -- null check
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE age > 18 AND city = 'Berlin';
SELECT * FROM users WHERE age > 65 OR age < 18;
SELECT * FROM users WHERE NOT city = 'Berlin';

SELECT * FROM users ORDER BY name;                -- ascending (default)
SELECT * FROM users ORDER BY age DESC;            -- descending
SELECT * FROM users ORDER BY city, name;          -- multiple columns
SELECT * FROM users ORDER BY age DESC LIMIT 5;    -- top 5 oldest

-- Insert one row
INSERT INTO users (name, email, age) VALUES ('Alex', 'alex@example.com', 25);

-- Insert multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Sam', 'sam@example.com', 30),
    ('Jordan', 'jordan@example.com', 22);

-- Update
UPDATE users SET age = 26 WHERE name = 'Alex';

-- Update multiple columns
UPDATE users SET city = 'Munich', age = 27 WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 5;

-- Delete all rows (use with caution!)
DELETE FROM users;

-- Faster delete all (resets table)
TRUNCATE TABLE users;

-- UPSERT — insert or update if exists (PostgreSQL)
INSERT INTO users (email, name) VALUES ('alex@example.com', 'Alex')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- MySQL equivalent
INSERT INTO users (email, name) VALUES ('alex@example.com', 'Alex')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INNER JOIN        LEFT JOIN         RIGHT JOIN        FULL JOIN
┌───┬───┐        ┌───┬───┐        ┌───┬───┐        ┌───┬───┐
│ A │ B │        │ A │ B │        │ A │ B │        │ A │ B │
│ ■ │ ■ │        │ ■ │ ■ │        │ ■ │ ■ │        │ ■ │ ■ │
│   │   │        │ ■ │   │        │   │ ■ │        │ ■ │   │
│   │   │        │   │   │        │   │   │        │   │ ■ │
└───┴───┘        └───┴───┘        └───┴───┘        └───┴───┘
 matching          all A +          all B +         all rows,
  rows            matching         matching        NULL where
                                                    no match

-- INNER JOIN — only matching rows from both tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN — all rows from left + matching from right
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Users without orders get NULL for o.total

-- RIGHT JOIN — all rows from right + matching from left
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL JOIN — all rows from both (NULL where no match)
-- Note: RIGHT JOIN and FULL JOIN are not supported in SQLite
SELECT u.name, o.total
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

-- Self-join — join a table to itself
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, 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;

-- Group By
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- HAVING — filter groups (WHERE filters rows, HAVING filters groups)
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 100;

-- Multiple aggregations
SELECT
    city,
    COUNT(*) AS total,
    AVG(age) AS avg_age,
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM users
GROUP BY city
ORDER BY total DESC;

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

-- Subquery in FROM
SELECT city, avg_age FROM (
    SELECT city, AVG(age) AS avg_age FROM users GROUP BY city
) AS city_stats
WHERE avg_age > 30;

-- EXISTS — true if subquery returns any rows
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- CTE (Common Table Expression) — readable alternative to subqueries
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;

-- ROW_NUMBER — unique row number per partition
SELECT name, city,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

-- RANK — same rank for ties, gaps after
SELECT name, score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM players;

-- DENSE_RANK — same rank for ties, no gaps
SELECT name, score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM players;

-- PARTITION BY — window function per group
SELECT name, city, age,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) AS city_rank
FROM users;

-- LAG / LEAD — access previous/next row
SELECT date, revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS growth
FROM sales;

-- Running total
SELECT date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Moving average (last 7 rows)
SELECT date, amount,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

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

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

-- INTERSECT — rows in both queries
SELECT name FROM customers
INTERSECT
SELECT name FROM suppliers;

-- EXCEPT — rows in first but not second
SELECT name FROM customers
EXCEPT
SELECT name FROM suppliers;

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN old_data;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;

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

-- COALESCE — first non-null value
SELECT name, COALESCE(nickname, name) AS display_name FROM users;

-- NULLIF — returns NULL if equal (useful to avoid division by zero)
SELECT total / NULLIF(count, 0) AS average FROM stats;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,               -- auto-increment (PostgreSQL)
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 0),
    city VARCHAR(100) DEFAULT 'Unknown',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

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

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

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Drop an index
DROP INDEX idx_users_email;

-- Check query performance
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alex@example.com';

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- If something goes wrong
ROLLBACK;