Querying one table

SELECT and FROM

Also called projection, column alias, AS

What

SELECT lists the columns or expressions you want back and FROM names the table they come from. AS gives a column or table a shorter or clearer name in the result.

Why

Every read query starts here. Naming columns explicitly makes results predictable and keeps queries working when someone adds a column.

SELECT id,
       title,
       price * 0.9 AS sale_price
FROM products AS p;

WHERE

Also called filter, predicate, IN, BETWEEN, LIKE

What

WHERE keeps only the rows for which a condition is true, using comparisons, AND, OR, NOT, IN for a list, BETWEEN for an inclusive range and LIKE for simple patterns.

Why

Filtering in the database, not in your code, sends back only the rows you need and lets the database use indexes.

SELECT id, title, price
FROM products
WHERE category IN ('books', 'games')
  AND (price < 20 OR on_sale = TRUE)
  AND title LIKE 'The%';

NULL and three-valued logic

Also called IS NULL, COALESCE, unknown, NULL, IS NOT NULL, three-valued logic

What

NULL means a value is missing or unknown. Any comparison with NULL gives unknown rather than true or false, so WHERE drops the row. Test with IS NULL or IS NOT NULL and supply defaults with COALESCE.

Why

Missing values are everywhere in real data, and NULL rules explain most "my query lost rows" surprises in filters, joins, counts and NOT IN.

SELECT name,
       COALESCE(phone, 'none') AS phone
FROM customers
WHERE deleted_at IS NULL;

ORDER BY

Also called sort, ASC, DESC, NULLS FIRST, NULLS LAST

What

ORDER BY sorts the result by one or more columns or expressions, ascending by default or DESC for descending. Without it, the database may return rows in any order.

Why

Reports, pagination and "top N" queries all depend on a defined order. Adding a unique column as a tie-breaker makes the order stable.

Limiting rows

Also called LIMIT, TOP, OFFSET FETCH, pagination, OFFSET, FETCH FIRST, FETCH NEXT

What

Returning only the first N rows of a sorted result. PostgreSQL and MySQL use LIMIT and OFFSET; SQL Server uses TOP or the standard OFFSET ... FETCH, which needs an ORDER BY.

Why

It keeps pages small and fast, and it is the syntax difference you will hit first when you move between databases.

SELECT TOP (10) id, title FROM products
ORDER BY price DESC, id;

SELECT id, title FROM products
ORDER BY price DESC, id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

DISTINCT

What

DISTINCT removes duplicate rows from the result, comparing all selected columns together.

Why

It answers questions like "which countries do we have customers in" without writing a GROUP BY.

CASE expression

Also called CASE WHEN, conditional logic

What

CASE returns a value based on conditions checked in order, like if and else inside a query. It works in SELECT, WHERE, ORDER BY and inside aggregates.

Why

It turns raw values into labels and buckets, such as price bands or status names, without a lookup table or application code.

SELECT title,
       CASE
         WHEN price < 10 THEN 'budget'
         WHEN price < 50 THEN 'standard'
         ELSE 'premium'
       END AS band
FROM products;

String and date functions

Also called CONCAT, NOW, GETDATE, date arithmetic

What

Built-in functions for text (joining, trimming, changing case, extracting parts) and for dates (current time, adding intervals, truncating to a month). Names and operators differ more between databases than any other part of SQL.

Why

Cleaning names, grouping sales by month and finding records older than 30 days all rely on them, so learn your own database's set.

SELECT CONCAT(first_name, ' ', last_name) AS name,
       DATETRUNC(month, created_at) AS month
FROM customers
WHERE created_at >= DATEADD(day, -30, SYSDATETIME());

Logical order of a query

Also called order of execution

What

The order a query is evaluated in, which differs from the order you write it: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT or TOP.

Why

It explains why a column alias works in ORDER BY but not in WHERE, and why HAVING exists at all.