Filtering and sorting

A tester rarely wants a whole table. You want the order your test just created, the users in one state, or the five newest rows. WHERE chooses rows, ORDER BY sorts them and LIMIT keeps the first few. Interviewers ask these early because small slips here, such as a missing bracket (a round bracket, also called a parenthesis) or a sort with no tie-breaker (a second column that decides the order of equal values), give wrong rows without any error. The examples use the company data.

Comparisons

WHERE keeps a row when its condition is true. The comparison operators (the symbols that compare two values) are =, <> (not equal; most databases also accept !=), <, >, <= and >=.

SELECT name, salary FROM employees WHERE salary > 80000;

name | salary
Asha Rao | 150000
Vikram Shah | 95000
Neha Gupta | 82000
Dev Malhotra | 95000

Upper and lower case matter for text. In SQLite and PostgreSQL, status = 'delivered' does not match DELIVERED. MySQL, with its default settings, ignores case, so the same query would match there. Write values exactly as they are stored and your query behaves the same everywhere.

AND, OR and brackets

AND needs both conditions to be true; OR needs at least one. AND is applied before OR, the way multiplication is done before addition. That causes a classic bug. Suppose you want delivered or shipped orders above 10,000:

SELECT id, status, amount FROM orders
WHERE status = 'DELIVERED' OR status = 'SHIPPED' AND amount > 10000;

id | status | amount
101 | DELIVERED | 12000
103 | DELIVERED | 8000
104 | SHIPPED | 15000
106 | DELIVERED | 9500
107 | DELIVERED | 7000
109 | SHIPPED | 15000
110 | DELIVERED | 2500

The database read it as "delivered with any amount, or shipped above 10,000", so small delivered orders slipped in. Brackets fix it:

SELECT id, status, amount FROM orders
WHERE (status = 'DELIVERED' OR status = 'SHIPPED') AND amount > 10000;

id | status | amount
101 | DELIVERED | 12000
104 | SHIPPED | 15000
109 | SHIPPED | 15000

Whenever a condition mixes AND and OR, add brackets, even where you think they are not needed.

IN, BETWEEN and LIKE

IN matches any value in a list and replaces a chain of ORs: status IN ('SHIPPED', 'PENDING'). NOT IN is the opposite, and it has a trap with NULL (a missing value) that the NULL topic explains.

BETWEEN a AND b includes both ends. It works on numbers, and on dates stored as YYYY-MM-DD text, because that text sorts in date order:

SELECT name, hire_date FROM employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

name | hire_date
Rohan Mehta | 2022-06-01
Dev Malhotra | 2022-12-01

If a column holds a date and a time, an upper end of '2022-12-31' misses times later on that day. A safer pattern is hire_date >= '2022-01-01' AND hire_date < '2023-01-01'.

LIKE matches a pattern. % stands for any number of characters (including none) and _ for exactly one. So 'B%' means "starts with B", '%Ltd' means "ends with Ltd" and '%mart%' means "contains mart". SQLite, and MySQL with its default settings, ignore case in LIKE for English letters. PostgreSQL does not, and offers ILIKE instead.

SELECT DISTINCT customer FROM orders WHERE customer LIKE '%Ltd' OR customer LIKE 'B%';

customer
Acme Ltd
Bharat Traders

Sorting with ORDER BY

Without ORDER BY, the database may return rows in any order. The order you see today can change after an index (a lookup structure the database builds to find rows faster) is added or the data grows, so a test that checks row order must sort. ASC (the default) sorts from small to large and DESC from large to small. With several columns, the second one breaks ties in the first:

SELECT name, salary FROM employees
WHERE salary >= 64000
ORDER BY salary DESC, name ASC;

name | salary
Asha Rao | 150000
Dev Malhotra | 95000
Vikram Shah | 95000
Neha Gupta | 82000
Arjun Das | 70000
Priya Nair | 64000
Rohan Mehta | 64000

Vikram Shah and Dev Malhotra earn the same, so the name decides their order. Without the second column, their order would not be guaranteed.

LIMIT, OFFSET and pages

LIMIT n keeps the first n rows after sorting, and OFFSET k skips k rows first. Together they give you pages, which is what you need when you check an API that returns its results page by page against the database. Page 2 at 4 rows per page skips the first 4:

SELECT id, name FROM employees ORDER BY id LIMIT 4 OFFSET 4;

id | name
5 | Priya Nair
6 | Arjun Das
7 | Kavya Iyer
8 | Imran Khan

MySQL, PostgreSQL and SQLite accept LIMIT and OFFSET. Other databases write it differently: SQL Server uses TOP or OFFSET ... FETCH NEXT ... ROWS ONLY, and Oracle uses FETCH FIRST n ROWS ONLY.

How to answer in an interview

Say the conditions out loud before you write them, and turn words into operators: "more than" is >, "at least" is >=. When a question asks for "the top 3" or "page 2", sort first, add a tie-breaker column, and only then limit. Mention that BETWEEN includes both ends.

Common mistakes

• Mixing AND and OR without brackets.
• Using LIMIT without ORDER BY, so "the first rows" can be any rows.
• Sorting on a column with ties and no second column, so the order can change between runs.
• Writing status = 'delivered' when the data stores DELIVERED.

Recap

WHERE keeps rows whose condition is true. IN matches a list, BETWEEN a range with both ends included, and LIKE a pattern. ORDER BY sorts, and LIMIT with OFFSET picks a page after the sort.