# SELECT from a Table

> Read the sample fruits table with explicit columns, aliases, and filters.

---

LLMS index: [llms.txt](/en/llms.txt)

---

Now that `fruits` has rows, you can read them. `SELECT *` returns every column; listing column names returns just those, in the order you list them. The result is unordered unless you add `ORDER BY` — even if it looks ordered today, it may not be tomorrow.

## What you'll learn {#learn}

- `SELECT * FROM table` for all columns
- Selecting specific columns and controlling their order in the result
- Combining `SELECT` with `WHERE` and `ORDER BY`
- Why `SELECT *` is fine for ad-hoc queries but risky in application code
- Result column names and how to override them

> [!NOTE] Prepare the sample table
> Before this lesson, run `psql -f source/create-table.sql -f source/insert.sql postgres` in your scratch database. These are the files introduced in chapters 16 and 24; the table must exist before the SELECT lessons.

## Example {#example}

```sql {num="6.1" caption="SELECT from a Table"}
-- All columns. The order matches CREATE TABLE.
SELECT * FROM fruits ORDER BY id;

-- Specific columns, in the order you list them
SELECT name, id FROM fruits ORDER BY id;

-- Renaming output columns
SELECT id AS fruit_id, name AS fruit_name FROM fruits ORDER BY id LIMIT 3;

-- Combining a filter and an order
SELECT id, name
FROM fruits
WHERE id BETWEEN 2 AND 4
ORDER BY name;

-- Counting rows
SELECT count(*) AS total FROM fruits;
```

Run this after `create-table.sql` and `insert.sql`. `SELECT *` returns every column in the *physical* column order. Listing columns gives you total control over the result shape, which matters when the data flows into application code that maps columns by position. `AS` renames a column in the output without changing anything in the table.

The space character is optional around `*` and around operators; the SQL parser is generous with whitespace. Multi-line queries are normal and encouraged for anything beyond two columns.

## Run the example {#run}

To run:

```text
$ psql -f source/select-from-table.sql postgres
 id  |   name
-----+-----------
   1 | apple
   2 | banana
   3 | cherry
   4 | date
   5 | elderberry
 101 | fruit_1
 102 | fruit_2
 103 | fruit_3
(8 rows)

    name    | id
------------+-----
 apple      |   1
 banana     |   2
 ...
```

## Common pitfalls {#pitfalls}

- `SELECT * FROM big_table;` will fetch every column of every row across the network — slow and wasteful. List the columns you actually need.
- Output column names are not guaranteed unique. `SELECT id, id FROM fruits` has two columns both named `id` — application drivers may not handle that gracefully.
- In application code, prefer explicit columns over `SELECT *`. When the schema adds a column, the shape of `SELECT *` changes and may break code that assumes a particular column order or count.

> [!TIP] Tip
> When you're exploring data, `\x` in psql toggles *expanded display* — each row prints one column per line. It is much easier to read a wide row that way.

> [!NOTE] Try it
> Run `SELECT * FROM fruits WHERE id > 100;` to see only the generated rows. Then run `SELECT name FROM fruits ORDER BY name;` for alphabetical names. Then try `SELECT id || ': ' || name AS label FROM fruits;` to build a label column with the `||` string concatenation operator.

## Downloads {#downloads}

SQL file: [select-from-table.sql](/examples/source/select-from-table.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)