SQL Select

The SELECT statement is the most commonly used command in SQL — it retrieves data from one or more tables in a database. Rather than changing anything, SELECT simply asks the database engine to find and return rows that match the criteria you specify, in the shape you ask for. Mastering SELECT — its clauses, execution order, and pitfalls — is the foundation for everything else in SQL, from filtering and sorting to joins and aggregation.

Overview: How SELECT Works

A SELECT query describes the data you want, not the steps to fetch it — SQL is declarative. Given that description, the database engine’s query planner decides how to actually retrieve the rows: which table(s) to scan, whether an index can be used to avoid scanning every row, what order to evaluate filters in, and how to combine and sort the results. This is very different from an imperative language: you never tell SQL to loop through rows — you describe the final result set and the engine works out the plan.

Internally, most database engines (including SQLite) evaluate a query in a fixed logical order regardless of how you typed the clauses: the engine first determines the source rows (FROM, including any joins), then filters them (WHERE), then groups them (GROUP BY), then filters the groups (HAVING), then computes the requested columns (SELECT), then removes duplicates (DISTINCT) if requested, then sorts (ORDER BY), and finally trims the result to a page (LIMIT/OFFSET). This logical order explains many behaviors that otherwise look like quirks — for example, why you cannot reference a column alias inside a WHERE clause (covered in Common Mistakes below).

If a suitable index exists on a filtered or joined column, the engine can use it to jump directly to matching rows instead of scanning the whole table. Indexing frequently-filtered columns matters for performance as tables grow, even though it never changes what a SELECT returns — only how fast it returns it.

Syntax

The general form of a SELECT statement is:

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column [ASC|DESC]
LIMIT count;

Clause	Purpose	Required?
SELECT	Lists the columns or expressions to return	Yes
FROM	Names the table(s) the data comes from	Yes (for most queries)
WHERE	Filters individual rows before grouping	No
GROUP BY	Collapses rows into groups for aggregation	No
HAVING	Filters groups after aggregation	No
ORDER BY	Sorts the final result set	No
LIMIT	Caps the number of rows returned	No

A minimal, real query against the sample employees table looks like this:

SELECT name, department FROM employees;

This returns every row from employees, but only the name and department columns — omitting id, salary, and hire_date entirely. Using SELECT * instead would return all columns, in the table’s defined column order.

Examples

Example 1: Selecting specific columns

CREATE TABLE students (id INTEGER, name TEXT, grade INTEGER, score REAL);
INSERT INTO students (id, name, grade, score) VALUES
  (1, 'Alice', 10, 92.5),
  (2, 'Bob', 11, 78.0),
  (3, 'Charlie', 10, 85.0),
  (4, 'Dana', 12, 88.5);

SELECT name, score FROM students;

Result:

name	score
Alice	92.5
Bob	78.0
Charlie	85.0
Dana	88.5

Because there’s no ORDER BY, SQLite returns the rows in the order they were inserted. Only the two named columns appear — id and grade are still in the table but not part of this result set.

Example 2: Filtering and sorting

CREATE TABLE students (id INTEGER, name TEXT, grade INTEGER, score REAL);
INSERT INTO students (id, name, grade, score) VALUES
  (1, 'Alice', 10, 92.5),
  (2, 'Bob', 11, 78.0),
  (3, 'Charlie', 10, 85.0),
  (4, 'Dana', 12, 88.5);

SELECT name, grade, score
FROM students
WHERE grade = 10
ORDER BY score DESC;

Result:

name	grade	score
Alice	10	92.5
Charlie	10	85.0

WHERE grade = 10 keeps only Alice and Charlie (Bob is grade 11, Dana is grade 12), and ORDER BY score DESC then sorts the surviving rows from highest to lowest score.

Example 3: Expressions, CASE, and LIMIT

CREATE TABLE students (id INTEGER, name TEXT, grade INTEGER, score REAL);
INSERT INTO students (id, name, grade, score) VALUES
  (1, 'Alice', 10, 92.5),
  (2, 'Bob', 11, 78.0),
  (3, 'Charlie', 10, 85.0),
  (4, 'Dana', 12, 88.5);

SELECT name, score,
       score - 60 AS bonus_margin,
       CASE
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         ELSE 'C'
       END AS grade_letter
FROM students
WHERE grade IN (10, 11)
ORDER BY score DESC
LIMIT 2;

Result:

name	score	bonus_margin	grade_letter
Alice	92.5	32.5	A
Charlie	85.0	25.0	B

WHERE grade IN (10, 11) keeps Alice, Bob, and Charlie (Dana is grade 12, so she’s excluded). SELECT can compute new values on the fly — score - 60 and the CASE expression are not stored columns, they’re calculated per row. After sorting by score descending, LIMIT 2 keeps only the top two rows, so Bob (78.0) is cut off.

How It Works Step by Step: Logical Query Processing Order

SQL engines evaluate a SELECT in a fixed logical order that is different from the order you type the clauses in. Understanding this order explains a lot of “why doesn’t this work” moments:

CREATE TABLE sales (id INTEGER, region TEXT, amount REAL);
INSERT INTO sales (id, region, amount) VALUES
  (1, 'East', 100),
  (2, 'East', 150),
  (3, 'West', 200),
  (4, 'West', 50),
  (5, 'North', 300);

SELECT region, SUM(amount) AS total, COUNT(*) AS cnt
FROM sales
WHERE amount > 60
GROUP BY region
HAVING COUNT(*) >= 2
ORDER BY total DESC;

Result:

region	total	cnt
East	250.0	2

1. FROM — the engine starts with all 5 rows of sales.
2. WHERE amount > 60 — row 4 (West, 50) is dropped for failing the filter, leaving 4 rows.
3. GROUP BY region — the remaining rows collapse into 3 groups: East (100, 150), West (200), North (300).
4. HAVING COUNT(*) >= 2 — only the East group has 2 or more rows, so West and North are dropped.
5. SELECT — for the surviving East group, SUM(amount) is computed as 250 and COUNT(*) as 2.
6. ORDER BY — with only one row left, sorting has nothing left to reorder.

Notice that WHERE runs before grouping, so it filters individual rows (row 4 never even reaches the grouping step), while HAVING runs after grouping, so it filters entire groups based on an aggregate condition. Mixing these two up is one of the most common SQL mistakes.

Common Mistakes

Mistake 1: Referencing a SELECT alias inside WHERE

CREATE TABLE students (id INTEGER, name TEXT, score REAL);
INSERT INTO students (id, name, score) VALUES (1, 'Alice', 92.5), (2, 'Bob', 78.0);

SELECT name, score AS pct
FROM students
WHERE pct > 80;

This fails with an error like no such column: pct. It looks reasonable, but because WHERE is logically evaluated before SELECT, the alias pct doesn’t exist yet when WHERE runs — aliases only become visible starting at the SELECT/ORDER BY stage. The fix is to filter on the real column or expression instead:

CREATE TABLE students (id INTEGER, name TEXT, score REAL);
INSERT INTO students (id, name, score) VALUES (1, 'Alice', 92.5), (2, 'Bob', 78.0);

SELECT name, score AS pct
FROM students
WHERE score > 80;

Result: one row — Alice, 92.5 (displayed under the column header pct). Bob’s score of 78.0 doesn’t satisfy score > 80, so he’s excluded.

Mistake 2: Comparing to NULL with equals

CREATE TABLE students (id INTEGER, name TEXT, notes TEXT);
INSERT INTO students (id, name, notes) VALUES (1, 'Alice', NULL), (2, 'Bob', 'good');

SELECT name FROM students WHERE notes = NULL;

Result: an empty result set — zero rows, even though Alice’s notes is actually NULL. This query runs without error, which makes the mistake easy to miss. In SQL, NULL means “unknown,” so any comparison against it evaluates to unknown rather than true, and WHERE only keeps rows where the condition is true. The correct way to test for NULL is the IS NULL operator:

CREATE TABLE students (id INTEGER, name TEXT, notes TEXT);
INSERT INTO students (id, name, notes) VALUES (1, 'Alice', NULL), (2, 'Bob', 'good');

SELECT name FROM students WHERE notes IS NULL;

Result: one row — Alice. Use IS NOT NULL for the opposite check.

Best Practices

• Name the exact columns you need instead of SELECT * in application code — it’s faster, clearer, and won’t silently break if the table gains new columns.
• Filter with WHERE as early and specifically as possible; reserve HAVING for conditions on aggregated values.
• Always pair LIMIT with an ORDER BY — without a defined sort order, “the first N rows” is not guaranteed to be stable across runs.
• Use IS NULL / IS NOT NULL to test for missing values; never = NULL.
• Give computed expressions and aggregates readable aliases with AS so results are self-documenting.
• Index columns that are frequently used in WHERE, join, or ORDER BY clauses to help the query planner avoid full table scans.
• Use DISTINCT deliberately, not as a reflex to “fix” duplicate rows — duplicates are often a sign of an unintended join or missing filter.

Practice Exercises

1. Using the employees sample table (columns: id, name, department, salary, hire_date), write a query that returns just the name and salary columns for everyone in the Engineering department, sorted by salary from highest to lowest.
2. Using the products sample table (columns: product_id, product_name, price, category), write a query that lists the distinct category values that appear in the table.
3. Using the orders sample table (columns: order_id, customer_id, order_date, amount), write a query that returns the single highest-amount order, including all of its columns. Hint: combine ORDER BY with LIMIT 1.

Summary

• SELECT retrieves rows from a table without modifying any data; it describes the desired result, and the engine decides how to fetch it.
• The logical processing order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, regardless of the order the clauses are written in.
• WHERE filters rows before grouping; HAVING filters groups after aggregation.
• Column aliases created with AS aren’t visible inside WHERE, because WHERE runs before the SELECT list is evaluated.
• NULL must be tested with IS NULL / IS NOT NULL, never with =.
• Prefer explicit column lists over SELECT *, and always pair LIMIT with ORDER BY for predictable results.