DISTINCT, Aliases and Expressions

Tidy up your results, rename your columns, and do maths right inside SELECT

Big Idea: A SELECT does not have to show columns exactly as they are stored. You can hide repeated values with DISTINCT, give columns friendlier names with aliases, and even calculate brand-new values with expressions like price * quantity or CONCAT(...).

By the end you will be able to:

remove repeated rows from a result with SELECT DISTINCT,
rename a column or a table with an alias using AS,
do arithmetic (+ - * /) inside a SELECT,
join text together with CONCAT(),
tell the difference between what is stored and what is only shown.

0. Our example table

Every query on this page uses one tiny shop table called products. Here is how it was built and filled:

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    category VARCHAR(50),
    price INT,
    quantity INT
);

INSERT INTO products (id, name, category, price, quantity) VALUES
(1, 'Notebook',  'Stationery', 3,  10),
(2, 'Pen',       'Stationery', 1,  50),
(3, 'Backpack',  'Bags',       20, 5),
(4, 'Pencil',    'Stationery', 1,  30),
(5, 'Lunchbox',  'Bags',       8,  12);

So the whole table looks like this:

id	name	category	price	quantity
1	Notebook	Stationery	3	10
2	Pen	Stationery	1	50
3	Backpack	Bags	20	5
4	Pencil	Stationery	1	30
5	Lunchbox	Bags	8	12

Notice that the category column repeats: "Stationery" appears three times and "Bags" appears twice. Keep that in mind for the very next section.

1. DISTINCT: only the different ones

Sometimes a column has the same value over and over, and you only want to see each different value once. The keyword DISTINCT does exactly that: it throws away repeated rows and keeps only the unique ones.

Think of a bag of coloured sweets. If you tip them out you might see red, red, blue, red, blue. But if a friend asks "which colours are in the bag?", you would just answer "red and blue" — you say each colour once. That is what DISTINCT does.

Without DISTINCT, asking for every category gives you a repeat for every row:

SELECT category
FROM products;

category
Stationery
Stationery
Bags
Stationery
Bags

Now add DISTINCT right after SELECT and the repeats vanish:

SELECT DISTINCT category
FROM products;

category
Stationery
Bags

Five rows became two, because there are only two different categories.

DISTINCT on more than one column: when you list several columns, DISTINCT keeps each unique combination of those columns, not each single value. For example SELECT DISTINCT category, price FROM products; keeps a row for every different (category, price) pair.

DISTINCT, Aliases and Expressions