SELECT & FROM: Choosing Columns

SELECT * got you in the door, but real queries almost never ask for everything — they ask for exactly the columns that answer the question. This lesson teaches projection (choosing columns), puts expressions in your select list so queries can compute, and hands you the professional habit of saying what you mean instead of *.

Choosing columns (projection)

Name the columns you want after SELECT, comma-separated, and the grid narrows to your question:

SELECT name, price FROM menu_items;

      name       | price
-----------------+-------
 Espresso        |  2.50
 Cappuccino      |  3.80
 Flat White      |  4.00
 Cold Brew       |  4.50
 Earl Grey       |  3.00
 Sencha          |  3.40
 Croissant       |  3.20
 Cinnamon Roll   |  4.10
 Banana Bread    |  3.60
 Sparkling Water |  2.20
(10 rows)

The database calls this projection — same rows, fewer facts per row. Two details worth noticing. You choose the order: SELECT price, name returns the columns swapped; the grid follows your select list, not the table's schema. And misspell a column and the parser has your back, often with a startlingly good guess:

SELECT nam FROM menu_items;

ERROR:  column "nam" does not exist
LINE 1: SELECT nam FROM menu_items
               ^
HINT:  Perhaps you meant to reference the column "menu_items.name".

That HINT line is Postgres being a good colleague. The reading ritual from 1.4 gets a third step: what, where — and, when offered, the fix.

Why SELECT * is for exploring, not for production

* is the right tool exactly once per table: the first time you meet it. In lasting code — the query inside an app, a report, a dashboard — professionals name their columns, for three reasons that all bite eventually:

• It says what the query means. SELECT name, price documents which facts the code depends on; * says "whatever happens to be there."
• It survives schema changes. Add a column next year and the * query silently starts returning it — new data flowing into code that never asked, in an order you don't control.
• It moves less. In Part IV you'll see the performance cost of hauling columns nobody reads; SELECT * is I/O you didn't need, every time the query runs.

Exploring at the sql> prompt? * away. Writing a query that will outlive the afternoon? Name the columns.

Expressions: the select list can compute

Anything after SELECT can be an expression, not just a column name. Arithmetic on numeric columns works the way you'd hope — here's the menu with a 10%-off preview:

SELECT name, price, price * 0.9
FROM menu_items
WHERE category = 'coffee';

    name    | price | ?column?
------------+-------+----------
 Espresso   |  2.50 |    2.250
 Cappuccino |  3.80 |    3.420
 Flat White |  4.00 |    3.600
 Cold Brew  |  4.50 |    4.050
(4 rows)

(That WHERE line is next lesson's star — for now, read it as "coffee only.") Notice the computed column's header: ?column?. The database has no idea what to call price * 0.9, so it shrugs in Postgres. The cure is an alias — price * 0.9 AS sale_price — which lesson 2.4 makes official; try adding it in the playground below and watch the header change.

Gluing text: concatenation

The || operator joins text values — column to column, column to literal:

SELECT name || ' (' || category || ')'
FROM menu_items
WHERE in_stock = false;

      ?column?
--------------------
 Cold Brew (coffee)
 Sencha (tea)
(2 rows)

|| is the SQL standard's spelling, and Postgres's. The portable alternative you'll meet in other codebases is the CONCAT(a, b, …) function — same idea, function-shaped; MySQL historically treats || as logical OR unless configured otherwise, which is why cross-engine code often prefers CONCAT. (Accents, as promised. Section 20 has the full map.)

You can now choose your facts and compute new ones. The other half of precision — choosing your rows — is WHERE, and it's next.