How does the SELECT statement work?

Question

Accepted Answer

**`SELECT`** retrieves data from one or more tables — it's the most-used SQL statement. You specify which columns you want and from which table, optionally filtering, sorting, and limiting the results.

## Basic SELECT

```sql
SELECT name, email              -- specific columns
FROM users;

SELECT *                        -- ALL columns (convenient, but avoid in production code)
FROM users;

SELECT name AS full_name        -- AS gives a column an alias (renamed in the output)
FROM users;
```

You list the columns to return after `SELECT` (or `*` for all), and the table after `FROM`. Aliases (`AS`) rename columns in the result.

## Avoid SELECT * in application code

```sql
-- ❌ SELECT * fetches every column — wasteful, fragile (breaks if columns change)
SELECT * FROM users;
-- ✅ select only what you need — less data transferred, clearer intent, more stable
SELECT id, name, email FROM users;
```

While `SELECT *` is handy for exploration, in real code you should select only the columns you need — it transfers less data, makes intent clear, and won't break if the table's columns change.

## Computed columns and expressions

```sql
SELECT
  name,
  price * quantity AS total,       -- a calculated column
  UPPER(name) AS name_upper,        -- a function applied to a column
  price * 1.1 AS price_with_tax
FROM orders;
```

`SELECT` can include expressions and functions, not just plain columns.

## The logical order of a query

```sql
SELECT name                      -- 5. select columns (runs near-last logically)
FROM users                       -- 1. from the table
WHERE active = true              -- 2. filter rows
GROUP BY country                 -- 3. group
HAVING COUNT(*) > 5              -- 4. filter groups
ORDER BY name                    -- 6. sort
LIMIT 10;                        -- 7. limit
```

```text
LOGICAL execution order (NOT the written order):
  FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
→ This is why you can't use a SELECT alias in WHERE (WHERE runs before SELECT).
```

## Why it matters

`SELECT` is the foundation of querying data — the most-used SQL statement, central to reading from any relational database, so mastering it is essential everyday knowledge.

Understanding how to specify columns, use aliases, and include computed expressions covers the basics of retrieving and shaping data.

An important practice is **avoiding `SELECT *` in application code** (selecting only needed columns transfers less data, is clearer, and is more robust to schema changes — a common best practice).

Particularly valuable is understanding the **logical execution order** of a query (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT), which differs from the written order and explains real behaviors — like why you can't reference a `SELECT` alias in a `WHERE` clause (because WHERE logically runs before SELECT).

This mental model of how queries actually process is fundamental to writing correct queries and debugging confusing errors.

Since reading data with `SELECT` is the most common database operation, and since understanding column selection, aliases, expressions, and especially the logical query order is foundational to all querying, mastering the `SELECT` statement is core, must-know knowledge for anyone working with SQL databases.