SELECT and FROM: choosing columns

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  T["employees<br/>emp_id, name, dept_id,<br/>manager_id, salary, hire_date"] -->|"FROM: every row"| S["SELECT name, salary,<br/>salary + 5000 AS after_raise"]
  S --> R["result<br/>name, salary, after_raise"]

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.

The shape of it

SELECT column, expression AS new_name
FROM table_name;

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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 on the fly.

Worked examples

SELECT name, salary, salary + 5000 AS after_raise
FROM employees
ORDER BY emp_id;

SELECT dept_name || ' in ' || location AS where_it_is
FROM departments
ORDER BY dept_id;

Must-know points

• SELECT chooses and calculates columns; FROM says which table the rows come from.
• Separate items with commas — and no comma after the last one.
• AS gives a column an alias. Use one for every calculated column, so the result has a readable name.
• SELECT * is fine for looking at a table, but in real queries name the columns you need: it is clearer, and faster on wide tables.
• Without a WHERE, every row of the table is in the result.

Common mistakes

• A trailing comma before FROM: SELECT name, salary, FROM employees is a syntax error.
• Using double quotes for text. 'London' is text; "London" means a column called London in PostgreSQL.
• Using an alias in WHERE in the same query. WHERE runs before SELECT, so the alias doesn't exist yet (Phase 2 explains why).

In other databases

• Joining text: PostgreSQL and SQLite use ||; MySQL uses CONCAT(a, b); SQL Server uses + or CONCAT. CONCAT works almost everywhere.
• AS is optional for column aliases in every major database (salary + 5000 after_raise works), but writing AS is clearer.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 0: Querying

  Harvard, free

• Read

  W3Schools — SQL SELECT Statement

  short page with a try-it editor, free

• Practice

  SQLBolt — Lesson 1: SELECT queries 101

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what SELECT and FROM each do, and what AS is for, using the employees table.

   Show answer

   FROM says which table to read: FROM employees means "look at the employees sheet".

   SELECT says which columns to give back, and in what order: SELECT name, salary returns just those two, even though the table has six. It can also work things out, such as salary + 5000.

   AS gives a column a new name in the result: salary + 5000 AS after_raise. Without it, the column would be called "salary + 5000". AS only renames the result; the table itself does not change.

2. Writefrom a blank page, then run it

   Write a query that returns each product's name, its price, and the price of 3 of them, called price_for_three.

   Show answer

   Work out the new column in SELECT and name it with AS. Every product comes back, because there is no WHERE.

   sqlCopy

   SELECT product_name, price, price * 3 AS price_for_three
   FROM products
   ORDER BY product_id;

   Try it

   7 rows

   product_name	price	price_for_three
   Pen	2	6
   Notebook	5	15
   Mug	8	24
   Desk lamp	25	75
   Backpack	30	90
   Headphones	40	120
   Phone case	15	45

3. Predictsay it before you run it

   How many rows and columns does SELECT name, name FROM employees; return?

   Show answer

   8 rows and 2 columns. There is no WHERE, so you get one row per employee. SELECT returns exactly what you list, and you listed name twice, so both columns are the same name. Asking for a column twice is allowed; it is just pointless.

   sqlCopy

   SELECT name, name
   FROM employees
   ORDER BY emp_id;

   Try it

   8 rows

   name	name
   Asha	Asha
   Ben	Ben
   Chen	Chen
   Dara	Dara
   Eli	Eli
   Fatima	Fatima
   Gus	Gus
   Hana	Hana

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   What does salary + 5000 give for a row where salary is NULL? (No employee here has one — reason it out, then test it with SELECT NULL + 5000;.)

   Show answer

   NULL. NULL means "unknown", and unknown plus 5000 is still unknown. There is no error and no 5000: the NULL quietly spreads through the sum.

   If a missing salary should count as 0, say so: COALESCE(salary, 0) + 5000.

   sqlCopy

   SELECT NULL + 5000;

   Try it

   1 row

   NULL + 5000
   NULL

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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, and I avoid SELECT star in production queries: naming columns is clearer, it reads less data, and the query doesn't silently change when someone adds a column.

Follow-up questions

Tick each check when you can do it without notes