How do I find duplicate rows in SQL?

Use GROUP BY with HAVING COUNT(*) > 1: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. To delete duplicates, keep the row with the lowest ID using: DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email).

What is a SQL subquery?

A subquery is a SELECT statement nested inside another query. It can appear in WHERE, FROM, or SELECT clauses. Example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees). Subqueries can be correlated (reference outer query) or non-correlated (independent).

How do I use window functions in SQL?

Window functions perform calculations across rows related to the current row using OVER(). Common functions: ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) assigns a rank within each department. LAG() and LEAD() access previous and next rows. SUM() OVER() calculates running totals. They don't collapse rows like GROUP BY does.

What is the difference between DELETE and TRUNCATE in SQL?

DELETE removes specific rows matching a WHERE condition, is logged per row, can be rolled back, and fires triggers. TRUNCATE removes all rows at once, is minimally logged (faster), cannot be rolled back in most databases, and doesn't fire row-level triggers. Use DELETE for conditional removal, TRUNCATE to empty a table completely.

How do I create an index in SQL?

Use CREATE INDEX: CREATE INDEX idx_user_email ON users(email). For unique indexes: CREATE UNIQUE INDEX idx_unique_email ON users(email). Composite indexes cover multiple columns: CREATE INDEX idx_name_dept ON employees(last_name, department). Use indexes on columns frequently used in WHERE, JOIN ON, and ORDER BY clauses. Too many indexes slow down INSERT/UPDATE operations.

What is a CTE (Common Table Expression) in SQL?

A CTE is a temporary named result set defined with WITH, making complex queries more readable. Syntax: WITH cte_name AS (SELECT ...) SELECT * FROM cte_name. CTEs can reference themselves for recursive queries (e.g., traversing hierarchical data like org charts or category trees). Unlike subqueries, CTEs can be referenced multiple times in the same query.

SQL Cheat Sheet — Quick Reference for SELECT, JOIN, GROUP BY & More

Q: What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before aggregation, while HAVING filters groups after aggregation. Use WHERE with regular column conditions, and HAVING with aggregate functions like COUNT(), SUM(), or AVG(). Example: SELECT dept, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY dept HAVING COUNT(*) > 5.

Q: What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?

INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right (NULLs where no match). RIGHT JOIN returns all rows from the right table plus matching rows from the left. FULL OUTER JOIN returns all rows from both tables.

SELECT — Querying Data

Basic SELECT

Retrieve all or specific columns from a table

SELECT * FROM users; SELECT id, name, email FROM users;

WHERE clause

Filter rows based on conditions

SELECT * FROM users WHERE age > 18 AND active = true;

ORDER BY

Sort results ascending or descending

SELECT * FROM products ORDER BY price DESC, name ASC;

LIMIT / OFFSET

Paginate results

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- page 3 of 10

DISTINCT

Remove duplicate rows from results

SELECT DISTINCT country FROM customers;

LIKE — pattern matching

% matches any sequence, _ matches one character

SELECT * FROM users WHERE email LIKE '%@gmail.com'; SELECT * FROM products WHERE name LIKE 'Pro%'; -- starts with "Pro"

IN / NOT IN

Match against a list of values

SELECT * FROM orders WHERE status IN ('pending', 'processing'); SELECT * FROM users WHERE id NOT IN (1, 2, 3);

BETWEEN

Filter within a range (inclusive)

SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';

IS NULL / IS NOT NULL

Check for missing values (never use = NULL)

SELECT * FROM users WHERE deleted_at IS NULL; SELECT * FROM orders WHERE shipped_at IS NOT NULL;

CASE expression

Conditional logic inside a SELECT

SELECT name, salary, CASE WHEN salary >= 100000 THEN 'Senior' WHEN salary >= 60000 THEN 'Mid' ELSE 'Junior' END AS level FROM employees;

COALESCE

Return the first non-NULL value

SELECT name, COALESCE(phone, email, 'no contact') AS contact FROM users;

UNION / UNION ALL

Combine results from two queries

SELECT id, name FROM customers UNION -- removes duplicates SELECT id, name FROM vendors; SELECT id, name FROM customers UNION ALL -- keeps duplicates (faster)

JOINs — Combining Tables

INNER JOIN

Only rows with matching values in both tables

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

LEFT JOIN

All rows from left table + matched rows from right (NULLs where no match)

SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id; -- users with no orders still appear (o.total = NULL)

RIGHT JOIN

All rows from right table + matched rows from left

SELECT u.name, o.id FROM users u RIGHT JOIN orders o ON u.id = o.user_id; -- orders with no user still appear

FULL OUTER JOIN

All rows from both tables (NULLs where no match)

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

Self JOIN

Join a table to itself (e.g., org hierarchy)

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

Multi-table JOIN

Chain multiple joins together

SELECT o.id, u.name, p.title FROM orders o JOIN users u ON o.user_id = u.id JOIN products p ON o.product_id = p.id;

Find unmatched rows

Use LEFT JOIN + WHERE NULL to find rows with no match

-- users who have never placed an order SELECT u.id, u.name FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;

Aggregate Functions & GROUP BY

COUNT / SUM / AVG / MIN / MAX

Core aggregate functions

SELECT COUNT(*) AS total_rows, COUNT(email) AS with_email, -- ignores NULLs SUM(amount) AS total, AVG(amount) AS avg, MIN(created_at) AS oldest, MAX(created_at) AS newest FROM orders;

GROUP BY

Aggregate per group

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY headcount DESC;

HAVING

Filter groups after aggregation (not WHERE)

SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5; -- only customers with 5+ orders

Find duplicates

Identify rows that appear more than once

SELECT email, COUNT(*) AS n FROM users GROUP BY email HAVING COUNT(*) > 1;

GROUP BY ROLLUP

Subtotals + grand total (PostgreSQL, MySQL 8+, SQL Server)

SELECT region, product, SUM(sales) FROM sales_data GROUP BY ROLLUP(region, product); -- includes subtotals per region + grand total row

STRING_AGG / GROUP_CONCAT

Aggregate values into a delimited string

-- PostgreSQL SELECT user_id, STRING_AGG(tag, ', ') AS tags FROM user_tags GROUP BY user_id; -- MySQL SELECT user_id, GROUP_CONCAT(tag SEPARATOR ', ') FROM user_tags GROUP BY user_id;

Modify Data — INSERT, UPDATE, DELETE

INSERT

Add one or multiple rows

INSERT INTO users (name, email, created_at) VALUES ('Alice', 'alice@example.com', NOW()); -- multiple rows INSERT INTO products (name, price) VALUES ('Pen', 1.99), ('Notebook', 4.99);

INSERT … SELECT

Populate a table from a query result

INSERT INTO archived_orders (id, user_id, total) SELECT id, user_id, total FROM orders WHERE created_at < '2023-01-01';

UPDATE

Modify existing rows (always use WHERE!)

UPDATE users SET email = 'new@example.com', updated_at = NOW() WHERE id = 42; -- update from another table UPDATE orders o JOIN users u ON o.user_id = u.id SET o.region = u.region;

DELETE

Remove rows matching a condition (always use WHERE!)

DELETE FROM sessions WHERE expires_at < NOW(); -- delete with JOIN (MySQL) DELETE o FROM orders o JOIN users u ON o.user_id = u.id WHERE u.deleted_at IS NOT NULL;

UPSERT (INSERT … ON CONFLICT)

Insert or update if duplicate key (PostgreSQL)

-- PostgreSQL INSERT INTO user_settings (user_id, theme) VALUES (42, 'dark') ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme; -- MySQL INSERT INTO user_settings (user_id, theme) VALUES (42, 'dark') ON DUPLICATE KEY UPDATE theme = VALUES(theme);

TRUNCATE vs DELETE

TRUNCATE is faster but can't be rolled back in most DBs

DELETE FROM temp_data; -- logged, rollback-safe TRUNCATE TABLE temp_data; -- fast, resets auto-increment

Transactions

Group statements into an atomic unit

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- or ROLLBACK to undo

Schema — CREATE, ALTER, DROP

CREATE TABLE

Define a new table with columns and constraints

CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, age INT CHECK (age >= 0), created_at TIMESTAMPTZ DEFAULT NOW() );

Foreign key

Reference another table's primary key

CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE, total DECIMAL(10, 2), status VARCHAR(20) DEFAULT 'pending' );

ALTER TABLE

Modify an existing table structure

ALTER TABLE users ADD COLUMN phone VARCHAR(20); ALTER TABLE users DROP COLUMN phone; ALTER TABLE users RENAME COLUMN username TO name; ALTER TABLE users ALTER COLUMN name TYPE TEXT; ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

DROP TABLE / DATABASE

Permanently remove a table or database

DROP TABLE IF EXISTS temp_data; DROP TABLE users CASCADE; -- removes dependent objects too DROP DATABASE myapp_dev; -- irreversible!

CREATE VIEW

Save a query as a reusable virtual table

CREATE OR REPLACE VIEW active_users AS SELECT id, name, email FROM users WHERE deleted_at IS NULL AND active = true; -- query it like a table SELECT * FROM active_users WHERE name LIKE 'A%';

Data types (PostgreSQL)

Common column types

SERIAL / BIGSERIAL -- auto-increment integer INT / BIGINT -- integers DECIMAL(10,2) / NUMERIC -- exact decimal FLOAT / DOUBLE PRECISION -- floating point VARCHAR(n) / TEXT -- strings BOOLEAN -- true/false DATE / TIME / TIMESTAMP -- date and time TIMESTAMPTZ -- timestamp with timezone UUID -- universally unique ID JSONB / JSON -- JSON data

Subqueries & CTEs

Subquery in WHERE

Filter using result of another query

-- employees earning above company average SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery in FROM (derived table)

Treat a query result as a table

SELECT dept, avg_salary FROM ( SELECT department AS dept, AVG(salary) AS avg_salary FROM employees GROUP BY department ) AS dept_stats WHERE avg_salary > 80000;

EXISTS / NOT EXISTS

Check if a subquery returns any rows (fast)

-- customers with at least one order SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

CTE — Common Table Expression

Named subquery for readability; reusable in same query

WITH high_value AS ( SELECT customer_id, SUM(total) AS ltv FROM orders GROUP BY customer_id HAVING SUM(total) > 1000 ) SELECT u.name, h.ltv FROM users u JOIN high_value h ON u.id = h.customer_id;

Recursive CTE

Traverse hierarchical data (org charts, categories)

WITH RECURSIVE org AS ( -- anchor: top-level employees SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- recursive: employees with a manager SELECT e.id, e.name, e.manager_id, o.level + 1 FROM employees e JOIN org o ON e.manager_id = o.id ) SELECT * FROM org ORDER BY level;

Multiple CTEs

Chain CTEs with commas for complex pipelines

WITH monthly_sales AS ( SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue FROM orders GROUP BY 1 ), ranked AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rk FROM monthly_sales ) SELECT * FROM ranked WHERE rk <= 3;

Window Functions

ROW_NUMBER

Unique sequential number per partition

SELECT name, department, salary, ROW_NUMBER() OVER ( PARTITION BY department ORDER BY salary DESC ) AS rank_in_dept FROM employees;

RANK / DENSE_RANK

RANK skips numbers on ties; DENSE_RANK doesn't

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM leaderboard;

Running total (SUM OVER)

Cumulative sum ordered by date

SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS running_total FROM daily_sales;

LAG / LEAD

Access the previous or next row's value

SELECT date, revenue, LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue, revenue - LAG(revenue, 1) OVER (ORDER BY date) AS delta FROM daily_sales;

FIRST_VALUE / LAST_VALUE

First or last value in a window frame

SELECT name, department, salary, FIRST_VALUE(salary) OVER ( PARTITION BY department ORDER BY salary DESC ) AS top_salary_in_dept FROM employees;

NTILE

Divide rows into N equal buckets

SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees; -- quartile 1 = lowest 25%, 4 = top 25%

Indexes & Performance

CREATE INDEX

Speed up queries on frequently filtered columns

CREATE INDEX idx_users_email ON users(email); CREATE UNIQUE INDEX idx_users_email ON users(email); CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

Partial index

Index only a subset of rows (PostgreSQL)

-- only index active users — smaller, faster CREATE INDEX idx_active_users ON users(email) WHERE active = true;

EXPLAIN / EXPLAIN ANALYZE

Show the query execution plan

EXPLAIN SELECT * FROM orders WHERE user_id = 42; EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42; -- ANALYZE actually runs the query and shows real timing

DROP INDEX

Remove an index

DROP INDEX IF EXISTS idx_users_email; DROP INDEX CONCURRENTLY idx_users_email; -- no table lock (PG)

Query optimization tips

Common patterns to avoid slow queries

-- ❌ slow: function on indexed column (disables index use) WHERE LOWER(email) = 'alice@example.com' -- ✅ fast: use a functional index instead CREATE INDEX ON users (LOWER(email)); -- ❌ slow: SELECT * (fetches unnecessary columns) -- ✅ fast: SELECT only the columns you need -- ❌ slow: NOT IN with subquery (full scan) -- ✅ fast: NOT EXISTS or LEFT JOIN … WHERE IS NULL

Common Table Stats (PostgreSQL)

Check table size and index usage

-- table sizes SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC; -- index usage SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

Pro Tips

Never update without WHERE

Before running UPDATE or DELETE, write the SELECT with the same WHERE clause first to verify you're targeting the right rows.

SELECT * FROM users WHERE id = 42; -- verify first UPDATE users SET active = false WHERE id = 42;

NULL is contagious

Any comparison with NULL returns NULL (unknown), not true or false. Use IS NULL / IS NOT NULL — never = NULL or != NULL.

SELECT NULL = NULL; -- returns NULL (not TRUE) SELECT NULL IS NULL; -- returns TRUE ✓

Use indexes on JOIN columns

Always index the foreign key column used in a JOIN. Without an index, the database performs a full table scan for every row in the outer table.

CREATE INDEX idx_orders_user_id ON orders(user_id);

Wrap risky writes in transactions

Use BEGIN / COMMIT to group DML statements. You can ROLLBACK to undo everything if something goes wrong before you commit.

BEGIN; UPDATE ...; DELETE FROM ...; -- test the result, then: COMMIT; -- or ROLLBACK;

LIMIT doesn't speed up COUNT

SELECT COUNT(*) FROM table LIMIT 10 counts ALL rows, then ignores the limit. For an approximate count use: SELECT reltuples FROM pg_class WHERE relname = 'table'.

Use RETURNING (PostgreSQL)

Get the inserted/updated/deleted rows back without a second query. Useful for getting auto-generated IDs after INSERT.

INSERT INTO users (name) VALUES ('Bob') RETURNING id, name; UPDATE orders SET status = 'shipped' WHERE id = 99 RETURNING *;

Frequently Asked Questions

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation. Use WHERE with regular column conditions and HAVING with aggregate functions like COUNT(), SUM(), or AVG(). Example: GROUP BY dept HAVING COUNT(*) > 5.

What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?

INNER JOIN returns only rows with matching values in both tables. LEFT JOIN returns all rows from the left table plus matched rows from the right (NULLs where no match). RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both tables.

How do I find and delete duplicate rows in SQL?

Find duplicates: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. Delete duplicates, keeping the lowest ID: DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email).

When should I use a CTE vs a subquery?

Use a CTE (WITH) when: the subquery is referenced more than once, the query is complex and you want readability, or you need a recursive query. Use a plain subquery for simple one-off conditions. Performance is usually equivalent in modern databases — choose CTEs for clarity.

How do window functions differ from GROUP BY?

Window functions perform calculations across related rows without collapsing them into groups. GROUP BY collapses multiple rows into one per group. Use window functions when you need both the original rows and an aggregate (e.g., show each employee's salary AND the department average in the same row).

What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows, is fully logged, can be rolled back, and fires triggers. TRUNCATE removes all rows instantly, is minimally logged (much faster on large tables), cannot be rolled back in most databases, and doesn't fire row-level triggers. Use TRUNCATE only to wipe a table completely.

How do I create and use indexes effectively?

Create indexes on columns used frequently in WHERE, JOIN ON, and ORDER BY. Use EXPLAIN ANALYZE to verify the index is being used. Avoid over-indexing — every index slows INSERT/UPDATE/DELETE. For multi-column queries, put the most selective column first in the index.

How do I do a recursive query in SQL?

Use a recursive CTE: WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ... FROM cte JOIN ...). The anchor member defines the starting rows (e.g., root nodes). The recursive member joins back to the CTE to traverse deeper levels. Common uses: org charts, category trees, bill-of-materials, file system hierarchies.