SQL Syntax Cheatsheet

Q: What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping — it cannot reference aggregate functions. HAVING filters groups after GROUP BY has been applied, so you can use aggregates like COUNT(*) > 5 in a HAVING clause but not in WHERE.

Complete SQL reference — queries, filtering, joins, data modification, table operations, aggregates, and advanced patterns like CTEs and window functions.

Data Query

SELECT col1, col2 FROM tableRetrieve specific columns from a table

SELECT * FROM tableRetrieve all columns from a table

SELECT DISTINCT col FROM tableReturn only unique values for a column

SELECT col AS alias FROM tableRename a column in the result set

SELECT * FROM table WHERE conditionFilter rows based on a condition

SELECT * FROM t1 JOIN t2 ON t1.id = t2.fkCombine rows from two tables on a matching key

SELECT col, COUNT(*) FROM table GROUP BY colGroup rows by column and aggregate

GROUP BY col HAVING COUNT(*) > 1Filter groups after aggregation

SELECT * FROM table ORDER BY col ASCSort results ascending (ASC) or descending (DESC)

SELECT * FROM table LIMIT 10Return only the first N rows

SELECT * FROM table LIMIT 10 OFFSET 20Skip 20 rows, then return the next 10

SELECT ... UNION SELECT ...Combine results of two queries, removing duplicates

SELECT ... UNION ALL SELECT ...Combine results of two queries, keeping duplicates

Filtering & Operators

WHERE col = valueEqual to

WHERE col != value / col <> valueNot equal to

WHERE col > valueGreater than

WHERE col < valueLess than

WHERE col >= valueGreater than or equal to

WHERE col <= valueLess than or equal to

WHERE col BETWEEN 10 AND 50Value within an inclusive range

WHERE col IN ('a', 'b', 'c')Value matches any item in a list

WHERE col NOT IN (subquery)Value does not match any item

WHERE col LIKE 'pat%'Pattern match — % is any characters, _ is one character

WHERE col ILIKE 'pat%'Case-insensitive LIKE (PostgreSQL)

WHERE col IS NULLCheck if value is NULL

WHERE col IS NOT NULLCheck if value is not NULL

WHERE cond1 AND cond2Both conditions must be true

WHERE cond1 OR cond2At least one condition must be true

WHERE NOT conditionNegate a condition

WHERE EXISTS (subquery)True if the subquery returns any rows

Joins

FROM t1 INNER JOIN t2 ON t1.id = t2.fkReturn rows that have matching values in both tables

FROM t1 LEFT JOIN t2 ON t1.id = t2.fkAll rows from left table, matched rows from right (NULLs if no match)

FROM t1 RIGHT JOIN t2 ON t1.id = t2.fkAll rows from right table, matched rows from left (NULLs if no match)

FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.fkAll rows from both tables, NULLs where no match exists

FROM t1 CROSS JOIN t2Cartesian product — every row in t1 paired with every row in t2

FROM employees e1 JOIN employees e2 ON e1.mgr = e2.idSelf join — join a table to itself using aliases

FROM t1 LEFT JOIN t2 ON ... WHERE t2.id IS NULLAnti-join — find rows in t1 with no match in t2

FROM t1 NATURAL JOIN t2Join on all columns with the same name (use with caution)

FROM t1 JOIN t2 USING (col)Join on a shared column name without specifying both table prefixes

Data Modification

INSERT INTO table (col1, col2) VALUES (v1, v2)Insert a single row

INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4)Insert multiple rows at once

INSERT INTO table SELECT ... FROM other_tableInsert rows from another query

UPDATE table SET col = value WHERE conditionUpdate specific rows matching a condition

UPDATE t1 SET col = t2.val FROM t2 WHERE t1.id = t2.fkUpdate using values from another table (PostgreSQL)

DELETE FROM table WHERE conditionDelete specific rows matching a condition

DELETE FROM tableDelete all rows (logged, can rollback)

INSERT ... ON CONFLICT (col) DO UPDATE SET ...Upsert — insert or update on conflict (PostgreSQL)

INSERT ... ON DUPLICATE KEY UPDATE ...Upsert — insert or update on duplicate (MySQL)

MERGE INTO target USING source ON ... WHEN MATCHED THEN ...MERGE / upsert (SQL Server, Oracle)

RETURNING *Return affected rows after INSERT/UPDATE/DELETE (PostgreSQL)

Table Operations

CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(100))Create a new table with columns and a primary key

CREATE TABLE t (... , CONSTRAINT fk FOREIGN KEY (col) REFERENCES other(id))Create table with a foreign key constraint

CREATE TABLE IF NOT EXISTS t (...)Create table only if it doesn't already exist

CREATE TABLE new_t AS SELECT * FROM old_tCreate a new table from a query result

ALTER TABLE t ADD COLUMN col TYPEAdd a new column to an existing table

ALTER TABLE t DROP COLUMN colRemove a column from a table

ALTER TABLE t RENAME COLUMN old TO newRename a column

ALTER TABLE t ALTER COLUMN col SET NOT NULLAdd a NOT NULL constraint

ALTER TABLE t ADD CONSTRAINT uq UNIQUE (col)Add a unique constraint

DROP TABLE tPermanently delete a table and all its data

DROP TABLE IF EXISTS tDrop table only if it exists (avoids errors)

TRUNCATE TABLE tRemove all rows quickly (not logged, cannot rollback in some DBs)

CREATE INDEX idx ON t (col)Create an index to speed up queries on a column

CREATE UNIQUE INDEX idx ON t (col)Create a unique index (enforces uniqueness)

DROP INDEX idxRemove an index

Aggregate Functions

COUNT(*)Count total number of rows

COUNT(col)Count non-NULL values in a column

COUNT(DISTINCT col)Count unique non-NULL values

SUM(col)Sum of all values in a column

AVG(col)Average of all values in a column

MIN(col)Smallest value in a column

MAX(col)Largest value in a column

GROUP_CONCAT(col SEPARATOR ', ')Concatenate grouped values into a string (MySQL)

STRING_AGG(col, ', ')Concatenate grouped values into a string (PostgreSQL)

ARRAY_AGG(col)Aggregate values into an array (PostgreSQL)

Common Patterns

SELECT * FROM t WHERE col IN (SELECT col FROM t2)Subquery — use a query result as a filter

SELECT *, (SELECT MAX(val) FROM t2) AS m FROM t1Scalar subquery — returns a single value

WITH cte AS (SELECT ... FROM t) SELECT * FROM cteCommon Table Expression (CTE) — named temporary result set

WITH RECURSIVE cte AS (base UNION ALL SELECT ... FROM cte WHERE ...)Recursive CTE — traverse hierarchies or generate series

CASE WHEN cond THEN val WHEN cond2 THEN val2 ELSE default ENDConditional logic inside a query

COALESCE(col1, col2, 'default')Return the first non-NULL value from a list

NULLIF(a, b)Return NULL if a equals b, otherwise return a

CAST(col AS INTEGER) / col::INTEGERConvert a value to a different data type

ROW_NUMBER() OVER (ORDER BY col)Window function — assign sequential row numbers

RANK() OVER (PARTITION BY grp ORDER BY col)Window function — rank within groups (gaps on ties)

DENSE_RANK() OVER (ORDER BY col)Window function — rank without gaps on ties

SUM(col) OVER (PARTITION BY grp ORDER BY date)Running total — cumulative sum within a group

LAG(col, 1) OVER (ORDER BY date)Access the value from the previous row

LEAD(col, 1) OVER (ORDER BY date)Access the value from the next row

FAQ

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping — it cannot reference aggregate functions. HAVING filters groups after GROUP BY has been applied, so you can use aggregates like COUNT(*) > 5 in a HAVING clause but not in WHERE.

When should I use a CTE vs a subquery?

CTEs (WITH ... AS) improve readability for complex queries and can be referenced multiple times. Subqueries are fine for simple, one-off filters. Use CTEs when the query is deeply nested, when you need to reuse the same derived table, or when building recursive queries.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one at a time, is fully logged, supports WHERE clauses, and fires triggers. TRUNCATE deallocates entire data pages at once, is much faster, but cannot filter rows and may not be rolled back in some databases. Use DELETE for targeted removal and TRUNCATE to quickly empty an entire table.

How do window functions differ from GROUP BY?

GROUP BY collapses rows into one row per group, losing individual row detail. Window functions (OVER clause) perform calculations across related rows while preserving every original row in the result. This lets you compute running totals, rankings, and moving averages without grouping.

Related Resources

JSON Formatter CSV to JSON Regex Cheatsheet Git Commands