SQL SELECT

Read exactly the columns you need, rename them, calculate new values and remove duplicates.

Lesson 2 of 102Beginner17 min read

SELECT is the statement you'll write more than any other. It reads data from a table and returns it as a result set: a new, temporary table containing just the rows and columns you asked for.

SELECT * is handy for a quick look, but real queries usually name the exact columns they need. That makes results easier to read, keeps them small and fast, and protects your code if someone adds new columns to the table later.

In this lesson you'll pick specific columns, give them friendlier names with AS, create calculated columns and remove duplicates with DISTINCT.

Choosing columns

List the columns you want after SELECT, separated by commas. They appear in the result in the order you write them:

SQL
SELECT name, city FROM customers;

You can list columns in any order and leave out the ones you don't need. Column names must match the table exactly, apart from letter case.

Renaming columns with AS

An alias gives a column a different name in the result. The table itself isn't changed.

SQL
SELECT name AS customer, city AS lives_in
FROM customers;

Aliases make results clearer, especially for calculated columns, which otherwise get messy automatic names. If an alias contains a space, wrap it in double quotes: AS "Customer name".

Calculated columns

A column in the result doesn't have to exist in the table. You can do maths with columns and give the answer an alias:

SQL
SELECT name, price, price * 1.2 AS price_with_tax
FROM products;

You can use +, -, * and /, and functions such as ROUND(value, 2) to tidy decimals or UPPER(text) to change case. The calculation runs once for every row.

Removing duplicates with DISTINCT

DISTINCT returns each different value only once:

SQL
SELECT DISTINCT city FROM customers;

Two customers live in Leeds, but Leeds appears just once in the result. With more than one column, DISTINCT removes rows where every listed column is the same.

The order SQL runs a query

You write a query starting with SELECT, but the database works through it in a different, logical order:

StepClauseWhat happens
1FROMChoose the table
2WHEREKeep the matching rows
3GROUP BYGroup rows that share values
4HAVINGKeep the matching groups
5SELECTWork out the columns and aliases
6ORDER BYSort the result
7LIMITKeep the first rows

This order explains several rules you'll meet. An alias made in SELECT doesn't exist yet when WHERE runs, so WHERE price_with_tax > 10 fails in most databases, while ORDER BY price_with_tax works because sorting happens later. You'll use the later clauses in the WHERE and ORDER BY and GROUP BY lessons.

Comments in SQL

SQL has two kinds of comment, both ignored by the database:

SQL
-- prices including 20% VAT
SELECT name,
       price * 1.2 AS price_with_tax   /* rounded later */
FROM products;

-- comments out the rest of the line, and /* ... */ can span several lines. Comments are useful for explaining business rules in longer queries, and for temporarily switching off one line while you test.

SQL SELECT Syntax

SELECT with columns

SELECT column1, column2 AS alias, expression AS alias
FROM table_name;

SELECT DISTINCT column FROM table_name;

SQL SELECT Summary

  • SELECT col1, col2 FROM table; returns only the columns you name.
  • AS gives a column a new name in the result.
  • You can calculate new columns from existing ones, row by row.
  • DISTINCT removes duplicate values or rows.
  • SQL ignores line breaks, so format queries for readability.

SQL SELECT Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Selected columns

SQL
SELECT name, city
FROM customers;
Output
name  | city
------+-------
Ana   | Lisbon
Ben   | Leeds
Chloe | Lyon
Dev   | Leeds
Ema   | Porto
Line-by-line explanation
  1. Line 1Only the two named columns appear, in the order written.
  2. Line 2The table name goes after FROM, as before.
Example 2

Prices with tax

SQL
SELECT name AS product,
       price,
       ROUND(price * 1.2, 2) AS price_with_tax
FROM products;
Output
product      | price | price_with_tax
-------------+-------+---------------
Notebook     | 3.50  | 4.2
Pen          | 1.20  | 1.44
Backpack     | 34.99 | 41.99
Water bottle | 12.00 | 14.4
Lunch box    | 8.75  | 10.5
Desk lamp    | 24.50 | 29.4
Line-by-line explanation
  1. Line 1The name column is shown with the heading "product".
  2. Line 3Each price is multiplied by 1.2, rounded to 2 decimals and named price_with_tax.
Example 3

Unique values

SQL
SELECT DISTINCT category
FROM products;
Output
category
----------
Stationery
Bags
Kitchen
Home
Line-by-line explanation
  1. Line 1Six products, but only four different categories, so four rows come back.

Common SQL SELECT Mistakes

A missing comma between columns

Don't
SELECT name city FROM customers;
Do
SELECT name, city FROM customers;

Without the comma, SQL reads city as an alias for name, so you get one column called city full of names.

A trailing comma

Don't
SELECT name, city, FROM customers;
Do
SELECT name, city FROM customers;

A comma after the last column makes the database expect another one, causing a syntax error.

Using SELECT * in real code

Don't
SELECT * FROM products;
Do
SELECT name, price FROM products;

* is fine for exploring, but in an app it fetches columns you don't need and can break when the table changes.

SQL SELECT in Real Projects

SELECT in real projects

A shop's product page might run SELECT name, price, stock FROM products to fill its grid, while a report might calculate price * quantity AS line_total. Analysts use DISTINCT to answer questions like "which countries do our customers come from?".

SQL SELECT FAQ

Can I use a column alias in WHERE?

In most databases, no, because WHERE is processed before SELECT creates the alias. Repeat the expression, WHERE price * 1.2 > 10, or wrap the query in a subquery or CTE and filter the alias in the outer query. Aliases can be used in ORDER BY.

What is the difference between single and double quotes in SQL?

Single quotes mark text values: 'Leeds'. Double quotes mark identifiers, such as a column alias with a space: AS "Customer name". MySQL also accepts double quotes for text by default, but writing values in single quotes works everywhere.

Does SELECT change the data in the table?

No. SELECT only reads. Calculated columns, aliases and DISTINCT affect the result you see, not the stored table. Changing data needs INSERT, UPDATE or DELETE, which you'll meet in the INSERT, UPDATE and DELETE lesson.

Why does 7 / 2 give 3 instead of 3.5?

In PostgreSQL, SQLite and SQL Server, dividing one integer by another gives an integer, and the fraction is dropped. Make one side a decimal, 7.0 / 2, or convert it with CAST(quantity AS REAL), to get 3.5. MySQL returns a decimal result for integer division with /.

How do I select only the first few rows?

Add LIMIT: SELECT name FROM products LIMIT 3;. Without an ORDER BY, the database may return any three rows, so sort first when "first" should mean something, such as the cheapest. SQL Server uses SELECT TOP 3 instead.

SQL SELECT Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so it returns only the name and price of every product.

SELECT name price FROM ;
Multiple choiceeasy

Which keyword renames a column in the result?

Predict the outputmedium

How many rows does this return for the example shop?

SELECT DISTINCT city FROM customers;
Predict the outputmedium

What does this return for the Pen row?

SELECT name, price * 10 AS ten_pens FROM products;
Predict the outputmedium

How many rows does SELECT DISTINCT city FROM customers; return for this course's customers table?

SQL SELECT Quiz

5 questions to check what you learned.

  1. What does SELECT name, city FROM customers; return?

    • Only the name and city columns, for every row
    • Every column
    • Only the first row
    • The column names without data

  2. What does DISTINCT do?

    • Sorts the result
    • Counts the rows
    • Deletes duplicates from the table
    • Removes duplicate rows from the result

  3. Does an alias change the column name in the table?

    • Yes, permanently
    • No, only in the query's result
    • Only if you use quotes
    • Only for calculated columns

  4. What's wrong with SELECT name price FROM products;?

    • Nothing, it returns both columns
    • It returns one column, name, labelled "price"
    • It returns only price
    • It always causes a syntax error

  5. What does AS do in SELECT price * 1.2 AS with_tax?

    • Filters the rows
    • Sorts the result
    • Creates a new table
    • Gives the calculated column a name

Related SQL topics

01IntroductionWhat databases, tables, rows and columns are, and how to write your first SQL query.03WHERE (Filtering)Return only the rows you need with comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.05ORDER BY & GROUP BYSort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.
Esc
↑↓ to navigateEnter to open