SQL Basics: SELECT, WHERE, ORDER BY and LIMIT

The shape of a first query

A read query in SQL is almost always built from the same parts in a fixed order:

SELECT columns
FROM table
WHERE condition
ORDER BY sorting
LIMIT count;

• SELECT — which columns to show.
• FROM — which table to read.
• WHERE — which rows to keep.
• ORDER BY — how to sort them.
• LIMIT — how many rows to return.

Only SELECT and FROM are required; the rest is added when needed. The order of the parts cannot change.

The sample orders table

All examples below use this orders table:

SELECT: choosing columns

SELECT customer, total FROM orders;

SELECT * returns every column. It is handy for exploring a table, but in application code list the columns explicitly: the query is faster and will not break when someone adds new fields.

WHERE: filtering rows

SELECT id, customer, total
FROM orders
WHERE status = 'paid' AND total > 300000;

Core operators:

• comparison: =, <> (not equal), >, <, >=, <=;
• logic: AND, OR, NOT;
• range: total BETWEEN 100000 AND 500000 (both ends included).

When mixing AND and OR, use parentheses — AND is evaluated first:

WHERE city = 'Tashkent' AND (status = 'new' OR status = 'paid')

IN and LIKE

IN replaces a chain of ORs:

SELECT * FROM orders WHERE city IN ('Tashkent', 'Bukhara');

LIKE matches a pattern: % means any number of characters, _ means exactly one.

SELECT * FROM orders WHERE customer LIKE 'Al%';

Note that in PostgreSQL LIKE is case-sensitive (use ILIKE for case-insensitive search), while in MySQL it depends on the collation. A pattern that starts with % usually cannot use a regular index and gets slow on large tables.

NULL needs special care

NULL means "unknown value" — not zero and not an empty string. Order 5 has no total yet.

-- wrong: returns no rows
SELECT * FROM orders WHERE total = NULL;

-- correct
SELECT * FROM orders WHERE total IS NULL;
SELECT * FROM orders WHERE total IS NOT NULL;

Any comparison with NULL yields "unknown", so the row fails the filter. That is also why WHERE total < 100000 will not show order 5. To substitute a default value, use COALESCE(total, 0).

ORDER BY and LIMIT

SELECT customer, total, created_at
FROM orders
WHERE status <> 'cancelled'
ORDER BY total DESC, created_at ASC
LIMIT 3;

• ASC sorts ascending (the default), DESC descending.
• You can sort by several columns: first by the first one, then by the second on ties.
• Where NULLs land in the sort order depends on the DBMS. PostgreSQL lets you set it explicitly with NULLS LAST.
• LIMIT works in PostgreSQL, MySQL and SQLite. SQL Server uses TOP or OFFSET ... FETCH instead.

Without ORDER BY, row order is not guaranteed, so LIMIT without sorting may return different rows each time.

Common beginner mistakes

• Double quotes for text. Strings go in single quotes: 'paid'. In standard SQL, double quotes mark column names.
• = NULL instead of IS NULL.
• Missing parentheses when combining AND and OR.
• NOT IN with a list that contains NULL — the query returns no rows at all.
• LIMIT without ORDER BY — your "top 10" ends up random.
• Practicing on a production database. Use a copy while you learn.