Skip to content

Beginner: asking questions · SELECT: filtering and sorting · topic 10 of 62 · 30 min

DISTINCT and how a query runs

Explanation

DISTINCT removes repeated rows from the result, judging the whole row of selected columns. A query is written SELECT, FROM, WHERE, ORDER BY, LIMIT, but it is worked out in a different order: FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT. That order explains why an alias works in ORDER BY but not in WHERE.

DISTINCT: each answer once

Which cities do our customers live in? SELECT city FROM customers gives Pune twice, because two customers live there. DISTINCT keeps one copy of each repeated row.

sql
SELECT DISTINCT city FROM customers;
-- Pune
-- Delhi
-- Chennai
-- Mumbai
-- Hyderabad
6 customers, 5 different cities.

DISTINCT on many columns

DISTINCT looks at the whole row of selected columns, not at the first column only. SELECT DISTINCT city, course gives each different pair once. Pune appears three times in the result, because Pune has BCA, BCom and BSc students.

sql
SELECT DISTINCT city, course FROM students ORDER BY city, course;
-- Chennai   | BCA
-- Delhi     | BCom
-- Delhi     | BSc
-- Hyderabad | BCA
-- Mumbai    | BSc
-- Pune      | BCA
-- Pune      | BCom
-- Pune      | BSc
8 different (city, course) pairs. Here every student happens to have a different pair.

DISTINCT treats NULLs as equal to each other, so all NULLs collapse into one row. SELECT DISTINCT dept_id FROM employees returns one NULL (for Deepak) plus 1, 2, 3 and 4.

A preview: counting distinct values

sql
SELECT COUNT(DISTINCT city) FROM students;   -- 5
COUNT counts rows; COUNT(DISTINCT city) counts different cities. Module 4 covers COUNT and its friends.

Written order versus run order

You must write the clauses of a query in a fixed order. But the database works them out in a different order, called the logical order. Knowing it explains many errors.

StepClauseWhat happens
1FROMPick the table (later, join tables)
2WHEREKeep only the rows that pass the condition
3GROUP BY / HAVINGGroup rows and filter groups (module 4)
4SELECTWork out the columns and expressions; aliases are born here
5DISTINCTRemove repeated rows
6ORDER BYSort; aliases from step 4 can be used
7LIMIT / OFFSETKeep only some of the sorted rows
Think of it like this: Think of making a cup of tea for guests. You take milk from the fridge (FROM), throw out any packet past its date (WHERE), make the tea and pour it into cups (SELECT), remove an extra cup if you made one too many (DISTINCT), arrange the cups on the tray (ORDER BY), and carry only as many as there are guests (LIMIT). You cannot arrange cups before the tea exists.

Why an alias fails in WHERE

An alias is created in step 4, SELECT. WHERE runs in step 2, before the alias exists. So the standard way is to repeat the expression in WHERE, and to use the alias in ORDER BY, which runs after SELECT.

sql
SELECT name, salary * 12 AS annual
FROM employees
WHERE salary * 12 >= 1200000     -- repeat the expression here
ORDER BY annual DESC;            -- the alias works here
-- Meera Iyer   | 3000000
-- Rahul Sharma | 1440000
-- Pooja Gupta  | 1320000
-- Farhan Ali   | 1260000
Careful: SQLite is lenient and also accepts WHERE annual >= 1200000. PostgreSQL ("column annual does not exist"), MySQL ("Unknown column") and SQL Server reject it. Repeat the expression so your SQL works everywhere.

Reading a query aloud

Reading in the logical order helps you check a query. For the query above: "From employees, keep those whose salary times 12 is at least 12 lakh, show their name and annual salary, and sort by annual salary, highest first." If the sentence sounds wrong, the query probably is.

Common mistakes

  • Using DISTINCT to hide duplicate rows caused by a wrong query (often a bad join, module 5). Find the cause instead.
  • Thinking DISTINCT applies to the first column only. It applies to the whole selected row.
  • Using a SELECT alias in WHERE. Repeat the expression, or use a subquery (module 6).
  • Writing clauses in the wrong order, like ORDER BY before WHERE. The written order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

Need help?

Stuck on a word or an idea? Search this course, or every course, and jump straight to the explanation or a worked question.

Try:

Watch the lesson

DISTINCT and how a query runs · 1/7

DISTINCT and query order

Two ideas in this lesson: removing repeats with DISTINCT, and the order in which a query really runs.

Prefer a video by a teacher? Find free videos on “sql distinct logical order of query execution alias where order by” on YouTube ↗

In real life: Filling a drop-down menu

A food delivery app shows a "Cuisine" filter with North Indian, South Indian, Chinese and so on. The list is not typed by hand; it comes from SELECT DISTINCT cuisine FROM restaurants ORDER BY cuisine, so a new cuisine appears in the menu as soon as the first restaurant of that kind joins.

sql
SELECT DISTINCT category FROM products ORDER BY category;
-- Electronics
-- Fashion
-- Grocery
-- Kitchen
The same idea on the SHOP database: one entry per category, sorted for the menu.

Practice (0/4 done)

Task 1 · Code · SQL

Which cities do the SHOP customers live in? Return each city once (one column: city).

Task 2 · Code · SQL

Return every product category once (one column: category).

Task 3 · Code · SQL

Which categories have at least one product priced above 1000? Return each category once, sorted A to Z (one column: category).

Task 4 · Code · SQL

salary in COMPANY is per month. Return name and salary * 12 named annual, only for employees whose annual pay is at least 1200000, sorted by annual from highest to lowest. Repeat the expression in WHERE and use the alias in ORDER BY.

Quick quiz

Question 1

1.In which logical order are these clauses worked out?

Question 2

2.Why can ORDER BY use a SELECT alias, while standard SQL does not allow it in WHERE?

Question 3

3.SELECT DISTINCT city, course FROM students: what is removed?

Question 4

4.dept_id has the values 1, 1, 2, NULL, NULL. How many rows does SELECT DISTINCT dept_id return?

Question 5

5.Your query returns duplicate rows. When is adding DISTINCT the wrong fix?

Read more (free)

Open a question to see the answer and why. The first ones are this topic's own quiz, so try the quiz above before you peek.

In which logical order are these clauses worked out?

Answer: FROM, WHERE, SELECT, ORDER BY

FROM picks the table, WHERE filters, SELECT builds columns, ORDER BY sorts.

Why can ORDER BY use a SELECT alias, while standard SQL does not allow it in WHERE?

Answer: ORDER BY runs after SELECT, WHERE runs before it

The alias exists only from the SELECT step onward.

SELECT DISTINCT city, course FROM students: what is removed?

Answer: Rows where both city and course repeat together

DISTINCT compares the whole selected row.

dept_id has the values 1, 1, 2, NULL, NULL. How many rows does SELECT DISTINCT dept_id return?

Answer: 3

DISTINCT treats NULLs as equal, so the result is 1, 2 and one NULL.

Your query returns duplicate rows. When is adding DISTINCT the wrong fix?

Answer: When the duplicates come from a mistake in the query, such as a wrong join

DISTINCT hides the symptom; fix the query that makes the extra rows.

What does SELECT DISTINCT city FROM customers return?Related

Answer: Each city only once

DISTINCT removes repeated rows, so each city appears once.

From ORDER BY, LIMIT and DISTINCT

students has 8 rows in 5 different cities. How many rows does SELECT city, COUNT(*) FROM students GROUP BY city return?Related

Answer: 5

GROUP BY gives one row per group, and there are 5 cities.

From GROUP BY

SELECT city, COUNT(*) AS n FROM students GROUP BY city HAVING n > 1 works in SQLite. What happens in PostgreSQL?Related

Answer: It fails, because a SELECT alias is not visible in HAVING

HAVING runs before SELECT in standard SQL. Write HAVING COUNT(*) > 1.

From HAVING versus WHERE

What does SELECT name FROM students WHERE marks > 80 return?Related

Answer: Names of students with marks above 80

SELECT name picks one column; WHERE marks > 80 keeps only the rows with more than 80.

From SELECT and WHERE: finding rows

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Text functions →

Next steps

  1. This is the last topic of “SELECT: filtering and sorting”. The module exam comes next.
  2. Module 2 exam: SELECT, filtering and sorting · 15 questions · 25 min, without help. 5 topics of this module still to finish first.
  3. Then the next module, Functions, CASE and NULL. 62 topics left in the course before the final exam.

← Previous: ORDER BY, LIMIT and OFFSET