SQL SELECT and Filtering: WHERE, ORDER BY, and LIMIT

Basic SELECT

SELECT retrieves data from tables. The order of clauses matters.

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- With alias
SELECT first_name AS name, salary * 12 AS annual_salary
FROM employees;

WHERE clause and operators

WHERE filters rows. Know all the comparison and logical operators.

SELECT * FROM employees
WHERE department = 'Engineering'
  AND salary > 80000
  AND hire_date >= '2020-01-01';

-- IN operator
WHERE department IN ('Engineering', 'Product', 'Design');

-- BETWEEN (inclusive on both ends)
WHERE salary BETWEEN 50000 AND 100000;

-- LIKE (pattern matching)
WHERE email LIKE '%@gmail.com';
WHERE name LIKE 'J%';        -- starts with J
WHERE name LIKE '_ob';       -- any single char then 'ob'

-- NULL checks
WHERE manager_id IS NULL;
WHERE manager_id IS NOT NULL;

ORDER BY and LIMIT

ORDER BY sorts results. LIMIT caps the number of rows returned.

-- Sort by salary descending
SELECT * FROM employees
ORDER BY salary DESC;

-- Multiple sort columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- Limit results
SELECT * FROM employees
ORDER BY hire_date DESC
LIMIT 10;          -- top 10 most recent

-- OFFSET for pagination
SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET 20;  -- rows 21-30

DISTINCT

DISTINCT removes duplicate rows from the result set.

-- Unique departments
SELECT DISTINCT department FROM employees;

-- Distinct combination of columns
SELECT DISTINCT department, job_title
FROM employees
ORDER BY department;

-- COUNT with DISTINCT
SELECT COUNT(DISTINCT department) AS dept_count
FROM employees;

Comparison operators and NULL behaviour

NULL propagates in comparisons — any operation involving NULL returns NULL (unknown).

-- All of these return NULL (not true or false):
SELECT NULL = NULL;      -- NULL
SELECT NULL != 'x';     -- NULL
SELECT NULL > 0;        -- NULL
SELECT 5 + NULL;        -- NULL

-- NULL in WHERE: rows with NULL salary are excluded
SELECT * FROM employees WHERE salary != 50000;
-- Rows with NULL salary are NOT returned!

-- To include NULLs:
SELECT * FROM employees
WHERE salary != 50000 OR salary IS NULL;

Exam tip

The most common SQL exam trap is NULL behaviour. NULL != NULL evaluates to NULL (not true), and rows with NULL are excluded from WHERE conditions. Always use IS NULL / IS NOT NULL for NULL checks.