SELECT and FROM: choosing columns

SELECT lists the columns (or calculations) you want in the result, and FROM names the table they come from.

Why it matters

A table can have dozens of columns, and you usually need two or three. You also often need values that are not stored at all — a salary after a raise, a name and city joined into one label. SELECT lets you choose and calculate exactly the columns you need.

The idea

After SELECT you write a list, separated by commas. Each item can be a column name (salary), a calculation (salary + 5000), or a fixed value ('hello'). AS gives an item a new name in the result, called an alias. SELECT * means every column, in table order.

The rows come from the table named after FROM, and every row of that table is used, because there is no filter yet.

Ordering a set menu but saying "just the starter and the dessert, please". FROM is the menu you are ordering from; SELECT is the courses you want on your plate, in the order you name them.

A waiter can only bring what is on the menu. SELECT can also bring things that don't exist in the table, because it can calculate new values from the columns as it runs.

Syntax

SELECT column, expression AS new_name
FROM table_name;

How it works

  1. The database reads FROM first: it takes every row of employees.
  2. For each row, it works out every item in the SELECT list: name, salary, and salary + 5000.
  3. AS after_raise names the calculated column; without it, the column would be called "salary + 5000".
  4. The result has one row for every row of the table, with just these columns.
  5. Nothing is saved. The calculated column exists only in this result.

Key points

Common mistakes

Not true: An alias defined in SELECT can be used in the WHERE of the same query.
In fact: WHERE runs before SELECT, so the alias does not exist yet. Repeat the calculation in WHERE instead.

In the interview

SELECT defines the columns of the result — stored columns, expressions, or constants — and FROM names the source table. I alias every calculated column with AS, so the output is readable. I avoid SELECT star in production queries: naming the columns is clearer, it reads less data, and the query doesn't silently change when someone adds a column.

Part of Unit 1: Reading data: SELECT, WHERE and sorting in the free SQL course.