Himanshu SharmaSoftware engineer
Let’s talk

MySQL · Lesson 10 of 50

DISTINCT and aliases

Remove duplicate rows with DISTINCT, count unique values, and rename columns and tables with AS aliases to make queries and results easier to read.

11 minReading dataUpdated 22 October 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonList unique values with DISTINCT and rename columns and tables with aliases.

Duplicates in results

A table's rows can repeat values: many customers live in India, many orders share a status. When you only care about which values exist, not how often, ask for them once.

DISTINCT

Put DISTINCT right after SELECT:

sql
SELECT DISTINCT country FROM customers;
result
+-------------+
| country     |
+-------------+
| India       |
| UK          |
| France      |
| South Korea |
| Italy       |
| Nigeria     |
| Japan       |
| Portugal    |
| USA         |
| Ireland     |
+-------------+

Twelve customers, but each country appears only once. DISTINCT looks at the whole selected row. With more than one column it removes only rows that are identical in every selected column:

sql
SELECT DISTINCT customer_id, status FROM orders ORDER BY customer_id, status;
result
+-------------+-----------+
| customer_id | status    |
+-------------+-----------+
|           1 | shipped   |
|           1 | delivered |
|           2 | pending   |
|           2 | delivered |
|           3 | shipped   |
|           3 | delivered |
|           4 | delivered |
|           5 | cancelled |
|           6 | pending   |
|           6 | shipped   |
|           6 | delivered |
|           7 | pending   |
|           8 | delivered |
|           8 | cancelled |
|          10 | shipped   |
|          10 | delivered |
|          11 | delivered |
+-------------+-----------+

Customer 1 appears several times, but only once for each different status they have had. This is a common point of confusion: DISTINCT is not a function applied to the first column. It applies to the entire row you select.

Counting different values

To count the unique values, put DISTINCT inside COUNT:

sql
SELECT COUNT(country) AS rows_with_country, COUNT(DISTINCT country) AS different_countries FROM customers;
result
+-------------------+---------------------+
| rows_with_country | different_countries |
+-------------------+---------------------+
|                12 |                  10 |
+-------------------+---------------------+

COUNT(country) counts rows; COUNT(DISTINCT country) counts different values. The aggregate functions lesson returns to this.

Column aliases

An alias is a temporary name for a column in the result. It does not change the table. Use AS:

sql
SELECT first_name AS given_name, last_name AS family_name FROM customers LIMIT 3;
result
+------------+-------------+
| given_name | family_name |
+------------+-------------+
| Asha       | Verma       |
| Ben        | Carter      |
| Chloe      | Dubois      |
+------------+-------------+

Aliases matter most for expressions, which otherwise get an unreadable header:

sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name, UPPER(city) AS city_upper FROM customers LIMIT 3;
result
+--------------+------------+
| full_name    | city_upper |
+--------------+------------+
| Asha Verma   | DELHI      |
| Ben Carter   | LONDON     |
| Chloe Dubois | PARIS      |
+--------------+------------+

If the alias contains a space or is a reserved word, wrap it in backticks:

sql
SELECT name AS `Product name`, price AS `Unit price` FROM products LIMIT 2;
result
+---------------------+------------+
| Product name        | Unit price |
+---------------------+------------+
| Wireless Mouse      |      24.99 |
| Mechanical Keyboard |      89.50 |
+---------------------+------------+

Prefer simple names without spaces, such as full_name, when other code (like an application) reads the result.

Where aliases work

A common surprise: you cannot use a column alias in WHERE.

sql
SELECT price * stock AS stock_value FROM products WHERE stock_value > 5000;
result
ERROR 1054 (42S22): Unknown column 'stock_value' in 'where clause'

MySQL evaluates WHERE before it computes the select list, so stock_value does not exist yet. You can repeat the expression, or use a subquery. Aliases do work in ORDER BY, because sorting happens after the select list:

sql
SELECT name, price * stock AS stock_value FROM products ORDER BY stock_value DESC LIMIT 3;
result
+-----------------------------+-------------+
| name                        | stock_value |
+-----------------------------+-------------+
| SQL Basics Handbook         |     5990.00 |
| Mechanical Keyboard         |     5370.00 |
| Noise-Cancelling Headphones |     4999.75 |
+-----------------------------+-------------+

Table aliases

Tables can have aliases too. With one table it is a convenience, but with joins, which come soon, it becomes essential, because it shortens column references and lets you use the same table twice.

sql
SELECT p.name, p.price FROM products AS p WHERE p.price > 100;
result
+-----------------------------+--------+
| name                        | price  |
+-----------------------------+--------+
| Noise-Cancelling Headphones | 199.99 |
+-----------------------------+--------+

Once you give a table an alias, use the alias everywhere in that query. Writing products.price after FROM products AS p is an error. Short, meaningful aliases such as c for customers and o for orders are common.

Common mistakes

  • Expecting DISTINCT to apply to one column when you select several.
  • Using DISTINCT to hide a bug. If a join produces duplicate rows, the cause is usually the join condition. Fix that rather than adding DISTINCT.
  • Using an alias in WHERE.
  • Quoting an alias with single quotes in one place and backticks in another. Pick backticks for names.
  • Using DISTINCT on huge results without need, which makes the server sort or hash everything.

Exercise

Write queries that return: (1) the different order statuses in the orders table, (2) the number of different cities customers live in, and (3) each product's name as item and price * 1.18 as price_with_tax, sorted by price_with_tax descending, top three.

Show one possible answer
sql
SELECT DISTINCT status FROM orders;
SELECT COUNT(DISTINCT city) AS cities FROM customers;
SELECT name AS item, price * 1.18 AS price_with_tax FROM products ORDER BY price_with_tax DESC LIMIT 3;
result
+-----------+
| status    |
+-----------+
| delivered |
| shipped   |
| cancelled |
| pending   |
+-----------+
+--------+
| cities |
+--------+
|     12 |
+--------+
+-----------------------------+----------------+
| item                        | price_with_tax |
+-----------------------------+----------------+
| Noise-Cancelling Headphones |       235.9882 |
| Mechanical Keyboard         |       105.6100 |
| Standing Desk Mat           |        64.9000 |
+-----------------------------+----------------+

Key points

  • SELECT DISTINCT removes duplicate rows from the result. With several columns it compares the whole combination.
  • COUNT(DISTINCT column) counts how many different values a column holds.
  • AS gives a column or table a temporary name. Wrap names containing spaces in backticks.
  • A column alias can be used in ORDER BY but not in WHERE, because WHERE runs earlier.

Check yourself

1. What does SELECT DISTINCT country FROM customers; return?
2. SELECT DISTINCT customer_id, status FROM orders; removes duplicates of what?
3. Where can a column alias defined in the select list be used?
4. How do you write an alias that contains a space?