SQL

SELECT * FROM table_name;

SELECT * FROM users;

-- Returns all columns and rows from the users table

Note Avoid SELECT * in production code. Always specify columns to reduce data transfer and prevent breakage when table schema changes.

SELECT column1, column2 FROM table_name;

SELECT first_name, email FROM users;

-- first_name | email
-- Alice      | [email protected]
-- Bob        | [email protected]

Note Listing columns explicitly improves readability and query performance since the database only fetches what you need.

SELECT columns FROM table WHERE condition;

SELECT first_name, email
FROM users
WHERE is_active = true;

-- Only rows where is_active is true

Note WHERE filters rows before any grouping occurs. Use HAVING to filter after GROUP BY.

SELECT columns FROM table ORDER BY column [ASC|DESC];

SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Products sorted from most expensive to cheapest

Note ASC is the default direction. You can sort by multiple columns: ORDER BY category ASC, price DESC. Sorting happens after WHERE filtering.

SELECT columns FROM table LIMIT count OFFSET skip;

SELECT product_name, price
FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- Returns rows 21-30 (skips first 20, then takes 10)

Note MySQL/PostgreSQL use LIMIT. SQL Server uses TOP or FETCH FIRST. High OFFSET values degrade performance because the database still scans skipped rows.

SELECT DISTINCT column FROM table;

SELECT DISTINCT city
FROM users
ORDER BY city;

-- Returns each city only once, no duplicates

Note DISTINCT applies to the entire row when used with multiple columns. SELECT DISTINCT city, state treats (city, state) pairs as the unit of uniqueness.

SELECT column AS alias_name FROM table AS t;

SELECT
  u.first_name AS name,
  o.total_amount AS order_total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id;

-- name  | order_total
-- Alice | 149.99

Note AS is optional in most databases but improves clarity. You cannot reference a column alias in the WHERE clause of the same query — use a subquery or CTE instead.

SELECT expression AS alias FROM table;

SELECT
  product_name,
  price,
  quantity,
  price * quantity AS line_total
FROM order_items;

-- product_name | price | quantity | line_total
-- Widget       | 25.00 | 3        | 75.00

Note You can use arithmetic operators (+, -, *, /), string functions, and CASE expressions directly in the SELECT list.

-- PostgreSQL / SQL Server
SELECT columns INTO new_table FROM source_table;
-- MySQL
CREATE TABLE new_table AS SELECT columns FROM source_table;

CREATE TABLE active_users AS
SELECT id, first_name, email
FROM users
WHERE is_active = true;

-- Creates a new table active_users with matching rows

Note This copies data but not indexes or constraints. MySQL uses CREATE TABLE ... AS SELECT, while PostgreSQL and SQL Server support SELECT ... INTO.

SELECT columns FROM table
ORDER BY column
FETCH FIRST n ROWS ONLY;

SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY;

-- Skips 5, returns next 10 most expensive products

Note FETCH FIRST is the ANSI SQL standard way to limit results. PostgreSQL supports both FETCH FIRST and LIMIT. MySQL only supports LIMIT. Use FETCH FIRST for maximum portability.

WHERE column = | <> | < | > | <= | >= value

SELECT product_name, price
FROM products
WHERE price >= 50.00 AND price < 200.00;

-- Products priced from 50 up to (not including) 200

Note Use <> for not-equal (ANSI standard). != works in MySQL and PostgreSQL but is not part of the standard.

WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition

SELECT first_name, city, is_active
FROM users
WHERE (city = 'Seattle' OR city = 'Portland')
  AND NOT is_active = false;

-- Active users in Seattle or Portland

Note AND binds tighter than OR. Always use parentheses to make precedence explicit. WHERE a OR b AND c means WHERE a OR (b AND c), which trips people up.

WHERE column IN (value1, value2, ...)

SELECT order_id, status
FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

-- Orders with any of the three listed statuses

Note IN is shorthand for multiple OR conditions. If the list contains a NULL, it will not match NULL rows — use IS NULL separately. You can also use a subquery inside IN.

WHERE column NOT IN (value1, value2, ...)

SELECT product_name
FROM products
WHERE category_id NOT IN (5, 12, 18);

-- Products not in categories 5, 12, or 18

Note Dangerous with NULLs: if any value in the list is NULL, NOT IN returns no rows at all. This is because NULL comparisons yield UNKNOWN, and NOT UNKNOWN is still UNKNOWN.

WHERE column BETWEEN low AND high

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

-- Orders placed during the entire year 2025

Note BETWEEN is inclusive on both ends. For timestamps, BETWEEN '2025-01-01' AND '2025-12-31' misses anything after midnight on Dec 31. Use < '2026-01-01' instead for date ranges with times.

WHERE column LIKE 'pattern'
-- % = any number of characters
-- _ = exactly one character

SELECT first_name, email
FROM users
WHERE email LIKE '%@gmail.com';

-- All users with Gmail addresses

Note LIKE is case-sensitive in PostgreSQL but case-insensitive in MySQL (with default collation). PostgreSQL offers ILIKE for case-insensitive matching. A leading % prevents index usage.

WHERE column IS NULL
WHERE column IS NOT NULL

SELECT first_name, phone
FROM users
WHERE phone IS NOT NULL;

-- Users who have provided a phone number

Note Never use = NULL or <> NULL. These always evaluate to UNKNOWN and return no rows. NULL is not a value — it represents the absence of a value, so only IS NULL / IS NOT NULL work.

WHERE EXISTS (subquery)

SELECT u.first_name, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
  AND o.total_amount > 500
);

-- Users who have at least one order over 500

Note EXISTS stops scanning as soon as it finds one matching row, making it efficient. It generally outperforms IN with large subquery results. The SELECT list inside EXISTS is irrelevant — SELECT 1 is conventional.

WHERE column > ANY (subquery)
WHERE column > ALL (subquery)

SELECT product_name, price
FROM products
WHERE price > ALL (
  SELECT AVG(price)
  FROM products
  GROUP BY category_id
);

-- Products priced above every category's average

Note ANY means the condition must be true for at least one value from the subquery. ALL means it must be true for every value. If the subquery returns an empty set, ALL conditions are true and ANY conditions are false.

WHERE CASE WHEN condition THEN result ... END

SELECT order_id, total_amount, customer_type
FROM orders
WHERE total_amount > CASE customer_type
  WHEN 'wholesale' THEN 1000
  WHEN 'retail' THEN 100
  ELSE 50
END;

-- Filters with different thresholds per customer type

Note You can use CASE in WHERE to apply different filter logic per row. However, this prevents index usage. Consider restructuring into separate OR conditions if performance matters.

SELECT columns
FROM table1
INNER JOIN table2 ON table1.col = table2.col;

SELECT u.first_name, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- Only users who have orders, paired with their order data

Note INNER JOIN returns only rows with matching values in both tables. Users with no orders and orders with no matching user are excluded. JOIN without a prefix defaults to INNER JOIN.

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.col = table2.col;

SELECT u.first_name, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- All users appear. Users with no orders show NULL for order columns.

Note LEFT JOIN keeps all rows from the left table regardless of matches. Filtering the right table in WHERE (instead of ON) converts the LEFT JOIN into an INNER JOIN. Put right-table filters in the ON clause instead.

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.col = table2.col;

SELECT o.order_id, u.first_name
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- All users appear. Orders without a matching user show NULL for order columns.

Note RIGHT JOIN is the mirror of LEFT JOIN. Most developers avoid RIGHT JOIN and rewrite it as a LEFT JOIN by swapping table order — it is easier to read consistently.

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.col = table2.col;

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

-- All users and all orders appear.
-- Unmatched rows on either side get NULLs.

Note MySQL does not support FULL OUTER JOIN natively. Simulate it with a UNION of a LEFT JOIN and a RIGHT JOIN. PostgreSQL and SQL Server support it directly.

SELECT columns
FROM table1
CROSS JOIN table2;

SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

-- Every combination of size and color
-- If 4 sizes and 5 colors, result has 20 rows

Note CROSS JOIN produces the Cartesian product — every row in table1 paired with every row in table2. Row count = rows_in_A * rows_in_B, so it can explode quickly on large tables.

SELECT a.col, b.col
FROM table a
JOIN table b ON a.related_col = b.id;

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

-- employee | manager
-- Alice    | Bob
-- Bob      | NULL  (Bob has no manager)

Note A self join joins a table to itself. You must use different aliases to distinguish the two references. Common for hierarchical data like org charts or threaded comments.

SELECT columns
FROM table1
JOIN table2 ON ...
JOIN table3 ON ...;

SELECT
  u.first_name,
  o.order_id,
  p.product_name,
  oi.quantity
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.id = oi.product_id;

-- Connects users to their orders, line items, and product details

Note Chain as many JOINs as needed. Each JOIN condition should reference a column from a table already in the query. Watch performance on many-table joins — the optimizer may struggle with 8+ tables.

SELECT columns
FROM table1
JOIN table2 USING (shared_column);

SELECT order_id, user_id, first_name
FROM orders
JOIN users USING (user_id);

-- Works when both tables share the same column name

Note USING is shorthand for ON when the join column has the same name in both tables. The shared column appears only once in the output. Not supported in SQL Server.

SELECT columns
FROM table1
NATURAL JOIN table2;

SELECT first_name, order_id
FROM users
NATURAL JOIN orders;

-- Joins on ALL columns with matching names in both tables

Note Avoid NATURAL JOIN in production. It implicitly joins on every shared column name, so adding a column like 'status' to both tables silently changes the join condition. Always use explicit ON or USING.

SELECT columns
FROM table1
LEFT JOIN table2 ON t1.col = t2.col AND t2.filter = value;

SELECT u.first_name, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
  AND o.status = 'completed';

-- All users. Only completed orders appear; others show NULL.

Note Putting the filter in ON vs WHERE matters for outer joins. In ON, unmatched left rows still appear with NULLs. In WHERE, those rows get eliminated, turning it into an inner join.

SELECT COUNT(*) FROM table;
SELECT COUNT(column) FROM table;
SELECT COUNT(DISTINCT column) FROM table;

SELECT
  COUNT(*) AS total_orders,
  COUNT(shipped_date) AS shipped_orders,
  COUNT(DISTINCT user_id) AS unique_customers
FROM orders;

-- total_orders | shipped_orders | unique_customers
-- 1500         | 1230           | 480

Note COUNT(*) counts all rows including NULLs. COUNT(column) skips NULLs. COUNT(DISTINCT column) counts unique non-NULL values. This distinction is a common source of bugs.

SELECT SUM(column), AVG(column) FROM table;

SELECT
  SUM(total_amount) AS revenue,
  AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= '2025-01-01';

-- revenue   | avg_order_value
-- 245890.50 | 163.93

Note Both SUM and AVG ignore NULL values. If all values are NULL, SUM returns NULL (not 0). Wrap with COALESCE(SUM(col), 0) if you need a zero default.

SELECT MIN(column), MAX(column) FROM table;

SELECT
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  MIN(created_at) AS first_product,
  MAX(created_at) AS latest_product
FROM products;

-- cheapest | most_expensive | first_product | latest_product
-- 4.99     | 2499.00        | 2023-03-15    | 2025-11-20

Note MIN and MAX work on numbers, strings, and dates. For strings, they use lexicographic ordering based on the column's collation.

SELECT column, aggregate(col)
FROM table
GROUP BY column;

SELECT
  category_id,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price
FROM products
GROUP BY category_id;

-- category_id | product_count | avg_price
-- 1           | 45            | 29.99
-- 2           | 32            | 89.50

Note Every non-aggregated column in SELECT must appear in GROUP BY (ANSI rule). MySQL historically allowed non-grouped columns with unpredictable results; enable ONLY_FULL_GROUP_BY mode to catch this.

SELECT column, aggregate(col)
FROM table
GROUP BY column
HAVING aggregate_condition;

SELECT
  user_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5;

-- Only users with 5 or more orders

Note HAVING filters groups after aggregation. WHERE filters rows before aggregation. A common mistake is using WHERE with aggregate functions — that is a syntax error.

SELECT col1, col2, aggregate(col)
FROM table
GROUP BY col1, col2;

SELECT
  EXTRACT(YEAR FROM order_date) AS order_year,
  status,
  COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), status
ORDER BY order_year, status;

-- order_year | status    | order_count
-- 2024       | completed | 890
-- 2024       | cancelled | 42
-- 2025       | completed | 1050

Note Grouping by multiple columns creates one group for each unique combination of values. The more columns you group by, the more granular (and more numerous) the groups become.

SELECT col1, col2, aggregate(col)
FROM table
GROUP BY ROLLUP(col1, col2);

SELECT
  category_id,
  brand,
  SUM(price * stock_qty) AS inventory_value
FROM products
GROUP BY ROLLUP(category_id, brand);

-- Includes subtotals per category and a grand total row
-- category_id | brand | inventory_value
-- 1           | Acme  | 5000
-- 1           | NULL  | 12000  (subtotal for category 1)
-- NULL         | NULL  | 45000  (grand total)

Note ROLLUP generates subtotals in a hierarchy. Use GROUPING() function to distinguish real NULLs from subtotal NULLs. MySQL supports WITH ROLLUP syntax; PostgreSQL uses ROLLUP().

SELECT col1, col2, aggregate(col)
FROM table
GROUP BY GROUPING SETS ((col1), (col2), ());

SELECT
  category_id,
  region,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
  (category_id, region),
  (category_id),
  (region),
  ()
);

-- Returns aggregations at every specified level
-- including per-category, per-region, and grand total

Note GROUPING SETS let you define exactly which grouping combinations you want, unlike ROLLUP which follows a strict hierarchy. Supported in PostgreSQL and SQL Server; MySQL uses ROLLUP only.

aggregate_function(col) FILTER (WHERE condition)

SELECT
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total_amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

-- total_orders | completed | cancelled | completed_revenue
-- 1500         | 1200      | 85        | 198450.00

Note FILTER is PostgreSQL-specific (ANSI SQL:2003). In MySQL, use SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as the equivalent pattern.

-- PostgreSQL / ANSI
STRING_AGG(column, delimiter)
-- MySQL
GROUP_CONCAT(column SEPARATOR delimiter)

-- PostgreSQL
SELECT
  order_id,
  STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY order_id;

-- order_id | products
-- 101      | Keyboard, Monitor, Mouse

Note STRING_AGG is standard SQL. MySQL uses GROUP_CONCAT with a default max length of 1024 characters — increase group_concat_max_len if results get truncated.

SELECT column, (SELECT single_value FROM ...) AS alias
FROM table;

SELECT
  product_name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- product_name | price  | diff_from_avg
-- Laptop       | 999.00 | 849.50
-- Cable         | 9.99   | -139.51

Note A scalar subquery must return exactly one row and one column. If it returns more than one row, the query fails. If it returns zero rows, the result is NULL.

WHERE column IN (SELECT column FROM ...)

SELECT first_name, email
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE total_amount > 500
);

-- Users who have placed at least one order over 500

Note For large datasets, EXISTS often performs better than IN with a subquery because EXISTS short-circuits on the first match. IN materializes the entire subquery result first.

SELECT columns
FROM table1 t1
WHERE column op (
  SELECT aggregate(col) FROM table2 t2
  WHERE t2.ref = t1.id
);

SELECT product_name, price, category_id
FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category_id = p.category_id
);

-- Products priced above their own category's average

Note A correlated subquery references the outer query and executes once per outer row. This can be slow on large tables. Consider rewriting as a JOIN with a derived table for better performance.

SELECT columns
FROM (SELECT ... FROM ...) AS alias;

SELECT
  top_customers.first_name,
  top_customers.total_spent
FROM (
  SELECT u.first_name, SUM(o.total_amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.first_name
  HAVING SUM(o.total_amount) > 1000
) AS top_customers
ORDER BY top_customers.total_spent DESC;

-- first_name | total_spent
-- Alice      | 4520.00
-- Bob        | 2310.50

Note Derived tables (subqueries in FROM) must have an alias. They are computed once and treated like a temporary table. CTEs (WITH clause) are generally more readable for the same purpose.

WHERE EXISTS (SELECT 1 FROM table WHERE condition)

SELECT c.category_name
FROM categories c
WHERE NOT EXISTS (
  SELECT 1 FROM products p
  WHERE p.category_id = c.id
);

-- Categories that have no products at all

Note NOT EXISTS is the safest way to find missing related rows. Unlike NOT IN, it handles NULLs correctly and will not produce unexpected empty results.

WHERE column > ANY (subquery)
WHERE column > ALL (subquery)

SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 3
);

-- Employees earning more than everyone in department 3

Note ANY means 'at least one' — the condition must hold for at least one row from the subquery. ALL means 'every' — it must hold for all rows. An empty subquery makes ALL true and ANY false.

SELECT columns
FROM table1 t1
CROSS JOIN LATERAL (
  SELECT ... FROM table2 WHERE ref = t1.id LIMIT n
) AS sub;

SELECT u.first_name, recent.order_id, recent.total_amount
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, total_amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY order_date DESC
  LIMIT 3
) AS recent;

-- Each user paired with their 3 most recent orders

Note LATERAL lets a subquery reference columns from preceding tables in the FROM clause. It is the SQL equivalent of a for-each loop. PostgreSQL supports LATERAL; MySQL 8+ supports LATERAL derived tables.

INSERT INTO target (columns)
SELECT columns FROM source WHERE condition;

INSERT INTO archived_orders (order_id, user_id, total_amount, order_date)
SELECT order_id, user_id, total_amount, order_date
FROM orders
WHERE order_date < '2024-01-01';

-- Copies old orders into an archive table

Note The SELECT column count and types must match the INSERT column list. This is an efficient way to bulk-copy data between tables without round-tripping through the application.

INSERT INTO table (col1, col2) VALUES (val1, val2);

INSERT INTO users (first_name, email, created_at)
VALUES ('Alice', '[email protected]', CURRENT_TIMESTAMP);

-- 1 row inserted

Note Always specify column names explicitly. Relying on column order breaks when the table schema changes. Strings must be in single quotes — double quotes are for identifiers in ANSI SQL.

INSERT INTO table (col1, col2)
VALUES (val1, val2), (val3, val4), ...;

INSERT INTO products (product_name, price, category_id)
VALUES
  ('Wireless Mouse', 29.99, 2),
  ('USB Keyboard', 49.99, 2),
  ('27" Monitor', 349.00, 3);

-- 3 rows inserted

Note Multi-row INSERT is much faster than individual INSERTs because it reduces round trips and allows batch optimization. Most databases support this syntax. There may be a limit on the number of rows per statement.

UPDATE table SET col1 = val1, col2 = val2 WHERE condition;

UPDATE products
SET price = price * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE category_id = 5;

-- Increases price by 10% for all products in category 5

Note Always include a WHERE clause unless you genuinely want to update every row. Running UPDATE without WHERE is a common disaster. Test with a SELECT using the same WHERE first.

-- PostgreSQL
UPDATE table1 SET col = t2.col
FROM table2 t2 WHERE table1.ref = t2.id;
-- MySQL
UPDATE table1 t1 JOIN table2 t2 ON t1.ref = t2.id
SET t1.col = t2.col;

-- PostgreSQL
UPDATE orders
SET shipping_region = u.region
FROM users u
WHERE orders.user_id = u.id
  AND orders.shipping_region IS NULL;

-- Fills in missing shipping regions from the users table

Note Syntax differs between databases. PostgreSQL uses UPDATE ... FROM. MySQL uses UPDATE ... JOIN. Standard SQL uses a correlated subquery in SET. Always test with a SELECT first.

DELETE FROM table WHERE condition;

DELETE FROM sessions
WHERE last_active < CURRENT_DATE - INTERVAL '90 days';

-- Removes sessions inactive for over 90 days

Note DELETE without WHERE deletes ALL rows. Use TRUNCATE TABLE for faster full-table deletion (it resets auto-increment too). DELETE fires row-level triggers; TRUNCATE usually does not.

INSERT INTO table (columns) VALUES (values)
ON CONFLICT (conflict_column)
DO UPDATE SET col = EXCLUDED.col;

INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET
  theme = EXCLUDED.theme,
  language = EXCLUDED.language;

-- Inserts if user_id 42 has no row, otherwise updates

Note EXCLUDED refers to the row that was proposed for insertion. You need a unique constraint or unique index on the conflict column for ON CONFLICT to work.

INSERT INTO table (columns) VALUES (values)
ON DUPLICATE KEY UPDATE col = VALUES(col);

INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON DUPLICATE KEY UPDATE
  theme = VALUES(theme),
  language = VALUES(language);

-- Inserts or updates, same as PostgreSQL ON CONFLICT

Note MySQL 8.0.19+ also supports VALUES(col) replacement with the alias syntax: AS new_row followed by new_row.col. The VALUES() function in ON DUPLICATE KEY UPDATE is deprecated in MySQL 8.0.20+.

MERGE INTO target USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

MERGE INTO inventory t
USING shipments s ON t.product_id = s.product_id
WHEN MATCHED THEN
  UPDATE SET t.quantity = t.quantity + s.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity)
  VALUES (s.product_id, s.quantity);

-- Updates existing inventory or inserts new product rows

Note MERGE is supported by SQL Server, Oracle, and PostgreSQL 15+. MySQL does not support MERGE — use ON DUPLICATE KEY UPDATE instead. MERGE can include a WHEN MATCHED AND condition for conditional updates.

INSERT INTO table (columns) VALUES (values) RETURNING *;
UPDATE table SET col = val WHERE condition RETURNING col;
DELETE FROM table WHERE condition RETURNING id;

INSERT INTO orders (user_id, total_amount, status)
VALUES (42, 299.99, 'pending')
RETURNING order_id, created_at;

-- order_id | created_at
-- 1847     | 2025-11-20 14:35:00

Note RETURNING avoids a separate SELECT to get generated IDs or default values. Supported in PostgreSQL natively. MySQL 8.0 does not support RETURNING — use LAST_INSERT_ID() instead. SQL Server uses the OUTPUT clause.

TRUNCATE TABLE table_name;

TRUNCATE TABLE temp_import_data;

-- All rows removed instantly, auto-increment reset

Note TRUNCATE is much faster than DELETE for removing all rows because it deallocates data pages instead of logging individual row deletions. It cannot be rolled back in MySQL (it can in PostgreSQL). It also cannot have a WHERE clause.

CREATE TABLE table_name (
  column_name data_type constraints,
  ...
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table created with auto-increment ID, constraints, and defaults

Note SERIAL is PostgreSQL-specific (auto-increment integer). MySQL uses INT AUTO_INCREMENT. Standard SQL uses GENERATED ALWAYS AS IDENTITY. Always define a primary key.

INTEGER, BIGINT, SMALLINT
DECIMAL(precision, scale), NUMERIC
VARCHAR(n), TEXT, CHAR(n)
BOOLEAN
DATE, TIME, TIMESTAMP, TIMESTAMPTZ
UUID, JSON, JSONB

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  weight_kg NUMERIC(6, 3),
  description TEXT,
  is_available BOOLEAN DEFAULT true,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Demonstrates typical column type choices

Note Use DECIMAL for money — never FLOAT or DOUBLE, which have rounding errors. Use TEXT over VARCHAR when you do not need a length limit. JSONB (PostgreSQL) is preferred over JSON because it supports indexing.

column_name type PRIMARY KEY
-- or composite:
PRIMARY KEY (col1, col2)

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- Composite primary key on (order_id, product_id)

Note A primary key is automatically NOT NULL and UNIQUE. Composite primary keys are useful for junction/bridge tables. Each table should have exactly one primary key.

FOREIGN KEY (column) REFERENCES other_table(column)
  ON DELETE action ON UPDATE action

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total_amount DECIMAL(10, 2),
  order_date DATE DEFAULT CURRENT_DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- user_id must reference an existing users.id

Note ON DELETE options: RESTRICT (block), CASCADE (delete child rows), SET NULL, SET DEFAULT. CASCADE is convenient but dangerous — one delete can wipe many related rows. Default is RESTRICT in most databases.

column_name type UNIQUE
-- or table-level:
UNIQUE (col1, col2)

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  employee_number VARCHAR(20) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL,
  department_id INTEGER NOT NULL,
  UNIQUE (email, department_id)
);

-- employee_number is unique on its own;
-- (email, department_id) must be unique as a pair

Note UNIQUE allows multiple NULLs in most databases (PostgreSQL, MySQL). SQL Server treats NULLs as equal in unique constraints by default, so only one NULL is allowed. Use a partial unique index to handle this.

column_name type CHECK (condition)
-- or table-level:
CHECK (condition involving multiple columns)

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_name VARCHAR(200) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  max_attendees INTEGER CHECK (max_attendees > 0),
  CHECK (end_date >= start_date)
);

-- Ensures max_attendees is positive and end_date is not before start_date

Note MySQL 8.0+ supports CHECK constraints (earlier versions parsed but silently ignored them). CHECK constraints cannot reference other tables — use triggers or application logic for cross-table validation.

column_name type DEFAULT value

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  status VARCHAR(20) DEFAULT 'pending',
  priority INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Omitting status, priority, or created_at uses defaults

Note DEFAULT applies only when the column is omitted from INSERT. Explicitly inserting NULL overrides the default with NULL (unless NOT NULL is also set). You can use expressions like CURRENT_TIMESTAMP as defaults.

ALTER TABLE table_name
  ADD COLUMN col type constraints,
  DROP COLUMN col,
  ALTER COLUMN col SET DATA TYPE new_type,
  ADD CONSTRAINT name constraint_definition;

ALTER TABLE users
  ADD COLUMN phone VARCHAR(20),
  ADD COLUMN updated_at TIMESTAMP;

ALTER TABLE users
  ALTER COLUMN email SET NOT NULL;

ALTER TABLE users
  ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');

-- Adds columns, modifies existing, adds constraint

Note ALTER TABLE syntax varies across databases. PostgreSQL uses ALTER COLUMN ... TYPE. MySQL uses MODIFY COLUMN. Adding NOT NULL to a column with existing NULLs will fail — update the data first.

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name CASCADE;

DROP TABLE IF EXISTS temp_import;

-- PostgreSQL: CASCADE drops dependent objects
DROP TABLE IF EXISTS categories CASCADE;

-- Table and its data are permanently removed

Note DROP TABLE is irreversible outside of a transaction. CASCADE also drops views, foreign keys, and other objects that depend on the table. MySQL does not support CASCADE on DROP TABLE the same way.

CREATE TABLE IF NOT EXISTS table_name (
  columns...
);

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name VARCHAR(100) NOT NULL,
  action VARCHAR(10) NOT NULL,
  old_data JSONB,
  new_data JSONB,
  changed_by INTEGER,
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Creates the table only if it does not already exist

Note IF NOT EXISTS prevents errors in migration scripts that might run multiple times. It does NOT verify that the existing table has the same schema — it just skips creation if the name exists.

CREATE INDEX index_name ON table (column);

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Speeds up queries filtering or joining on orders.user_id

Note Indexes speed up reads but slow down writes (INSERT, UPDATE, DELETE) because the index must be maintained. Only index columns that appear frequently in WHERE, JOIN, and ORDER BY clauses.

CREATE UNIQUE INDEX index_name ON table (column);

CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Enforces uniqueness and speeds up lookups by email

Note A unique index enforces a constraint and provides index performance in one operation. It is functionally similar to a UNIQUE constraint. In PostgreSQL, UNIQUE constraints are implemented as unique indexes internally.

CREATE INDEX index_name ON table (col1, col2);

CREATE INDEX idx_orders_user_date
  ON orders (user_id, order_date DESC);

-- Optimizes queries filtering by user_id AND order_date

Note Column order matters enormously. The index is useful for queries on (user_id), (user_id, order_date), but NOT for (order_date) alone. Put the most selective or most-filtered column first. This is the leftmost prefix rule.

-- PostgreSQL
CREATE INDEX index_name ON table (column) WHERE condition;

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- Smaller, faster index that only covers pending orders

Note Partial indexes are smaller and faster because they only include rows matching the WHERE condition. PostgreSQL supports them natively. MySQL does not — use a generated column with an index as a workaround.

-- PostgreSQL 11+
CREATE INDEX index_name ON table (col1) INCLUDE (col2, col3);

CREATE INDEX idx_orders_user_covering
  ON orders (user_id)
  INCLUDE (total_amount, status);

-- Index-only scan possible for queries selecting total_amount and status filtered by user_id

Note A covering index includes all columns a query needs, enabling an index-only scan without touching the table. INCLUDE columns are stored in the index but not used for searching or sorting.

-- PostgreSQL
EXPLAIN ANALYZE SELECT ...;
-- MySQL
EXPLAIN SELECT ...;

EXPLAIN ANALYZE
SELECT u.first_name, COUNT(*) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.is_active = true
GROUP BY u.first_name;

-- Shows execution plan with actual timing and row counts
-- Look for: Seq Scan (bad on big tables), Index Scan (good), Hash Join vs Nested Loop

Note EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings. Use EXPLAIN (FORMAT JSON) in PostgreSQL for machine-parseable output. Never run EXPLAIN ANALYZE on destructive queries (DELETE/UPDATE) without wrapping in a transaction and rolling back.

-- Index when:
-- 1. Column is in WHERE frequently
-- 2. Column is in JOIN conditions
-- 3. Column is in ORDER BY
-- 4. Column has high cardinality (many unique values)

-- Skip indexing when:
-- 1. Table is small (< few thousand rows)
-- 2. Column has low cardinality (e.g., boolean)
-- 3. Table has heavy write load
-- 4. Column is rarely queried

-- Good: frequently filtered, high cardinality
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Questionable: boolean column, low cardinality
-- CREATE INDEX idx_users_active ON users (is_active);
-- Better as a partial index:
CREATE INDEX idx_users_active ON users (id) WHERE is_active = true;

-- Indexing strategy depends on query patterns and data distribution

Note Use EXPLAIN to verify indexes are being used. The optimizer may ignore an index if it estimates a sequential scan is faster (e.g., when selecting most of the table). Regularly check for unused indexes and remove them.

-- PostgreSQL
DROP INDEX index_name;
DROP INDEX CONCURRENTLY index_name;
-- MySQL
DROP INDEX index_name ON table_name;

-- PostgreSQL: non-blocking drop
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_old_status;

-- Index removed; writes become slightly faster, reads may slow down

Note In PostgreSQL, DROP INDEX CONCURRENTLY avoids locking the table during removal but cannot run inside a transaction. MySQL always requires specifying the table name. Remove unused indexes to save storage and speed up writes.

CREATE INDEX index_name ON table (expression);

CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));

-- Query that benefits:
-- SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Speeds up case-insensitive email lookups

Note The query WHERE clause must use the exact same expression as the index. WHERE LOWER(email) = 'x' uses the index; WHERE email = 'x' does not. PostgreSQL and MySQL 8.0+ support expression indexes.

CONCAT(str1, str2, ...)
-- or ANSI: str1 || str2

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  first_name || ' ' || last_name AS full_name_ansi
FROM users;

-- full_name     | full_name_ansi
-- Alice Johnson | Alice Johnson

Note The || operator is ANSI standard and works in PostgreSQL. MySQL uses CONCAT() only. In MySQL, CONCAT returns NULL if any argument is NULL. In PostgreSQL, || with a NULL also returns NULL. Use COALESCE to handle NULLs.

SUBSTRING(string FROM start FOR length)
SUBSTRING(string, start, length)

SELECT
  SUBSTRING(phone FROM 1 FOR 3) AS area_code,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- area_code | domain
-- 206       | example.com

Note Positions are 1-based in SQL (not 0-based like most programming languages). The FROM/FOR syntax is ANSI; the comma syntax works in MySQL and PostgreSQL.

UPPER(string)
LOWER(string)

SELECT
  UPPER(last_name) AS last_upper,
  LOWER(email) AS email_lower
FROM users;

-- last_upper | email_lower
-- JOHNSON    | [email protected]

Note Useful for case-insensitive comparisons: WHERE LOWER(email) = LOWER('[email protected]'). For better performance on frequent case-insensitive lookups, create a functional index on LOWER(column). PostgreSQL also offers ILIKE.

TRIM([LEADING|TRAILING|BOTH] [chars] FROM string)
LTRIM(string)
RTRIM(string)

SELECT
  TRIM('  Alice  ') AS trimmed,
  TRIM(LEADING '0' FROM '000425') AS no_leading_zeros,
  LTRIM('  hello') AS left_trimmed,
  RTRIM('hello  ') AS right_trimmed;

-- trimmed | no_leading_zeros | left_trimmed | right_trimmed
-- Alice   | 425              | hello        | hello

Note TRIM with no arguments removes whitespace from both sides. You can specify characters to trim. LTRIM and RTRIM are shorthand for leading/trailing trim. Clean user input with TRIM before storing.

LENGTH(string)
CHAR_LENGTH(string)

SELECT
  first_name,
  CHAR_LENGTH(first_name) AS name_length
FROM users
WHERE CHAR_LENGTH(first_name) > 10;

-- first_name  | name_length
-- Christopher | 11

Note CHAR_LENGTH counts characters; LENGTH counts bytes. They differ for multi-byte character sets (UTF-8). Use CHAR_LENGTH for user-facing string length. ANSI standard is CHARACTER_LENGTH.

REPLACE(string, search, replacement)

SELECT
  REPLACE(phone, '-', '') AS digits_only,
  REPLACE(product_name, 'V1', 'V2') AS updated_name
FROM products;

-- digits_only | updated_name
-- 2065551234  | Widget V2 Pro

Note REPLACE is case-sensitive in PostgreSQL and MySQL. It replaces all occurrences, not just the first one. For regex-based replacement in PostgreSQL, use REGEXP_REPLACE.

COALESCE(value1, value2, ..., default)

SELECT
  first_name,
  COALESCE(nickname, first_name) AS display_name,
  COALESCE(phone, email, 'No contact') AS primary_contact
FROM users;

-- Returns the first non-NULL value from the list

Note COALESCE accepts any number of arguments and returns the first non-NULL. It is ANSI standard and works everywhere. Use it for NULL fallback chains. It is NOT specific to strings — works with any data type.

CAST(expression AS data_type)
expression::data_type  -- PostgreSQL shorthand

SELECT
  CAST(price AS INTEGER) AS rounded_price,
  CAST(order_date AS VARCHAR) AS date_string,
  '42'::INTEGER + 8 AS sum_pg  -- PostgreSQL only
FROM products;

-- rounded_price | date_string | sum_pg
-- 29             | 2025-03-15  | 50

Note CAST is ANSI standard. PostgreSQL's :: shorthand is shorter but not portable. Be careful casting — CAST('abc' AS INTEGER) will throw an error. Use TRY_CAST in SQL Server for safe conversions.

POSITION(substring IN string)
STRPOS(string, substring)  -- PostgreSQL

SELECT
  email,
  POSITION('@' IN email) AS at_position,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- email             | at_position | domain
-- [email protected] | 6           | example.com

Note Returns the 1-based position of the first occurrence. Returns 0 if not found (not -1 like most programming languages). MySQL uses LOCATE(substring, string) which has the arguments in reverse order.

LPAD(string, target_length, pad_char)
RPAD(string, target_length, pad_char)

SELECT
  LPAD(CAST(invoice_number AS VARCHAR), 8, '0') AS padded_invoice,
  RPAD(product_code, 10, '.') AS padded_code
FROM invoices;

-- padded_invoice | padded_code
-- 00004271       | PRD-42....

Note LPAD pads on the left, RPAD on the right. If the string is already longer than target_length, it gets truncated to target_length. Commonly used for formatting invoice numbers, report columns, and display output.

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
NOW()

SELECT
  CURRENT_DATE AS today,
  CURRENT_TIMESTAMP AS right_now,
  NOW() AS also_now;

-- today      | right_now                    | also_now
-- 2025-11-20 | 2025-11-20 14:35:22.123456+00 | 2025-11-20 14:35:22.123456+00

Note CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP are ANSI standard and work everywhere. NOW() is a function that does the same as CURRENT_TIMESTAMP but is not standard SQL. In a transaction, these return the time the transaction started, not the current wall clock time.

-- PostgreSQL
date + INTERVAL 'n unit'
-- MySQL
DATE_ADD(date, INTERVAL n unit)
-- ANSI
date + INTERVAL 'n' unit

-- PostgreSQL
SELECT
  order_date,
  order_date + INTERVAL '30 days' AS due_date,
  order_date - INTERVAL '1 year' AS year_ago
FROM orders;

-- MySQL
SELECT
  order_date,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date
FROM orders;

-- order_date  | due_date    | year_ago
-- 2025-10-15  | 2025-11-14  | 2024-10-15

Note Interval units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Adding months is tricky — Jan 31 + 1 month may yield Feb 28 or Mar 3 depending on the database. PostgreSQL truncates to end of month; MySQL may overflow.

-- PostgreSQL
date1 - date2  -- returns integer (days)
-- MySQL
DATEDIFF(date1, date2)  -- returns integer (days)
-- Standard: use EXTRACT on interval

-- PostgreSQL
SELECT
  order_date,
  shipped_date,
  shipped_date - order_date AS days_to_ship
FROM orders
WHERE shipped_date IS NOT NULL;

-- MySQL
SELECT
  order_date,
  shipped_date,
  DATEDIFF(shipped_date, order_date) AS days_to_ship
FROM orders;

-- order_date  | shipped_date | days_to_ship
-- 2025-10-01  | 2025-10-04   | 3

Note PostgreSQL subtracts dates directly. MySQL uses DATEDIFF(end, start) — note the argument order is end first. SQL Server's DATEDIFF takes a unit argument: DATEDIFF(DAY, start, end). Always check argument order in your database.

EXTRACT(part FROM date)
-- Parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY, EPOCH

SELECT
  order_date,
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  EXTRACT(DOW FROM order_date) AS day_of_week
FROM orders;

-- order_date  | order_year | order_month | day_of_week
-- 2025-10-15  | 2025       | 10          | 3

Note EXTRACT is ANSI standard. In PostgreSQL, DOW is 0 (Sunday) to 6 (Saturday). MySQL's DAYOFWEEK returns 1 (Sunday) to 7 (Saturday). EXTRACT(EPOCH FROM timestamp) gives Unix timestamp in PostgreSQL.

-- PostgreSQL
TO_CHAR(date, 'format')
-- MySQL
DATE_FORMAT(date, 'format')

-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_date,
       TO_CHAR(order_date, 'Mon DD, YYYY') AS pretty_date
FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_date,
       DATE_FORMAT(order_date, '%b %d, %Y') AS pretty_date
FROM orders;

-- iso_date    | pretty_date
-- 2025-10-15  | Oct 15, 2025

Note Format codes differ between databases. PostgreSQL uses YYYY, MM, DD, HH24, MI, SS. MySQL uses %Y, %m, %d, %H, %i, %s. Format dates in the application layer when possible to avoid DB-specific code.

-- PostgreSQL
DATE_TRUNC('unit', timestamp)
-- MySQL
DATE(timestamp)  -- truncate to date
DATE_FORMAT(timestamp, '%Y-%m-01')  -- truncate to month

-- PostgreSQL: group orders by month
SELECT
  DATE_TRUNC('month', order_date) AS order_month,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

-- order_month           | order_count | revenue
-- 2025-10-01 00:00:00   | 342         | 52340.00
-- 2025-11-01 00:00:00   | 298         | 44890.50

Note DATE_TRUNC is extremely useful for time-series grouping. It rounds down to the start of the given period (year, quarter, month, week, day, hour). MySQL lacks DATE_TRUNC — use DATE_FORMAT or manual rounding.

INTERVAL 'quantity unit'
-- Can combine: INTERVAL '2 hours 30 minutes'

SELECT
  created_at,
  created_at + INTERVAL '7 days' AS expires_at,
  NOW() - created_at AS age
FROM sessions
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Sessions created in the last 24 hours, with expiry and age

Note PostgreSQL supports rich interval syntax: '1 year 2 months 3 days'. MySQL intervals are single-unit: INTERVAL 1 YEAR, INTERVAL 30 DAY. Subtracting two timestamps gives an interval in PostgreSQL but not in MySQL.

AGE(timestamp1, timestamp2)
AGE(timestamp)  -- shorthand for AGE(NOW(), timestamp)

SELECT
  first_name,
  birth_date,
  AGE(birth_date) AS age,
  EXTRACT(YEAR FROM AGE(birth_date)) AS years_old
FROM users;

-- first_name | birth_date | age                    | years_old
-- Alice      | 1990-05-15 | 35 years 6 mons 5 days | 35

Note AGE is PostgreSQL-specific. It returns a human-readable interval. MySQL has no direct equivalent — compute age manually with TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) and adjust for whether the birthday has passed this year.

-- PostgreSQL
timestamp AT TIME ZONE 'zone'
-- MySQL
CONVERT_TZ(datetime, from_tz, to_tz)

-- PostgreSQL
SELECT
  created_at AT TIME ZONE 'UTC' AS utc_time,
  created_at AT TIME ZONE 'America/New_York' AS eastern_time
FROM events;

-- MySQL
SELECT
  CONVERT_TZ(created_at, '+00:00', '-05:00') AS eastern_time
FROM events;

-- utc_time                  | eastern_time
-- 2025-11-20 14:30:00+00    | 2025-11-20 09:30:00-05

Note Always store timestamps in UTC (use TIMESTAMPTZ in PostgreSQL). Convert to local time only for display. Daylight saving time offsets change — use named zones ('America/New_York') instead of fixed offsets ('-05:00') when possible.

ROW_NUMBER() OVER (ORDER BY column)

SELECT
  ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS rank,
  user_id,
  total_amount
FROM orders;

-- rank | user_id | total_amount
-- 1    | 42      | 999.00
-- 2    | 17      | 875.50
-- 3    | 88      | 849.99

Note ROW_NUMBER assigns a unique sequential number. Ties get arbitrary ordering — two rows with the same total_amount will get different numbers based on physical storage order. Use RANK or DENSE_RANK if you need tie handling.

RANK() OVER (ORDER BY column)
DENSE_RANK() OVER (ORDER BY column)

SELECT
  product_name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

-- product_name | price  | rank | dense_rank
-- Laptop       | 999.00 | 1    | 1
-- Tablet       | 499.00 | 2    | 2
-- Phone        | 499.00 | 2    | 2
-- Keyboard     | 49.99  | 4    | 3  <-- rank skips 3, dense_rank doesn't

Note RANK leaves gaps after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). Choose based on whether you need contiguous numbers or positional accuracy.

window_function() OVER (PARTITION BY column ORDER BY column)

SELECT
  department_id,
  first_name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

-- department_id | first_name | salary | dept_rank
-- 1             | Alice      | 95000  | 1
-- 1             | Bob        | 82000  | 2
-- 2             | Carol      | 105000 | 1
-- 2             | Dave       | 78000  | 2

Note PARTITION BY divides rows into groups and applies the window function independently within each group. It is like GROUP BY but without collapsing rows. You can partition by multiple columns.

LAG(column, offset, default) OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)

SELECT
  order_date,
  total_amount,
  LAG(total_amount) OVER (ORDER BY order_date) AS prev_amount,
  total_amount - LAG(total_amount) OVER (ORDER BY order_date) AS change
FROM orders
WHERE user_id = 42;

-- order_date  | total_amount | prev_amount | change
-- 2025-09-01  | 150.00       | NULL        | NULL
-- 2025-10-05  | 230.00       | 150.00      | 80.00
-- 2025-11-12  | 180.00       | 230.00      | -50.00

Note LAG looks at previous rows; LEAD looks at subsequent rows. The optional second argument specifies how many rows to look back/ahead (default 1). The third argument provides a default value instead of NULL for the first/last row.

SUM(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
  AVG(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM orders
WHERE user_id = 42;

-- order_date  | total_amount | running_total | moving_avg_3
-- 2025-09-01  | 150.00       | 150.00        | 150.00
-- 2025-10-05  | 230.00       | 380.00        | 190.00
-- 2025-11-12  | 180.00       | 560.00        | 186.67

Note Without ROWS/RANGE, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows with the same ORDER BY value. Use ROWS for exact row-based windows. This distinction matters when ORDER BY has ties.

NTILE(n) OVER (ORDER BY column)

SELECT
  product_name,
  price,
  NTILE(4) OVER (ORDER BY price) AS price_quartile
FROM products;

-- product_name | price  | price_quartile
-- Cable         | 9.99   | 1
-- Mouse         | 29.99  | 1
-- Keyboard      | 49.99  | 2
-- Monitor       | 349.00 | 3
-- Laptop        | 999.00 | 4

Note NTILE divides rows into n approximately equal groups. If the row count is not evenly divisible, earlier groups get one extra row. Useful for percentile bucketing, but the buckets are based on row count, not value distribution.

FIRST_VALUE(column) OVER (ORDER BY column)
LAST_VALUE(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

SELECT
  department_id,
  first_name,
  salary,
  FIRST_VALUE(first_name) OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS top_earner
FROM employees;

-- Shows each employee alongside their department's top earner

Note LAST_VALUE has a critical gotcha: the default window frame ends at CURRENT ROW, not at the end of the partition. You must add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.

ROWS BETWEEN start AND end
-- start/end: UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS weekly_moving_avg,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_total
FROM daily_sales;

-- 7-day moving average alongside cumulative total

Note ROWS counts physical rows. RANGE groups rows with the same ORDER BY value together. For date-based ranges in PostgreSQL, you can use RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW if the ORDER BY column is a date.

SELECT ...
FROM table
WINDOW w AS (PARTITION BY col ORDER BY col);

SELECT
  department_id,
  first_name,
  salary,
  ROW_NUMBER() OVER w AS dept_rank,
  SUM(salary) OVER w AS running_salary,
  AVG(salary) OVER w AS running_avg
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

-- Reuses the same window definition for three different functions

Note The WINDOW clause avoids repeating the same OVER specification. It is ANSI SQL and supported in PostgreSQL and MySQL 8+. You can still modify a named window in individual OVER clauses to add frame specs.

PERCENT_RANK() OVER (ORDER BY column)
CUME_DIST() OVER (ORDER BY column)

SELECT
  first_name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

-- first_name | salary | pct_rank | cumulative_dist
-- Dave       | 50000  | 0.00     | 0.25
-- Carol      | 65000  | 0.33     | 0.50
-- Bob        | 82000  | 0.67     | 0.75
-- Alice      | 95000  | 1.00     | 1.00

Note PERCENT_RANK returns (rank - 1) / (total - 1), ranging from 0 to 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. Both are useful for percentile analysis.

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue
ORDER BY month;

-- Month-over-month revenue with growth calculation

Note CTEs improve readability by breaking complex queries into named steps. Multiple CTEs can be chained: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b. In PostgreSQL 12+, the optimizer can inline CTEs for better performance.

WITH RECURSIVE cte AS (
  -- base case
  SELECT ... WHERE condition
  UNION ALL
  -- recursive step
  SELECT ... FROM cte JOIN ...
)
SELECT * FROM cte;

WITH RECURSIVE org_chart AS (
  -- Base: top-level managers (no manager)
  SELECT id, first_name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  -- Recursive: employees who report to someone already in the result
  SELECT e.id, e.first_name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, first_name;

-- id | first_name | manager_id | depth
-- 1  | Alice      | NULL       | 1
-- 2  | Bob        | 1          | 2
-- 5  | Carol      | 1          | 2
-- 3  | Dave       | 2          | 3

Note Always include a depth counter and a LIMIT or WHERE condition in the outer query to prevent infinite loops from circular references. MySQL 8+ and PostgreSQL support WITH RECURSIVE. Add CYCLE detection in PostgreSQL 14+ with CYCLE clause.

CASE
  WHEN condition THEN result
  WHEN condition THEN result
  ELSE default
END

SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount >= 500 THEN 'premium'
    WHEN total_amount >= 100 THEN 'standard'
    ELSE 'small'
  END AS order_tier
FROM orders;

-- order_id | total_amount | order_tier
-- 101      | 750.00       | premium
-- 102      | 45.99        | small
-- 103      | 200.00       | standard

Note CASE evaluates conditions top-to-bottom and returns the first match. If no condition matches and there is no ELSE, it returns NULL. CASE works in SELECT, WHERE, ORDER BY, and even inside aggregate functions.

NULLIF(expression1, expression2)

SELECT
  product_name,
  revenue,
  cost,
  revenue / NULLIF(cost, 0) AS margin_ratio
FROM product_stats;

-- Returns NULL instead of division-by-zero error when cost is 0

Note NULLIF returns NULL if the two expressions are equal; otherwise returns the first expression. The most common use is preventing division by zero: x / NULLIF(y, 0) gives NULL instead of an error.

CREATE VIEW view_name AS
SELECT ...;

CREATE OR REPLACE VIEW view_name AS
SELECT ...;

CREATE VIEW active_order_summary AS
SELECT
  u.id AS user_id,
  u.first_name,
  COUNT(o.order_id) AS order_count,
  SUM(o.total_amount) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status <> 'cancelled'
GROUP BY u.id, u.first_name;

-- Use like a table:
SELECT * FROM active_order_summary WHERE lifetime_value > 1000;

-- Encapsulates a complex query as a reusable virtual table

Note Views do not store data — they are saved queries that execute when referenced. Use materialized views (PostgreSQL) for caching expensive aggregations. Updating through views is limited to simple, single-table views.

BEGIN;
-- statements
COMMIT;
-- or
ROLLBACK;

BEGIN;

UPDATE accounts SET balance = balance - 200.00
WHERE account_id = 1001;

UPDATE accounts SET balance = balance + 200.00
WHERE account_id = 1002;

-- If both succeed:
COMMIT;
-- If something goes wrong:
-- ROLLBACK;

-- Both updates happen atomically, or neither does

Note Transactions ensure atomicity — all statements succeed together or fail together. Keep transactions short to avoid holding locks. PostgreSQL supports SAVEPOINT for partial rollbacks within a transaction. MySQL auto-commits by default; use START TRANSACTION explicitly.

SELECT columns FROM table1
UNION [ALL]
SELECT columns FROM table2;

SELECT first_name, email, 'customer' AS source
FROM customers
UNION ALL
SELECT first_name, email, 'employee' AS source
FROM employees;

-- Combined list of customers and employees

Note UNION removes duplicates (slower, requires sorting). UNION ALL keeps all rows (faster). Use UNION ALL unless you specifically need deduplication. Both queries must have the same number of columns with compatible types.

SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;

SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;

-- Customers who are also employees
SELECT email FROM customers
INTERSECT
SELECT email FROM employees;

-- Customers who are NOT employees
SELECT email FROM customers
EXCEPT
SELECT email FROM employees;

-- INTERSECT: emails in both tables
-- EXCEPT: emails only in customers

Note INTERSECT returns rows in both result sets. EXCEPT returns rows in the first set but not the second. MySQL 8.0.31+ supports these; earlier versions do not. SQL Server calls EXCEPT what PostgreSQL calls EXCEPT — they are the same. MINUS is Oracle's synonym for EXCEPT.

CREATE MATERIALIZED VIEW view_name AS
SELECT ...;

REFRESH MATERIALIZED VIEW view_name;

CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT
  p.product_name,
  SUM(oi.quantity) AS total_sold,
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.product_name;

-- Refresh when data changes:
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_summary;

-- Pre-computed summary table; queries are instant

Note Materialized views store results physically, unlike regular views. They must be manually refreshed. CONCURRENTLY allows refreshing without locking reads but requires a unique index. MySQL does not support materialized views natively.

-- PostgreSQL
column->>'key'  -- text
column->'key'   -- JSON
column @> '{"key": "value"}'  -- containment
-- MySQL
JSON_EXTRACT(column, '$.key')
column->>'$.key'

-- PostgreSQL
SELECT
  id,
  metadata->>'name' AS name,
  metadata->'address'->>'city' AS city
FROM products
WHERE metadata @> '{"category": "electronics"}';

-- id | name       | city
-- 1  | Widget Pro | Seattle

Note Use JSONB (not JSON) in PostgreSQL for indexing and efficient querying. Create a GIN index on JSONB columns: CREATE INDEX idx_meta ON products USING GIN (metadata). MySQL JSON functions use dollar-sign path syntax: $.key.nested.

CREATE TEMPORARY TABLE temp_name (
  columns...
);
-- or
CREATE TEMP TABLE temp_name AS
SELECT ...;

CREATE TEMP TABLE high_value_users AS
SELECT u.id, u.first_name, SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name
HAVING SUM(o.total_amount) > 5000;

-- Use in subsequent queries:
SELECT * FROM high_value_users ORDER BY total_spent DESC;

-- Temp table exists only for the current session

Note Temporary tables are automatically dropped at the end of the session (or transaction, if ON COMMIT DROP is specified). They are visible only to the creating session. Useful for breaking up complex multi-step queries.

-- WRONG:
WHERE column = NULL
WHERE column <> NULL

-- CORRECT:
WHERE column IS NULL
WHERE column IS NOT NULL

-- This returns NO rows, even if phone is NULL:
SELECT * FROM users WHERE phone = NULL;

-- This works correctly:
SELECT * FROM users WHERE phone IS NULL;

-- This also fails silently:
SELECT * FROM users WHERE phone <> NULL;
-- Returns no rows because NULL <> NULL is UNKNOWN

-- NULL = NULL evaluates to UNKNOWN, not TRUE
-- NULL <> NULL also evaluates to UNKNOWN, not TRUE

Note NULL represents unknown. Any comparison with NULL yields UNKNOWN, which is treated as FALSE in WHERE. This includes =, <>, <, >, IN, and NOT IN. The only operators that work with NULL are IS NULL, IS NOT NULL, and IS DISTINCT FROM.

-- WRONG: selecting non-aggregated column without GROUP BY
SELECT user_id, first_name, COUNT(*)
FROM orders
JOIN users ON users.id = orders.user_id
GROUP BY user_id;
-- first_name is not in GROUP BY or an aggregate!

-- WRONG (MySQL without ONLY_FULL_GROUP_BY):
SELECT category_id, product_name, AVG(price)
FROM products
GROUP BY category_id;
-- Which product_name does it pick? Undefined!

-- CORRECT:
SELECT category_id, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

-- PostgreSQL and standard SQL raise an error
-- MySQL may silently return an arbitrary value

Note Every column in SELECT must either be in GROUP BY or inside an aggregate function. MySQL's old default allowed this silently with random results. Always enable ONLY_FULL_GROUP_BY in MySQL. PostgreSQL enforces this strictly.

-- BAD: one query per user (in application code)
-- for each user:
--   SELECT * FROM orders WHERE user_id = ?

-- GOOD: one query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Instead of this application loop:
-- users = query("SELECT * FROM users")
-- for user in users:
--     orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)

-- Use a single query:
SELECT u.first_name, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;

-- 1 query instead of N+1 queries

Note The N+1 problem is the most common performance killer in database-backed applications. Fetch related data in a single query using JOINs, or use your ORM's eager-loading feature. Signs: page load time grows linearly with the number of records.

-- WRONG: comparing string column to integer
WHERE phone_number = 2065551234
-- The DB converts every phone_number to int for comparison!

-- CORRECT: compare with matching types
WHERE phone_number = '2065551234'

-- Slow: index on user_code (VARCHAR) is not used
SELECT * FROM users WHERE user_code = 12345;

-- Fast: index is used
SELECT * FROM users WHERE user_code = '12345';

-- Type mismatch forces a full table scan because the index cannot be used

Note When you compare a string column to a number, the database may cast every row's string value to a number, which prevents index usage and can cause errors on non-numeric strings. Always match the data type in your comparison.

-- After creating a foreign key, add an index:
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Without index: this JOIN does a full table scan on orders
SELECT u.first_name, COUNT(*) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.first_name;

-- After adding index: uses index scan, much faster
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Foreign key columns need indexes for JOIN performance

Note PostgreSQL does NOT automatically create indexes on foreign key columns (only on primary keys). MySQL InnoDB DOES auto-create them. Always check. A missing FK index also slows down DELETE on the parent table because it must scan the child table for references.

-- AVOID in production:
SELECT * FROM users;

-- PREFER:
SELECT id, first_name, email FROM users;

-- Bad: returns all columns including large TEXT/BLOB
SELECT * FROM products;

-- Good: only what you need
SELECT id, product_name, price FROM products;

-- SELECT * fetches unnecessary data and breaks when schema changes

Note SELECT * wastes bandwidth on unused columns, prevents covering index optimizations, and breaks application code when columns are added, removed, or reordered. It is fine for ad-hoc exploration but should never appear in production queries or views.

-- DANGEROUS:
UPDATE users SET is_active = false;
DELETE FROM orders;

-- SAFE: always filter
UPDATE users SET is_active = false WHERE last_login < '2024-01-01';
DELETE FROM orders WHERE status = 'cancelled';

-- Accidentally deactivates ALL users:
UPDATE users SET is_active = false;

-- What you meant:
UPDATE users SET is_active = false
WHERE last_login < '2024-01-01';

-- Without WHERE, every row in the table is affected

Note Always write the WHERE clause first, then add the UPDATE/DELETE around it. Or test with a SELECT using the same WHERE before running the modification. Wrap destructive operations in a transaction so you can ROLLBACK if the row count looks wrong.

-- Often slow (index may not be used):
WHERE city = 'Seattle' OR state = 'WA'

-- Faster alternative:
SELECT ... WHERE city = 'Seattle'
UNION
SELECT ... WHERE state = 'WA';

-- May not use either index effectively:
SELECT * FROM users
WHERE email = '[email protected]'
   OR phone = '2065551234';

-- Better with UNION (each query uses its own index):
SELECT * FROM users WHERE email = '[email protected]'
UNION
SELECT * FROM users WHERE phone = '2065551234';

-- UNION lets each branch use its optimal index

Note OR conditions on different columns often prevent the optimizer from using indexes efficiently. Rewriting as UNION (or UNION ALL if you know there are no duplicates) lets each branch use its own index. Check with EXPLAIN to verify.

-- WRONG: misses times after midnight on end date
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'

-- CORRECT: use exclusive upper bound
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

-- Misses orders on Dec 31 after midnight:
SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
-- created_at = '2025-12-31 14:30:00' is included
-- But the pattern is fragile with timestamp precision

-- Safe pattern:
SELECT * FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at < '2026-01-01';

-- The >= / < pattern correctly handles all times within the range

Note BETWEEN '2025-01-01' AND '2025-12-31' with timestamps works if the value is exactly midnight, but this pattern is fragile. The >= / < (half-open interval) approach is unambiguous and works regardless of timestamp precision.