ITInsightHub

SQL cheat sheet: querying data

Every query is the same five clauses, and SQL reads them in a fixed order that is not the order you write them: FROM (pick tables) → WHERE (filter rows) → GROUP BY (group) → HAVING (filter groups) → SELECT (choose columns) → ORDER BY (sort) → LIMIT (cap). Understanding that order is what makes WHERE vs HAVING finally click.

All examples use a tiny employees table — id, name, department, salary — so every snippet is runnable as written in PostgreSQL, MySQL, SQL Server or SQLite.

Published

The basic query

SELECT, FROM, WHERE, ORDER BY — the four you will write most
SELECT name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;
The clause toolkit
ClauseWhat it doesNotes
SELECT col1, col2choose columns (or * for all)use SELECT DISTINCT to drop duplicate rows
FROM tablethe table to readcan be a join of several tables
WHERE conditionfilter rows before groupingcannot use aggregate functions here
ORDER BY col DESCsort rows (ASC is default)DESC for descending; sort by multiple columns with commas
LIMIT nreturn only n rowsSQL Server uses SELECT TOP n; standard is FETCH FIRST n ROWS ONLY

Filtering with WHERE

The operators you will reach for

= <> != < > <= >=
comparison; <> and != both mean 'not equal' in most databases
AND / OR / NOT
combine conditions; use parentheses to control grouping
IN (1, 2, 3)
value is in a list — cleaner than chaining OR
BETWEEN 10 AND 20
inclusive range; works for numbers, dates and strings
LIKE 'Jo%'
pattern match: % any run, _ one character (case-insensitive in MySQL, case-sensitive in PostgreSQL)
IS NULL / IS NOT NULL
the ONLY correct null test — never = NULL or <> NULL
The null gotcha, demonstrated
-- NULL means unknown, so this matches NOTHING:
SELECT * FROM employees WHERE manager_id = NULL;

-- Correct — this is the null test:
SELECT * FROM employees WHERE manager_id IS NULL;
WHERE salary > 50000 silently skips any row whose salary is NULL, because a comparison with unknown yields unknown, not true. That is the classic "my totals don't match my rows" bug. When NULLs are possible, write the filter explicitly: WHERE salary > 50000 OR salary IS NULL if you want them included.

GROUP BY and aggregates

Grouping rows and filtering the groups
-- Count employees per department, then keep only big departments
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC;

Aggregate functions and the WHERE vs HAVING split

COUNT(*)
number of rows; COUNT(column) counts non-null values only
SUM(col)
total of a numeric column; ignores NULLs
AVG(col)
average; ignores NULLs (use COALESCE to treat them as 0)
MIN(col) / MAX(col)
smallest / largest value in the group
WHERE
filters rows BEFORE they are grouped
HAVING
filters groups AFTER grouping — the only place aggregates are allowed
Every column in SELECT must either be inside an aggregate function or listed in GROUP BY — otherwise the database rejects the query as ambiguous. The logical order explains it: by the time SELECT runs, rows have already collapsed into groups, so a bare name column no longer means a single value.

Sorting and limiting

Order and cap the result — dialect spellings
-- PostgreSQL / MySQL / SQLite
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 10;

-- SQL Server
SELECT TOP 10 name, salary FROM employees ORDER BY salary DESC;

-- Standard SQL (PostgreSQL, Oracle, DB2)
SELECT name, salary FROM employees ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

References

FAQ

What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped; HAVING filters groups after grouping. That is why aggregate functions like COUNT(*) or AVG(salary) can only appear in HAVING, never in WHERE. In practice: use WHERE for row conditions, HAVING for conditions about groups.

Why does = NULL match nothing?

In SQL, NULL means unknown. Comparing anything to unknown with = yields unknown, which is not true, so the row is not matched. The only correct test is IS NULL or IS NOT NULL. This is also why aggregate functions like COUNT and SUM silently skip NULLs.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row in the group. COUNT(column) counts only rows where that column is not NULL. If a column is nullable, the two differ — use COUNT(*) when you want a true row count, COUNT(column) when you specifically want non-null values.

What is the difference between LIMIT, TOP and FETCH FIRST?

They are three dialects of the same idea — cap the number of returned rows. LIMIT n is PostgreSQL, MySQL and SQLite; SELECT TOP n is SQL Server; FETCH FIRST n ROWS ONLY is the standard SQL spelling supported by PostgreSQL, Oracle and DB2.