cheat sheet

SQL Cheat Sheet

Every clause, join, aggregate, window function, and CTE. Searchable, filterable by dialect, copy-ready.

/ to focus
dialect:
level:
SQL Formatter syntax highlighter
Input SQL

Querying

Quick reference
Select all rows
SELECT * FROM ...
Full basic query
SELECT ... WHERE ... LIMIT
Unique values
SELECT DISTINCT
Conditional output
CASE WHEN ... THEN

SELECT Basics

STDMySQLPG
beginner
Select all columns
SELECT * FROM users;
Select specific columns
SELECT id, name, email FROM users;
Column alias with AS
SELECT first_name AS name, salary * 12 AS annual_salary FROM employees;
Select distinct values
SELECT DISTINCT country FROM customers;
Limit rows returned
SELECT * FROM products LIMIT 10;
Limit with offset (pagination)
SELECT * FROM products LIMIT 10 OFFSET 20;
Returns rows 21-30
Order results ascending
SELECT * FROM users ORDER BY last_name ASC;
Order by multiple columns
SELECT * FROM orders ORDER BY status ASC, created_at DESC;
Full SELECT anatomy
SELECT col1, col2 FROM table_name WHERE condition GROUP BY col1 HAVING aggregate_condition ORDER BY col2 DESC LIMIT 10;

Expressions & Literals

STDMySQLPG
beginner
Arithmetic in SELECT
SELECT price * quantity AS total FROM order_items;
String concatenation
-- Standard SQL / PostgreSQL (|| operator) SELECT first_name || ' ' || last_name AS full_name FROM users; -- MySQL (CONCAT function) SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
CASE expression (if/else)
SELECT name, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM students;
COALESCE: return first non-null
SELECT COALESCE(phone, mobile, 'N/A') AS contact FROM users;
NULLIF: return null if two values are equal
SELECT NULLIF(votes, 0) FROM polls; -- avoids divide-by-zero

Filtering

Quick reference
Equality filter
WHERE col = 'value'
Match list
WHERE col IN (...)
Pattern match
WHERE col LIKE '%...'
Check for null
WHERE col IS NULL

WHERE Conditions

STDMySQLPG
beginner
Equality and inequality
SELECT * FROM users WHERE status = 'active'; SELECT * FROM users WHERE status != 'banned';
Comparison operators
=   !=   <   >   <=   >=
AND / OR / NOT
SELECT * FROM products WHERE price > 100 AND stock > 0; SELECT * FROM users WHERE role = 'admin' OR role = 'mod';
BETWEEN (inclusive)
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
IN list
SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');
NOT IN
SELECT * FROM products WHERE category_id NOT IN (5, 10, 15);
LIKE pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com'; SELECT * FROM users WHERE name LIKE 'Jo%'; -- starts with Jo SELECT * FROM users WHERE name LIKE '_ohn'; -- _ matches one char
ILIKE: case-insensitive LIKE (PostgreSQL)
SELECT * FROM users WHERE name ILIKE 'john%';
NULL checks
SELECT * FROM users WHERE deleted_at IS NULL; SELECT * FROM users WHERE phone IS NOT NULL;

HAVING & GROUP BY

STDMySQLPG
intermediate
GROUP BY single column
SELECT country, COUNT(*) AS total FROM users GROUP BY country;
GROUP BY multiple columns
SELECT year, month, SUM(revenue) FROM sales GROUP BY year, month;
HAVING: filter after grouping
SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;
WHERE filters rows; HAVING filters groups

Joins

Quick reference
Inner join
JOIN t2 ON t1.id = t2.t1_id
All left + matched right
LEFT JOIN
Join on same column name
JOIN t2 USING (col)
Rows with no match
LEFT JOIN ... WHERE IS NULL

Join Types

STDMySQLPG
beginner
INNER JOIN: matching rows in both tables
SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;
LEFT JOIN: all left rows, matching right rows
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id; -- users with no orders will have NULL for o.*
RIGHT JOIN: all right rows, matching left rows
SELECT u.name, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
FULL OUTER JOIN: all rows from both tables
SELECT u.name, o.total FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;
CROSS JOIN: every combination (cartesian product)
SELECT colors.name, sizes.label FROM colors CROSS JOIN sizes;
Self JOIN: join a table to itself
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
Multiple joins
SELECT u.name, p.title, c.body FROM users u JOIN posts p ON u.id = p.user_id JOIN comments c ON p.id = c.post_id;
JOIN USING - shorthand when column names match
SELECT u.name, o.total FROM users u JOIN orders o USING (user_id);
Equivalent to ON u.user_id = o.user_id - column appears once in result
NATURAL JOIN - auto-joins on all matching column names
SELECT * FROM users NATURAL JOIN orders;
Avoid in production - fragile if columns are added later

Set Operations

STDMySQLPG
intermediate
UNION: combine results, remove duplicates
SELECT email FROM customers UNION SELECT email FROM newsletter_subscribers;
UNION ALL: combine results, keep duplicates
SELECT product_id FROM sales_2023 UNION ALL SELECT product_id FROM sales_2024;
INTERSECT: rows present in both queries
SELECT user_id FROM newsletter INTERSECT SELECT user_id FROM purchases;
EXCEPT: rows in first query but not second
SELECT user_id FROM newsletter EXCEPT SELECT user_id FROM unsubscribed;

Aggregates

Quick reference
Count all rows
COUNT(*)
Sum a column
SUM(col)
Count per group
GROUP BY col
Filter groups
HAVING COUNT(*) > 1

Aggregate Functions

STDMySQLPG
beginner
COUNT rows
SELECT COUNT(*) FROM orders; SELECT COUNT(email) FROM users; -- ignores NULLs SELECT COUNT(DISTINCT country) FROM users;
SUM values
SELECT SUM(total) AS revenue FROM orders;
AVG values
SELECT AVG(salary) AS avg_salary FROM employees;
MIN and MAX
SELECT MIN(price), MAX(price) FROM products;
All aggregates together
1 2 3 4 5 6 7
SELECT COUNT(*) AS total_orders, SUM(total) AS revenue, AVG(total) AS avg_order, MIN(total) AS smallest, MAX(total) AS largest FROM orders;
STRING_AGG: concatenate strings in a group (PostgreSQL)
SELECT department, STRING_AGG(name, ', ') AS members FROM employees GROUP BY department;
GROUP_CONCAT: concatenate strings in a group (MySQL)
SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS members FROM employees GROUP BY department;

Window Functions

Quick reference
Number rows per group
ROW_NUMBER() OVER (...)
Running total
SUM(col) OVER (ORDER BY)
Previous row value
LAG(col, 1) OVER (...)
Rank with tie gaps
RANK() OVER (...)

OVER Clause & Ranking

STDMySQLPG
advanced
ROW_NUMBER: sequential row number per partition
SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;
RANK: rank with gaps for ties
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;
Two rows tied at rank 1 means next rank is 3
DENSE_RANK: rank without gaps for ties
SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM leaderboard;
Two rows tied at rank 1 means next rank is 2
NTILE: split rows into n buckets
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;

Running Totals & Navigation

STDMySQLPG
advanced
Running total with SUM OVER
SELECT order_date, total, SUM(total) OVER (ORDER BY order_date) AS running_total FROM orders;
Moving average (last 7 rows)
SELECT sale_date, amount, AVG(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS moving_avg_7 FROM sales;
LAG: access previous row's value
SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month FROM monthly_sales;
LEAD: access next row's value
SELECT month, revenue, LEAD(revenue, 1) OVER (ORDER BY month) AS next_month FROM monthly_sales;
FIRST_VALUE / LAST_VALUE
SELECT name, salary, FIRST_VALUE(salary) OVER ( PARTITION BY department ORDER BY salary DESC ) AS highest_in_dept FROM employees;

Subqueries & CTEs

Quick reference
Subquery in WHERE
WHERE col > (SELECT ...)
Check row exists
WHERE EXISTS (...)
Basic CTE
WITH cte AS (...)
Recursive CTE
WITH RECURSIVE cte AS

Subqueries

STDMySQLPG
intermediate
Subquery in WHERE
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
Subquery in FROM (derived table)
SELECT dept, avg_sal FROM ( SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department ) AS dept_stats WHERE avg_sal > 60000;
Correlated subquery
SELECT e.name, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department );
EXISTS: check if subquery returns rows
SELECT * FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );

CTEs (Common Table Expressions)

STDMySQLPG
intermediate
Basic CTE with WITH
1 2 3 4 5 6 7
WITH high_earners AS ( SELECT * FROM employees WHERE salary > 80000 ) SELECT department, COUNT(*) AS count FROM high_earners GROUP BY department;
Multiple CTEs
WITH active_users AS ( SELECT * FROM users WHERE status = 'active' ), recent_orders AS ( SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days' ) SELECT u.name, COUNT(o.id) AS order_count FROM active_users u JOIN recent_orders o ON u.id = o.user_id GROUP BY u.name;
Recursive CTE: tree traversal
WITH RECURSIVE org_chart AS ( -- anchor: top-level employees SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL -- recursive: subordinates SELECT e.id, e.name, e.manager_id, o.level + 1 FROM employees e JOIN org_chart o ON e.manager_id = o.id ) SELECT * FROM org_chart ORDER BY level;

DDL (Data Definition Language)

Quick reference
Create a table
CREATE TABLE t (...)
Add a column
ALTER TABLE ... ADD COLUMN
Add an index
CREATE INDEX ... ON
Remove a table
DROP TABLE IF EXISTS

Tables

STDMySQLPG
beginner
Create a table
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Auto-increment primary key
-- PostgreSQL CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL ); -- MySQL CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL );
Create table only if it doesn't exist
CREATE TABLE IF NOT EXISTS categories ( id INT PRIMARY KEY, name VARCHAR(50) );
Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
Drop a column
ALTER TABLE users DROP COLUMN phone;
Rename a column
ALTER TABLE users RENAME COLUMN name TO full_name;
Drop a table
DROP TABLE old_logs; DROP TABLE IF EXISTS old_logs;
Add a foreign key
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
Create an index
CREATE INDEX idx_users_email ON users(email); CREATE UNIQUE INDEX idx_users_email ON users(email);

Views

STDMySQLPG
intermediate
Create a view
CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE status = 'active';
Replace / update a view
CREATE OR REPLACE VIEW active_users AS SELECT id, name, email, created_at FROM users WHERE status = 'active';
Drop a view
DROP VIEW IF EXISTS active_users;

DML (Data Manipulation Language)

Quick reference
Insert a row
INSERT INTO t VALUES
Update rows
UPDATE t SET col = val
Delete rows
DELETE FROM t WHERE
Wrap in transaction
BEGIN; ...; COMMIT;

INSERT

STDMySQLPG
beginner
Insert a single row
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
Insert multiple rows
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'), ('Bob', '[email protected]'), ('Carol', '[email protected]');
Insert from SELECT
INSERT INTO archived_orders (id, total, created_at) SELECT id, total, created_at FROM orders WHERE created_at < '2023-01-01';
Upsert - insert or update on conflict
-- PostgreSQL INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email; -- MySQL INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]') ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

UPDATE & DELETE

STDMySQLPG
beginner
Update rows
UPDATE users SET status = 'inactive', updated_at = NOW() WHERE last_login < '2024-01-01';
Delete specific rows
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';
Delete all rows (keep table structure)
DELETE FROM temp_table; -- or faster: TRUNCATE TABLE temp_table;
Transactions
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- or ROLLBACK; to undo

Functions

Quick reference
Uppercase text
UPPER(col)
Current datetime
NOW()
Null fallback
COALESCE(col, default)
Convert type
CAST(col AS type)

String Functions

STDMySQLPG
beginner
UPPER / LOWER
UPPER('hello') -- 'HELLO' LOWER('HELLO') -- 'hello'
LENGTH / LEN
LENGTH('hello') -- 5
TRIM: remove leading/trailing whitespace
TRIM(' hello ') -- 'hello' LTRIM(' hello') -- 'hello' RTRIM('hello ') -- 'hello'
SUBSTRING: extract part of a string
SUBSTRING('hello world', 1, 5) -- 'hello'
REPLACE: substitute characters
REPLACE('hello world', 'world', 'SQL') -- 'hello SQL'
POSITION / INSTR: find substring position
POSITION('@' IN '[email protected]') -- 5
REGEXP_REPLACE (PostgreSQL)
REGEXP_REPLACE('phone: 123-456', '[0-9-]', '', 'g') -- 'phone: '

Date & Time Functions

STDMySQLPG
beginner
Current date and time
NOW() -- current datetime CURRENT_DATE -- current date only CURRENT_TIME -- current time only
Date arithmetic
-- PostgreSQL NOW() + INTERVAL '7 days' NOW() - INTERVAL '1 month' -- MySQL DATE_ADD(NOW(), INTERVAL 7 DAY) DATE_SUB(NOW(), INTERVAL 1 MONTH)
Extract date parts
EXTRACT(YEAR FROM created_at) EXTRACT(MONTH FROM created_at) EXTRACT(DAY FROM created_at)
DATE_TRUNC: truncate to period (PostgreSQL)
DATE_TRUNC('month', created_at) -- first day of month DATE_TRUNC('week', created_at)
DATE_FORMAT (MySQL)
DATE_FORMAT(created_at, '%Y-%m') -- '2024-01'
DATEDIFF: days between two dates
-- MySQL DATEDIFF('2024-12-31', '2024-01-01') -- 365 -- PostgreSQL '2024-12-31'::date - '2024-01-01'::date -- 365

Numeric Functions

STDMySQLPG
beginner
ROUND, CEIL, FLOOR
ROUND(3.14159, 2) -- 3.14 CEIL(3.2) -- 4 FLOOR(3.9) -- 3
ABS: absolute value
ABS(-42) -- 42
MOD: remainder
MOD(10, 3) -- 1 10 % 3 -- 1 (operator syntax)
CAST: convert data types
CAST('42' AS INT) CAST(price AS VARCHAR) -- PostgreSQL shorthand: '42'::int
SQL Formatter syntax highlighter
Input SQL

Transactions

Quick reference
Basic transaction
BEGIN; ... COMMIT;
Undo everything
ROLLBACK;
Create savepoint
SAVEPOINT sp1;
Lock a row
SELECT ... FOR UPDATE;

Basic Transaction Control

STDMySQLPG
beginner
Begin and commit a transaction
1 2 3 4 5
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
Rollback - undo all changes in the transaction
BEGIN; DELETE FROM orders WHERE status = 'pending'; ROLLBACK; -- nothing was deleted
START TRANSACTION (MySQL alias for BEGIN)
START TRANSACTION; UPDATE inventory SET stock = stock - 1 WHERE id = 5; COMMIT;
AUTOCOMMIT: disable to require explicit COMMIT
-- MySQL SET autocommit = 0; -- PostgreSQL SET autocommit TO off;

SAVEPOINTs

STDMySQLPG
intermediate
Create a savepoint mid-transaction
SAVEPOINT sp1;
Rollback to savepoint (keep earlier changes)
ROLLBACK TO SAVEPOINT sp1;
Release a savepoint
RELEASE SAVEPOINT sp1;
Full savepoint workflow
1 2 3 4 5 6 7 8
BEGIN; INSERT INTO log VALUES (1, 'step1'); SAVEPOINT after_log; DELETE FROM temp_data; -- risky step -- something went wrong: ROLLBACK TO SAVEPOINT after_log; -- log insert is still intact COMMIT;

Isolation Levels

STDMySQLPG
advanced
READ UNCOMMITTED - sees dirty reads (weakest)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Can read rows changed by uncommitted transactions
READ COMMITTED - no dirty reads (default in PG)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Only sees rows committed before each statement runs
REPEATABLE READ - no dirty or non-repeatable reads (default in MySQL)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Same row read twice in same transaction returns same data
SERIALIZABLE - strictest, no anomalies
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Transactions execute as if fully sequential; slowest
Set isolation level for next transaction only
BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM accounts; COMMIT;

Locking

STDMySQLPG
advanced
SELECT FOR UPDATE - lock rows for update
BEGIN; SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- other transactions can't modify this row until COMMIT
NOWAIT - fail immediately if lock can't be acquired
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
Raises error instead of waiting for lock
SKIP LOCKED - skip rows that are already locked
SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;
Useful for work queue patterns - each worker grabs its own row
SELECT FOR SHARE - shared lock (allow reads, block writes)
SELECT * FROM products WHERE id = 5 FOR SHARE;

Indexes

Quick reference
Basic index
CREATE INDEX ... ON
Unique index
CREATE UNIQUE INDEX
Composite index
INDEX ON (col1, col2)
Remove index
DROP INDEX

Creating Indexes

STDMySQLPG
beginner
Basic index on one column
CREATE INDEX idx_users_email ON users(email);
Unique index - prevents duplicate values
CREATE UNIQUE INDEX idx_users_email ON users(email);
Composite index - covers multiple columns
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
Column order matters: most selective column first
Partial index - index only a subset of rows (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
Smaller, faster index - only rows matching the WHERE
Covering index - INCLUDE extra columns without indexing them (PostgreSQL)
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total, status);
Query can be satisfied from index alone - no heap fetch
Index on expression (PostgreSQL)
CREATE INDEX idx_lower_email ON users(LOWER(email));
Speeds up: WHERE LOWER(email) = '[email protected]'
Create index without blocking reads/writes (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
Safe for production tables - takes longer but doesn't lock
Drop an index
DROP INDEX idx_users_email; -- PostgreSQL: DROP INDEX IF EXISTS idx_users_email;

Index Types & When NOT to Index

PG
advanced
B-tree (default) - equality, range, ORDER BY
CREATE INDEX idx_bt ON t(col) USING btree;
Best for: =, <, >, BETWEEN, LIKE 'prefix%'
Hash - equality only, faster for = lookups
CREATE INDEX idx_hash ON t(col) USING hash;
Best for: = only. Not useful for range queries
GIN - for JSONB, arrays, full-text search
CREATE INDEX idx_gin ON documents USING gin(data);
Best for: @>, <@, ?, ?|, ?& on jsonb/array columns
When NOT to add an index
-- Skip indexes on: -- Small tables (full scan is faster) -- Low-cardinality columns (status: 'active'/'inactive') -- Columns rarely used in WHERE/JOIN/ORDER BY -- Tables with heavy INSERT/UPDATE load -- (indexes slow down writes)
List existing indexes on a table
-- PostgreSQL SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users'; -- MySQL SHOW INDEX FROM users;

JSON

Quick reference
Get text value (PG)
data->>'key'
Get JSON value (PG)
data->'key'
Extract (MySQL)
JSON_EXTRACT(col, '$.key')
Contains check (PG)
data @> '{...}'

PostgreSQL JSON Operators

PG
intermediate
Extract JSON value as JSON object
SELECT data->'address' FROM users; -- returns JSON
Extract JSON value as text
SELECT data->>'name' FROM users; -- returns TEXT
Nested path access
SELECT data->'address'->>'city' FROM users; -- or with path operator: SELECT data#>>'{address,city}' FROM users;
Contains operator @> - check if JSONB contains value
SELECT * FROM users WHERE data @> '{"role": "admin"}';
? key exists operator
SELECT * FROM users WHERE data ? 'phone';
Returns rows where data has a 'phone' key
jsonb_array_elements - expand JSON array to rows
SELECT jsonb_array_elements(tags) AS tag FROM articles;
jsonb_each - expand object to key/value rows
SELECT key, value FROM users, jsonb_each(metadata);
Update a JSONB field
UPDATE users SET data = jsonb_set(data, '{address,city}', '"London"') WHERE id = 1;
Remove a key from JSONB
UPDATE users SET data = data - 'temp_token' WHERE id = 1;

MySQL JSON Functions

MySQL
intermediate
JSON_EXTRACT - get value by path
SELECT JSON_EXTRACT(data, '$.name') FROM users; -- shorthand: SELECT data->'$.name' FROM users;
Unquote operator ->> (returns plain string)
SELECT data->>'$.name' FROM users; -- same as JSON_UNQUOTE(JSON_EXTRACT(...))
JSON_SET - update or insert a value
UPDATE users SET data = JSON_SET(data, '$.city', 'London') WHERE id = 1;
JSON_ARRAYAGG - aggregate rows into JSON array
SELECT user_id, JSON_ARRAYAGG(product_name) AS products FROM order_items GROUP BY user_id;
JSON_OBJECTAGG - aggregate to JSON object
SELECT JSON_OBJECTAGG(code, name) AS countries FROM country_table;
JSON_CONTAINS - check if path contains value
SELECT * FROM users WHERE JSON_CONTAINS(data, '"admin"', '$.roles');

EXPLAIN / Query Performance

Quick reference
Show query plan
EXPLAIN SELECT ...
Run + measure (PG)
EXPLAIN ANALYZE
JSON output (MySQL)
EXPLAIN FORMAT=JSON
Buffer stats (PG)
EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN Basics

STDMySQLPG
intermediate
EXPLAIN - show query execution plan without running it
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
Shows: Seq Scan or Index Scan, estimated cost, rows
EXPLAIN ANALYZE - run query and show actual timings (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;
Shows actual vs estimated rows - key for spotting bad estimates
EXPLAIN with buffer and verbose options (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name;
BUFFERS shows cache hits vs disk reads - critical for I/O tuning
EXPLAIN FORMAT=JSON (MySQL - more detail)
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42;
EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

Reading EXPLAIN Output

PGMySQL
advanced
Key terms in PostgreSQL EXPLAIN output
-- Seq Scan -- full table scan (no index used) -- Index Scan -- index used, then fetch from heap -- Index Only Scan-- index covers all needed columns -- Bitmap Scan -- efficient for many matching rows -- Hash Join -- join via hash table (unsorted) -- Nested Loop -- join via nested iteration -- Merge Join -- join on pre-sorted inputs -- cost=X..Y -- X=startup cost, Y=total cost -- rows=N -- estimated row count -- actual rows=N -- real row count (ANALYZE only)
Key columns in MySQL EXPLAIN output
-- type: ALL=full scan, ref=index lookup, const=best -- key: index used (NULL = no index) -- rows: estimated rows examined -- Extra: Using filesort, Using temporary = warnings -- Using index = covering index (fast)
Update table statistics (helps planner)
-- PostgreSQL ANALYZE users; VACUUM ANALYZE users; -- MySQL ANALYZE TABLE users;

Constraints

Quick reference
Require a value
NOT NULL
Prevent duplicates
UNIQUE
Validate value
CHECK (...)
Fallback value
DEFAULT value

Column Constraints

STDMySQLPG
beginner
NOT NULL - column must have a value
name VARCHAR(100) NOT NULL
UNIQUE - no duplicate values allowed
email VARCHAR(255) UNIQUE
DEFAULT - fallback when no value is provided
status VARCHAR(20) DEFAULT 'active' created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
CHECK - validate value with a condition
price DECIMAL(10,2) CHECK (price > 0), age INT CHECK (age BETWEEN 0 AND 150), status VARCHAR(20) CHECK (status IN ('active','inactive'))
PRIMARY KEY - uniquely identifies each row
id INT PRIMARY KEY -- or as table constraint: PRIMARY KEY (order_id, product_id)
FOREIGN KEY - enforces referential integrity
user_id INT REFERENCES users(id) ON DELETE CASCADE -- ON DELETE options: CASCADE, SET NULL, RESTRICT, NO ACTION

Table Constraints & Generated Columns

STDMySQLPG
intermediate
Named constraint - easier to drop or reference
CREATE TABLE orders ( id INT PRIMARY KEY, total DECIMAL(10,2), CONSTRAINT chk_positive_total CHECK (total > 0) );
Add constraint to existing table
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0); ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
Drop a named constraint
ALTER TABLE products DROP CONSTRAINT chk_price;
Generated / computed column (PostgreSQL)
CREATE TABLE order_items ( price DECIMAL(10,2), quantity INT, total DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) STORED );
Column value computed automatically; cannot be inserted directly
Exclude constraint - prevent overlapping ranges (PostgreSQL)
CREATE TABLE bookings ( room_id INT, period TSRANGE, EXCLUDE USING GIST (room_id WITH =, period WITH &&) );
Prevents two bookings for the same room in overlapping time periods

Most developers don't memorize every SQL command. They keep a reliable SQL cheat sheet close by instead.

And honestly? That's the smarter move.

Whether you're writing a SELECT statement for the first time or trying to remember the exact syntax for a window function at 11pm before a deadline, having a solid reference saves time. SQL covers a lot of ground, from basic data retrieval and JOIN operations to aggregate functions, subqueries, and CTEs.

This guide covers the most-used SQL commands across MySQL, PostgreSQL, and SQL Server, with clean syntax examples you can actually copy and use.

No filler. Just the queries you need.

What is SQL

SQL (Structured Query Language) is a programming language for managing and querying data in relational databases.

It lets you retrieve, insert, update, and delete records, create and modify table structures, and control access permissions, all within a single standardized language.

Every major back-end development stack touches SQL in some form. PostgreSQL, MySQL, Microsoft SQL Server, Oracle, SQLite - they all use it.

SQL runs on a simple idea: you describe what data you want, not how to get it. The database engine handles the rest.

SQL Command Categories

SQL commands split into four groups. Each group has a distinct job.

DDL Commands (Data Definition Language)

DDL defines and modifies database structure. Think schemas, tables, indexes.

DDL changes are usually auto-committed. No ROLLBACK after a DROP.

DML Commands (Data Manipulation Language)

DML handles the actual data inside tables. This is what you'll use most.

SELECT is technically read-only, but it's grouped here since it operates on table data.

DCL Commands (Data Control Language)

DCL manages permissions and access. Two commands cover everything.

Relevant in any multi-user database setup. Critical in production environments where software security is a concern.

TCL Commands (Transaction Control Language)

TCL groups multiple DML operations into a single unit of work.

This is where ACID properties (Atomicity, Consistency, Isolation, Durability) actually kick in.

SQL Data Types

Data types define what kind of value a column can store. Getting this right affects storage, performance, and query behavior.

Numeric Data Types

Type

Use

INT / INTEGER

Whole numbers

BIGINT

Large whole numbers

DECIMAL(p, s)

Fixed-point numbers, exact precision

FLOAT / REAL

Approximate decimal values

NUMERIC

Like DECIMAL, preferred for financial data

FLOAT is tricky for currency. Use DECIMAL or NUMERIC when precision matters.

String Data Types

Type

Use

CHAR(n)

Fixed-length string, always pads to n

VARCHAR(n)

Variable-length string, up to n characters

TEXT

Unlimited-length string (PostgreSQL/MySQL)

NVARCHAR(n)

Unicode variable-length (SQL Server)

CHAR is faster for fixed-width data like country codes. VARCHAR is the default for most use cases.

Date and Time Data Types

Type

Use

DATE

Year, month, day only

TIME

Time of day only

DATETIME

Date + time (MySQL, SQL Server)

TIMESTAMP

Date + time, often auto-updated

INTERVAL

Duration between two points (PostgreSQL)

Dialect differences matter here. MySQL uses DATETIME; PostgreSQL prefers TIMESTAMP WITH TIME ZONE.

Boolean and NULL

BOOLEAN stores TRUE or FALSE. MySQL uses TINYINT(1) as a stand-in. SQL Server uses BIT.

NULL is not zero. Not an empty string. NULL means "unknown" or "no value." Comparisons with NULL always return NULL, never TRUE or FALSE, which catches a lot of developers off guard.

Use IS NULL and IS NOT NULL, not = NULL.

SQL Syntax Rules

A few rules that apply across all SQL dialects.

Case sensitivity - SQL keywords are case-insensitive. SELECT and select work the same. Column and table names may or may not be case-sensitive depending on the database and OS.

Statements end with a semicolon (;). Required in multi-statement scripts; optional for single queries in many tools.

Whitespace doesn't matter. You can break a query across multiple lines freely.

Two comment styles:

-- Single-line comment

/* Multi-line
   comment */

String values use single quotes: 'value'. Double quotes are for identifiers (table/column names) in most databases. SQL Server uses square brackets: [column name].

Reserved words used as column names need quoting. Best practice: don't name columns order, select, table, or other SQL keywords.

SELECT Statement

SELECT is the most-used command in SQL. It retrieves rows from one or more tables.

Basic syntax: 

SELECT column1, column2
FROM table_name;

Use * to select all columns - fine for exploring, bad for production queries. Always specify columns explicitly in real code.

SELECT with WHERE Clause

WHERE filters which rows come back.

SELECT first_name, last_name
FROM employees
WHERE department = 'Engineering';

The WHERE clause runs before SELECT, so you can filter on columns you're not selecting.

SELECT with ORDER BY

ORDER BY sorts the result set. Default is ascending (ASC); add DESC to flip it.

SELECT product_name, price
FROM products
ORDER BY price DESC;

You can sort by multiple columns: ORDER BY category ASC, price DESC.

SELECT with LIMIT and OFFSET

LIMIT caps how many rows are returned. OFFSET skips rows before starting.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

SQL Server uses TOP instead of LIMIT. Oracle uses ROWNUM or FETCH FIRST n ROWS ONLY.

LIMIT without ORDER BY returns rows in unpredictable order. Always pair them.

SELECT DISTINCT

DISTINCT removes duplicate rows from the result.

SELECT DISTINCT country
FROM customers;

DISTINCT applies to the entire row, not just one column. SELECT DISTINCT first_name, last_name returns unique combinations, not unique first names alone.

Heavy use of DISTINCT often signals a join problem worth fixing.

SQL Filtering Conditions

Filtering is how you narrow down query results. SQL gives you a full set of operators for this.

Comparison Operators

Operator

Meaning

=

Equal to

<> or !=

Not equal to

> / <

Greater / less than

>= / <=

Greater or equal / less or equal

Standard across all dialects. Use <> for maximum compatibility; != works in most databases but isn't ANSI SQL.

BETWEEN, IN, LIKE, IS NULL

BETWEEN filters within a range, inclusive on both ends:

WHERE salary BETWEEN 50000 AND 80000

IN matches against a list of values:

WHERE country IN ('US', 'CA', 'UK')

LIKE matches string patterns. Two wildcards: % (any number of characters), _ (exactly one character):

WHERE email LIKE '%@gmail.com'

IS NULL checks for missing values. Never use = NULL - it always returns false.

AND, OR, NOT Logic

Combine multiple conditions with AND, OR, NOT. Use parentheses to control evaluation order.

WHERE (status = 'active' AND country = 'US')
   OR role = 'admin'

Without parentheses, AND evaluates before OR. Explicit grouping prevents logic bugs.

SQL Aggregate Functions

Aggregate functions collapse multiple rows into a single value.

Function

Returns

COUNT(*)

Total number of rows

COUNT(column)

Rows where column is not NULL

SUM(column)

Total of all values

AVG(column)

Mean value

MIN(column)

Lowest value

MAX(column)

Highest value

All aggregate functions ignore NULL values except COUNT(*).

GROUP BY

GROUP BY segments rows into groups so aggregate functions run per group, not across the whole table.

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Every column in SELECT that isn't an aggregate must appear in GROUP BY. Skipping this causes errors in strict SQL modes.

HAVING vs WHERE

WHERE filters rows before grouping. HAVING filters groups after aggregation.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Can't use aggregate functions in a WHERE clause. That's what HAVING is for.

SQL JOINs

JOINs combine rows from two or more tables based on a related column.

INNER JOIN

Returns only rows with matching values in both tables. Non-matching rows from either side are dropped.

SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Most common join type. If a row has no match, it disappears from results.

LEFT JOIN

Returns all rows from the left table, with matched rows from the right. Unmatched right-side columns return NULL.

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Good for finding customers who have never placed an order: WHERE o.order_id IS NULL.

RIGHT JOIN

Mirror of LEFT JOIN - keeps all rows from the right table. Rarely used; most developers rewrite as a LEFT JOIN with tables swapped for readability.

FULL OUTER JOIN

Returns all rows from both tables. NULLs fill in where there's no match on either side.

Not supported in MySQL natively. Workaround: LEFT JOIN + UNION + RIGHT JOIN.

CROSS JOIN

Returns every combination of rows from both tables. No ON condition.

SELECT * FROM colors CROSS JOIN sizes;

Produces a Cartesian product. Useful for generating test data or combination matrices. Dangerous on large tables.

Self JOIN

A table joined to itself. Useful for hierarchical data like org charts or parent-child relationships.

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

Always use aliases when self-joining, or the query won't parse.

SQL Subqueries

A subquery is a SELECT statement nested inside another query.

Subquery in WHERE

Filters based on results from another query.

SELECT name FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'NYC'
);

Can often be rewritten as a JOIN. JOINs are usually faster on large datasets.

Subquery in FROM

Treats the subquery result as a temporary table. Also called a derived table.

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

Must be aliased in most databases.

Correlated Subquery

Runs once per row of the outer query, referencing the outer query's columns.

SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE department = e.department
);

Powerful but slow at scale. If the outer table has 100k rows, the subquery runs 100k times.

Common Table Expressions (CTEs)

A CTE uses the WITH clause to define a named, temporary result set that you can reference in the main query.

WITH high_earners AS (
  SELECT name, salary FROM employees WHERE salary > 80000
)
SELECT * FROM high_earners ORDER BY salary DESC;

CTEs are cleaner than nested subqueries and easier to debug. They don't improve performance on their own - they're a readability tool.

Recursive CTEs handle hierarchical data like category trees or org charts:

WITH RECURSIVE org AS (
  SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

PostgreSQL, SQL Server, and MySQL 8+ support recursive CTEs. MySQL 5.x does not.

Window Functions

Window functions run calculations across a set of rows related to the current row, without collapsing them into a single result like aggregates do.

Basic syntax:

function_name() OVER (
  PARTITION BY column
  ORDER BY column
)

PARTITION BY divides rows into groups. ORDER BY sets the order within each partition. Both are optional.

ROW_NUMBER, RANK, DENSE_RANK

All three assign a number to each row within a partition.

Function

Ties

ROW_NUMBER()

No ties, always unique

RANK()

Ties get the same rank; next rank skips

DENSE_RANK()

Ties get the same rank; next rank does not skip

Use ROW_NUMBER() for pagination. Use DENSE_RANK() when gaps in ranking are confusing to end users.

LAG and LEAD

LAG accesses the value from a previous row; LEAD accesses the next row - without a self-join.

SELECT order_date,
       revenue,
       LAG(revenue) OVER (ORDER BY order_date) AS prev_revenue
FROM monthly_sales;

Offset defaults to 1 row. You can pass a second argument to go back/forward multiple rows.

SUM and AVG as Window Functions

Standard aggregate functions work as window functions when paired with OVER().

SELECT name, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

Returns the department total on every row, not a single summary row. Useful for calculating percentages: salary / SUM(salary) OVER (PARTITION BY department).

SQL String Functions

Core string functions across MySQL, PostgreSQL, and SQL Server.

Function

What it does

CONCAT(a, b)

Joins strings together

LENGTH(str)

Returns character count (LEN in SQL Server)

UPPER(str) / LOWER(str)

Changes case

TRIM(str)

Removes leading and trailing spaces

SUBSTRING(str, start, len)

Extracts part of a string

REPLACE(str, from, to)

Swaps one substring for another

CHARINDEX / POSITION

Finds position of a substring

PostgreSQL uses || for concatenation instead of CONCAT(), though CONCAT() also works.

TRIM only removes spaces by default. To remove specific characters, use LTRIM/RTRIM with a character argument in SQL Server, or TRIM('x' FROM str) in PostgreSQL.

SQL Date and Time Functions

Date handling varies more across dialects than almost anything else in SQL.

Operation

MySQL

PostgreSQL

SQL Server

Current date+time

NOW()

NOW()

GETDATE()

Current date

CURDATE()

CURRENT_DATE

CAST(GETDATE() AS DATE)

Add to date

DATE_ADD(d, INTERVAL n unit)

d + INTERVAL 'n unit'

DATEADD(unit, n, d)

Difference

DATEDIFF(a, b)

a - b (returns interval)

DATEDIFF(unit, a, b)

Format date

DATE_FORMAT(d, fmt)

TO_CHAR(d, fmt)

FORMAT(d, fmt)

When storing timestamps for app deployment logs or audit trails, always use UTC. Convert to local time in the application layer, not in SQL.

SQL Table Operations

CREATE TABLE with Constraints

CREATE TABLE users (
  id        INT          PRIMARY KEY AUTO_INCREMENT,
  email     VARCHAR(255) NOT NULL UNIQUE,
  status    VARCHAR(20)  DEFAULT 'active',
  created_at TIMESTAMP   DEFAULT CURRENT_TIMESTAMP
);

Constraints available inline: NOT NULL, UNIQUE, DEFAULT, CHECK. Define PRIMARY KEY and FOREIGN KEY inline for single columns, or as table-level constraints for composite keys.

PRIMARY KEY and FOREIGN KEY

Primary key uniquely identifies each row; one per table; cannot be NULL.

A foreign key links a column to the primary key of another table, enforcing referential integrity:

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE;

ON DELETE CASCADE deletes child rows when the parent is deleted. ON DELETE RESTRICT blocks the delete if children exist.

ALTER TABLE

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Change a column type
ALTER TABLE users MODIFY COLUMN status CHAR(10);  -- MySQL
ALTER TABLE users ALTER COLUMN status TYPE CHAR(10);  -- PostgreSQL

-- Rename a column
ALTER TABLE users RENAME COLUMN status TO account_status;  -- PostgreSQL/MySQL 8+

DROP vs TRUNCATE vs DELETE

Command

Removes

Rollback

Speed

DELETE

Selected rows

Yes

Slow (logs each row)

TRUNCATE

All rows

No (usually)

Fast

DROP

Entire table + structure

No

Instant

TRUNCATE resets auto-increment counters. DELETE does not.

SQL INSERT, UPDATE, DELETE

INSERT INTO

Insert one row:

INSERT INTO users (name, email) VALUES ('Sara', 'sara@example.com');

Insert multiple rows in one statement:

INSERT INTO users (name, email) VALUES
  ('Tom', 'tom@example.com'),
  ('Ana', 'ana@example.com');

Copy rows from another table: INSERT INTO archive SELECT * FROM orders WHERE status = 'closed'.

UPDATE with WHERE

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

Always include a WHERE clause. An UPDATE without WHERE modifies every row in the table. Test your WHERE condition with a SELECT first.

DELETE with WHERE

DELETE FROM sessions
WHERE last_active < NOW() - INTERVAL 30 DAY;

Same rule as UPDATE: missing WHERE deletes all rows. Use LIMIT in MySQL to cap how many rows get deleted in one run.

SQL Indexes

An index speeds up reads by creating a separate data structure the query optimizer can scan instead of the full table.

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

-- Create a unique index
CREATE UNIQUE INDEX idx_email ON users (email);

-- Drop an index
DROP INDEX idx_email ON users;  -- MySQL
DROP INDEX idx_email;           -- PostgreSQL

Index every column used in WHERE, JOIN ON, and ORDER BY clauses on large tables.

Downsides: indexes slow down INSERT, UPDATE, and DELETE because the index must be updated too. Don't index every column reflexively.

Composite indexes work left-to-right. An index on (last_name, first_name) helps queries filtering by last_name alone, but not first_name alone.

SQL Views

A view is a saved SELECT query treated as a virtual table.

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Query it like a table
SELECT * FROM active_users;

Views don't store data. They re-run the underlying query every time they're accessed.

Simple views are often updatable (you can INSERT/UPDATE through them). Views with JOIN, GROUP BY, DISTINCT, or aggregate functions are read-only.

Drop a view: DROP VIEW active_users;

SQL Transactions

A transaction groups multiple SQL statements so they either all succeed or all fail.

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

If the second UPDATE fails, ROLLBACK undoes the first one too. Nothing partial makes it to the database.

SAVEPOINT lets you roll back to a specific point without canceling the entire transaction:

SAVEPOINT before_update;
-- do some work
ROLLBACK TO SAVEPOINT before_update;

This maps directly to ACID properties: Atomicity (all or nothing), Consistency (valid state after), Isolation (concurrent transactions don't interfere), Durability (committed data persists).

SQL Performance and Query Optimization

Slow queries are usually fixable. Most performance problems fall into a small set of patterns.

Using EXPLAIN

EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) shows how the database executes a query without running it.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Look for Seq Scan on large tables - that means no index is being used. Index Scan or Index Seek is what you want.

Index Optimization Tips

Common Query Performance Mistakes

The code review process should catch unindexed queries before they reach production.

SQL Dialect Differences

Standard SQL exists, but every database extends it differently. What works in one might fail in another.

MySQL vs PostgreSQL Differences

Feature

MySQL

PostgreSQL

String concat

CONCAT(a, b)

a || b or CONCAT(a, b)

Auto-increment

AUTO_INCREMENT

SERIAL or GENERATED ALWAYS AS IDENTITY

Limit rows

LIMIT n

LIMIT n

Boolean type

TINYINT(1)

Native BOOLEAN

Full-text search

MATCH ... AGAINST

tsvector / tsquery

JSON support

JSON (MySQL 5.7+)

JSON / JSONB (more powerful)

PostgreSQL is stricter about SQL standards. MySQL is more forgiving with implicit conversions, which can hide bugs.

SQL Server (T-SQL) Specific Syntax

SQL Server uses T-SQL, Microsoft's SQL extension.

Key differences:

The web development IDE you use often determines which SQL dialect you interact with most.

SQLite Limitations

SQLite is file-based, serverless, and intentionally minimal. Common in mobile application development and database for mobile apps scenarios.

What SQLite doesn't support:

Fine for development and embedded use. Not for high-concurrency production workloads.

FAQ on SQL Cheat Sheets

What SQL commands should a beginner learn first?

Start with SELECT, WHERE, INSERT, UPDATE, and DELETE. These five DML commands cover most real-world tasks. Add JOIN and GROUP BY once the basics feel solid. Everything else builds on this foundation.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters after aggregation. You can't use aggregate functions like COUNT() or SUM() in a WHERE clause - that's what HAVING exists for.

When should I use a JOIN instead of a subquery?

JOINs are generally faster and easier to read. Use subqueries when the logic genuinely needs a nested result set, like correlated subqueries. For simple lookups across tables, an INNER JOIN or LEFT JOIN is almost always the better call.

What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes selected rows and supports ROLLBACK. TRUNCATE wipes all rows fast, no logging. DROP deletes the entire table structure. Use each at the right level - row, data, or schema.

How do window functions differ from aggregate functions?

Aggregate functions collapse rows into one result. Window functions keep every row intact and calculate across a related set using OVER(). Use them for running totals, rankings, or comparing each row to a group average.

What SQL data type should I use for currency?

Use DECIMAL or NUMERIC with explicit precision and scale, like DECIMAL(10, 2). Never use FLOAT for money - floating-point arithmetic introduces rounding errors that compound over time in financial calculations.

What is a CTE and when should I use it?

A Common Table Expression uses the WITH clause to name a temporary result set. Use it to break complex queries into readable steps. It doesn't improve performance on its own, but it makes nested subquery logic far easier to debug.

Why is SELECT * bad practice in production?

It pulls every column, including ones you don't need, which wastes I/O and memory. It also breaks silently when table schemas change. Always list specific columns in production SQL queries.

What causes an SQL query to run slowly?

Missing indexes on WHERE and JOIN columns are the most common cause. Other culprits: SELECT *, leading wildcards in LIKE, functions applied to indexed columns, and correlated subqueries running once per row on large tables.

Do SQL syntax rules differ between MySQL, PostgreSQL, and SQL Server?

Yes. Key differences include LIMIT vs TOP, AUTO_INCREMENT vs SERIAL, date functions like NOW() vs GETDATE(), and string concatenation (CONCAT vs ||). Core DML commands are consistent; functions and extensions vary significantly by dialect.