SQL Basics: SELECT, WHERE and ORDER BY

Why it matters

The screen and the API show data after the application has filtered and formatted it; the database shows what was stored. When a test fails, or passes for the wrong reason, a short query shows whether the defect is in the data or in the code that displays it, and gives the developer evidence to rerun.

SELECT is also where beginners make silent mistakes: a condition written as = NULL, missing parentheses or a list read without ORDER BY raises no error. It returns the wrong rows, and a tester who trusts them passes a broken feature or reports a bug that does not exist.

How a SELECT statement is built

SQL is declarative: you describe the result and the database decides how to produce it. The standard ISO/IEC 9075 defines the language; each engine, such as PostgreSQL, MySQL, SQLite or SQL Server, implements a dialect of it, and the basics here work in all of them unless a note says otherwise.

A statement is made of clauses that start with keywords. Keywords ignore case, but capitals keep queries readable. Text goes in single quotes ('PAID'); in standard SQL and PostgreSQL, double quotes mean a column or table name. -- starts a comment and ; ends the statement. The result is itself a table, and zero rows is a valid answer, not an error.

The clauses are evaluated, logically, in a different order from the one they are written in: FROM, WHERE, the SELECT list, ORDER BY, then LIMIT. So an alias created with AS in the SELECT list works in ORDER BY but not in WHERE, which runs before the alias exists. PostgreSQL and MySQL reject it there; SQLite accepts it, so a query that works in SQLite practice can fail on the project's engine.

-- Paid orders of customer 207, newest first
Part 3:3SELECT order_id, total_amount AS total, created_at
Part 1:1FROM orders
Part 2:2WHERE customer_id = 207
  AND status = 'PAID'
Part 4:4ORDER BY created_at DESC, order_id DESC
Part 5:5LIMIT 5;

-- Paid orders of customer 207, newest first
SELECT order_id, total_amount AS total, created_at
FROM orders
WHERE customer_id = 207
  AND status = 'PAID'
ORDER BY created_at DESC, order_id DESC
LIMIT 5;

Try it: SQL Formatter Try it: SQL Query Validator

Choosing columns with SELECT and rows with WHERE

Name the columns you check, as in SELECT order_id, status, and keep SELECT * for a quick look, so that saved checks read well in a bug report. AS renames an output column, and SELECT DISTINCT status FROM orders lists each value once: a quick way to spot values nobody expected, such as 'CANCELED' beside 'CANCELLED'.

WHERE is evaluated for every row, and only the rows for which it is true are returned. It is built from these parts:

• Comparisons: =, <> (most engines also accept !=), <, <=, >, >=.
• Logic: AND is evaluated before OR, so status = 'PAID' OR status = 'SHIPPED' AND total_amount > 500 returns all paid orders plus the shipped ones over 500. Use parentheses to say what you mean.
• Lists and ranges: status IN ('PAID', 'SHIPPED'); BETWEEN 100 AND 300 includes both ends. For timestamps use a half-open range, which includes its start and excludes its end: created_at >= '2026-09-01' AND created_at < '2026-10-01'.
• Patterns: LIKE '%@example.com', where % matches any sequence of characters, including none, and _ exactly one. Case sensitivity depends on the engine and the column's collation, its rules for comparing and sorting text: PostgreSQL compares text case-sensitively, MySQL's default collations do not, and SQLite's LIKE ignores the case of ASCII letters while its = does not.

Try it: SQL IN Clause Generator

Sorting the result with ORDER BY

Without ORDER BY the order of rows is not defined: it can change when the data grows, an index (a structure the database uses to find rows faster) is added or the engine is upgraded. Any check that reads “the first row” or compares a list with the screen must sort explicitly.

ORDER BY created_at DESC, order_id DESC uses the second column only when the first is equal; ASC is the default. End with a unique column such as the primary key: without that tie-breaker, equal rows may swap between runs, and with paging a row can appear on two pages or on none.

• LIMIT 10 keeps the first ten rows after sorting. SQL Server writes TOP (10); the standard form, FETCH FIRST 10 ROWS ONLY, works in PostgreSQL but not in MySQL or SQLite.
• Text sorts by the column's collation, and numbers stored as text sort as text: '10' comes before '9'. A price list sorted like that on screen is a reason to check the column's data type.

NULL: the value that is not there

NULL marks a missing or unknown value; it is not zero, not an empty string '' and not the text 'NULL'. A comparison with NULL is neither true nor false but unknown, and WHERE keeps only true rows, so rows where the compared column is NULL vanish from results without any warning.

Unknown also flows through AND and OR. An AND is true only when both sides are true, so true AND unknown is unknown and the row is dropped; an OR needs one true side, so unknown OR true is true and the row is kept.

• WHERE email = NULL never returns a row. Use IS NULL and IS NOT NULL.
• WHERE city <> 'Riyadh' also drops the rows whose city is NULL, and so does NOT (city = 'Riyadh'). If those rows belong in the result, add OR city IS NULL.
• Arithmetic with NULL gives NULL; COALESCE(delivery_fee, 0) returns its first argument that is not NULL.
• An empty string is not NULL in PostgreSQL, MySQL and SQLite (Oracle Database stores '' as NULL), so a check for a required field looks for both.
• In ascending order PostgreSQL sorts NULL last, MySQL and SQLite first; in descending order each does the reverse. PostgreSQL and SQLite accept NULLS FIRST or NULLS LAST to make the position explicit.

-- Customers without a usable email address
SELECT customer_id, full_name
FROM customers
WHERE email IS NULL OR email = '';

-- Show a default where the value is missing
SELECT order_id, COALESCE(coupon_code, 'none') AS coupon
FROM orders
WHERE customer_id = 207;

Worked example: checking a “My orders” page

Requirement: “My orders lists the customer's orders except cancelled ones, newest first; orders placed at the same time appear by order number, highest first; a page shows at most 10 orders.” The tester first writes the query that produces the expected list from the test data, so the test oracle, the source of the expected result, comes from the rule, not from the screen.

Customer 207 has five orders; order 1003 belongs to another customer. Each line of the query checks one part of the rule:

• customer_id = 207: if order 1003 appears, one customer sees another customer's data, a privacy defect.
• status <> 'CANCELLED': order 1002 is not expected. status is NOT NULL here; if it allowed NULL, this condition would also hide orders without a status.
• ORDER BY created_at DESC, order_id DESC: 1004 and 1005 have the same created_at, so the tie-breaker puts 1005 first. A page that shows 1004 first, or swaps them on reload, breaks the rule.
• LIMIT 10: the page size is a boundary. Test with exactly 10 and 11 non-cancelled orders, as in boundary value analysis: the 11th must appear on page two, and no order twice. LIMIT 10 OFFSET 10 skips the first ten rows and gives the expected page two.

-- Test data: orders
-- order_id  customer_id  status     total_amount  created_at
-- 1001      207          PAID       120.00        2026-09-03 10:15
-- 1002      207          CANCELLED   80.00        2026-09-05 18:40
-- 1003      311          PAID       455.50        2026-09-06 09:05
-- 1004      207          SHIPPED    455.50        2026-09-12 21:30
-- 1005      207          PAID        60.00        2026-09-12 21:30
-- 1006      207          PAID        35.25        2026-09-20 08:00

SELECT order_id, status, total_amount, created_at
FROM orders
WHERE customer_id = 207
  AND status <> 'CANCELLED'
ORDER BY created_at DESC, order_id DESC
LIMIT 10;

-- Expected, top to bottom: 1006, 1005, 1004, 1001

Try it: SQL Dataset Generator

Frequently asked questions

References

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupLexical structure of SQL in PostgreSQL: identifiers, quoting, string constants and escaping.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.