Lesson 5 of 22Beginner14 min

SELECT

By the end of this lesson

Retrieve columns and rows, and alias results readably.

SELECT asks the database a question and gets a table back. Every query you write for the rest of your career starts here.

The shape is short: name the columns you want, name the table they come from. The database works out how to find them, which is the point of SQL — you describe the result, not the steps.

The smallest useful query

SQL

SELECT company_name, country, credit_limit
FROM customers;

SELECT lists the columns you want back, in the order you want them.
FROM names the table. Column order in the result follows your SELECT list, not the table definition.
There is no WHERE, so this returns every row. On a table with two million customers, it returns two million rows.

SELECT * is for exploring, not for code

SELECT * returns every column. It is genuinely useful when you are looking at an unfamiliar table and want to see what is in it.

In application code or a saved report it causes two problems. You transfer columns nobody needed, which costs time and memory on large tables. And the result changes shape whenever someone adds a column, so code that reads results by position breaks without any warning.

Name your columns. It is a few more keystrokes once, and it documents what the query actually needs.

Aliases make results readable

Renaming columns and naming calculations

SQL

SELECT
    p.product_name          AS product,
    p.unit_price            AS price_each,
    p.units_in_stock        AS in_stock,
    p.unit_price * p.units_in_stock AS stock_value
FROM products AS p
WHERE p.discontinued = 0;

AS gives a column a different name in the result. The stored column name does not change.
The fourth line is a calculated column. It does not exist in the table — the database works it out per row. Without an alias it comes back with no usable name, which is why stock_value matters here rather than being decoration.
FROM products AS p gives the table a short alias. p.product_name then means "product_name from products". With one table this is optional; once you join tables it stops being optional, so the habit is worth forming now.
discontinued = 0 filters on a BIT column, where 0 is false and 1 is true. PostgreSQL would write discontinued = false.

DISTINCT removes duplicate rows

Which countries do we have customers in?

SQL

-- Every row, with countries repeated
SELECT country FROM customers;

-- Each country once
SELECT DISTINCT country FROM customers;

-- Each country and city combination once
SELECT DISTINCT country, city FROM customers;

DISTINCT applies to the whole row of the result, not to one column. The third query returns one row per country and city pairing, so a country with four cities appears four times.
DISTINCT has to compare and deduplicate every row, so it is real work. Reaching for it because a query returns unexpected duplicates usually hides a problem rather than fixing it — that comes up again in the joins lesson.

The order you write a query is not the order it runs

You write SELECT first, but the database resolves FROM first. This explains several errors that otherwise look arbitrary:

FROM — work out which table or tables the rows come from
WHERE — discard rows that do not match
GROUP BY — collapse the remaining rows into groups (a later lesson)
HAVING — discard groups that do not match (a later lesson)
SELECT — work out the output columns, including aliases
ORDER BY — sort the result

Because SELECT is resolved after WHERE, an alias you create in the SELECT list does not exist yet when WHERE runs. WHERE stock_value > 1000 fails with "invalid column name". Repeat the expression, or wrap the query in a CTE, which is a later lesson.

ORDER BY is resolved after SELECT, so ORDER BY stock_value does work. That asymmetry is confusing until you know the order, and obvious afterwards.

Summary

SELECT names the columns; FROM names the table, and the result is always a table
SELECT * is for exploring — named columns transfer less and do not break when a column is added
AS renames columns and gives calculated expressions a usable name
A query is resolved FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, which explains where aliases are and are not available

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Try it yourself

Write a query against employees that returns the full name as one column called employee, the job title, and the hire date. Use aliases so every column in the result reads well.

Show solution

Joining two text columns needs a concatenation operator: + in SQL Server, || in PostgreSQL, or CONCAT() in both plus MySQL.

The space between the names is easy to forget, and it produces "PriyaSharma" rather than an error. Checking the actual output rather than assuming it worked is the habit being practised here.

CONCAT() is worth preferring where it exists: if any part is NULL, + gives NULL for the whole string, while CONCAT() treats NULL as empty text.

SQL

SELECT
    e.first_name + ' ' + e.last_name AS employee,
    e.job_title                      AS role,
    e.hire_date                      AS started
FROM employees AS e;

-- Safer when a name part might be missing
SELECT
    CONCAT(e.first_name, ' ', e.last_name) AS employee,
    e.job_title                            AS role,
    e.hire_date                            AS started
FROM employees AS e;

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

Why does WHERE stock_value > 1000 fail when stock_value is an alias defined in the SELECT list?

Aliases can only be used on stored columnsWHERE is resolved before SELECT, so the alias does not exist yetCalculated columns cannot be filteredThe alias needs quotation marks in WHERE

Saved in this browser only.