SELECT

SELECT is the statement you'll write more than any other. It reads data from one or more tables and returns a result — itself a table of rows and columns. This lesson covers choosing columns, calculating new ones and naming them with aliases.

Selecting columns

SELECT name, category, price
FROM products;

List the columns you want after SELECT, separated by commas, and the table after FROM. The result has the columns in the order you listed them.

Selecting every column

SELECT * FROM departments;

* means "all columns". It's handy for exploring, but in application code list columns explicitly: it's clearer, sends less data, and doesn't break when someone adds a column to the table.

Calculated columns

SELECT name, price, stock, price * stock
FROM products
WHERE category = 'Home';

A column in the select list can be any expression: arithmetic, function calls, text joined together. The database calculates it for each row.

Column aliases with AS

SELECT name AS product,
       price * stock AS stock_value
FROM products
WHERE category = 'Home';

AS gives a column a new name in the result. Aliases make calculated columns readable and are how applications refer to them. The AS keyword is optional (price * stock stock_value also works), but including it is clearer.

SELECT first_name || ' ' || last_name AS "Full name",
       salary / 12 AS "Monthly salary"
FROM employees
WHERE department_id = 4;

Put an alias in double quotes if it contains spaces or capital letters you want to keep. || joins text (lesson 13).

Selecting values without a table

SELECT 2 + 3 AS answer, 'hello' AS greeting, upper('sql') AS shout;

Many databases (SQLite, PostgreSQL, MySQL, SQL Server) let you SELECT expressions without FROM — useful for testing functions. Oracle requires FROM dual.

Table aliases

SELECT p.name, p.price
FROM products AS p
WHERE p.price > 50;

Tables can have aliases too. They're short to type and essential once queries involve several tables (lesson 23). Prefixing a column with its table (p.name) says exactly where it comes from.

The order clauses are written in

A full SELECT can have many clauses. They must appear in this order, though you'll meet them one at a time:

SELECT   columns
FROM     tables
WHERE    row filter
GROUP BY grouping
HAVING   group filter
ORDER BY sorting
LIMIT    row count;

Try it yourself

List each employee's full name, job title and salary with a 10% raise, giving the raise column a clear name.