SQL Basics

SELECT

Retrieves columns from a table

WHERE

Filters rows based on a condition. Evaluated before aggregation. Cannot reference column aliases.

GROUP BY

Groups rows by specified column(s). Always used with aggregate functions.

HAVING

Filters groups AFTER GROUP BY. Like WHERE but for aggregated results. Can reference aliases.

ORDER BY

Sorts the result set. Use ASC (default) or DESC. Applied last in query execution.

LIMIT / TOP

Restricts number of rows returned. MySQL/PG: LIMIT n | SQL Server: TOP n

DISTINCT

Returns unique rows only. SELECT ______ col FROM table

AS

Creates an alias for a column or table

INNER JOIN

Returns only rows with matching values in BOTH tables.

LEFT JOIN

All rows from the left table + matching rows from right. Non-matches = NULL on right side.

RIGHT JOIN

All rows from the right table + matching rows from left. Non-matches = NULL on left side.

FULL OUTER JOIN

All rows from both tables. Non-matches on either side filled with NULL.

CROSS JOIN

Returns the Cartesian product — every combination of rows from both tables. No ON clause.

UNION

Combines result sets of two queries, removing duplicates. Columns must match.

UNION ALL

Combines result sets of two queries, keeping duplicates. Faster than UNION.

IN

Checks if a value exists in a list

NOT IN

Removes rows where value is found in list. Caution: returns nothing if list contains NULL.

BETWEEN

Inclusive range filter. WHERE col ______ 1 AND 10 — equivalent to col >= 1 AND col <= 10

LIKE

Pattern matching. % = any number of chars, _ = single char

IS NULL

Checks for NULL values. Cannot use = NULL — must use _________

IS NOT NULL

Checks that a value is not NULL.

COUNT(*)

Counts all rows including NULLs

SUM(col)

Returns total sum of a numeric column. Ignores NULLs.

AVG(col)

Returns the arithmetic mean. Ignores NULLs — may differ from SUM/COUNT.

MIN(col) / MAX(col)

Returns the smallest or largest value in a column.

CASE WHEN

Conditional logic

COALESCE(a, b, c)

Returns the first non-NULL value in the list. Commonly used for NULL fallbacks.

NULLIF(a, b)

Returns NULL if a equals b, otherwise returns a. Used to avoid divide-by-zero.

CAST(col AS type)

Converts a value to a specified data type

Subquery

A query nested inside another query. Can appear in SELECT, FROM (derived table), or WHERE.

Execution Order

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT