SELECT

SELECT retrieves data from a table — use * for all columns or list specific columns for clarity and performance.

SELECT *
FROM employees;

SELECT first_name, last_name, salary
FROM employees;

Prefer specific columns over SELECT *.

WHERE — Filtering

WHERE filters rows that satisfy a condition. Combine conditions with AND, OR, and NOT to narrow results precisely.

SELECT *
FROM employees
WHERE salary > 75000;

SELECT *
FROM employees
WHERE salary > 70000 AND department_id = 10;

SELECT *
FROM employees
WHERE department_id = 10 OR department_id = 20;

SELECT *
FROM employees
WHERE NOT department_id = 10;

DISTINCT

DISTINCT removes duplicate values and returns only unique rows for the selected columns.

SELECT DISTINCT department_id
FROM employees;

Returns unique values.

ORDER BY

ORDER BY sorts the result set by one or more columns, using ASC for ascending or DESC for descending order.

SELECT *
FROM employees
ORDER BY salary ASC;

SELECT *
FROM employees
ORDER BY salary DESC;

LIMIT / TOP

LIMIT (MySQL/PostgreSQL) or TOP (SQL Server) restricts the number of returned rows, typically after sorting.

-- MySQL / PostgreSQL
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- SQL Server
SELECT TOP 5 *
FROM employees
ORDER BY salary DESC;

Dialect matters — label clearly.

LIKE — Wildcards

LIKE searches for patterns using % for any number of characters and _ for a single character.

SELECT *
FROM employees
WHERE first_name LIKE 'M%'; -- starts with M

SELECT *
FROM employees
WHERE first_name LIKE '%n'; -- ends with n

SELECT *
FROM employees
WHERE first_name LIKE '%ar%'; -- contains ar

% zero or more, _ exactly one.

IN

IN checks if a value matches any value in a list, replacing multiple OR conditions for cleaner queries.

SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);

BETWEEN

BETWEEN filters values within an inclusive range, including both the lower and upper boundaries.

SELECT *
FROM employees
WHERE salary BETWEEN 70000 AND 90000;

Inclusive at both boundaries.

NULL

NULL represents an unknown value — always test with IS NULL or IS NOT NULL, never with = NULL.

SELECT *
FROM employees
WHERE manager_id IS NULL;

SELECT *
FROM employees
WHERE manager_id IS NOT NULL;

-- Wrong: WHERE manager_id = NULL

Always use IS NULL / IS NOT NULL, never = NULL.