Databases & SQL · Class 12 · Querying with SELECT

MySQLSELECT⏱️ 6 min read

DISTINCT

“Which cities do our students come from?” is not the same question as “list every student's city”. The second gives you twelve answers with repeats; the first wants each place named once. distinct is the difference.

1The problem

Asking for the city column plainly gives you one row per student:

MySQL command line client

mysql> select id, name, city from students;

+------+--------+----------+

| id | name | city |

+------+--------+----------+

| 1099 | Veena | Singtam |

| 1187 | Anjali | Gangtok |

| 1250 | Sohan | NULL |

| 1402 | Lhamu | Namchi |

| 2035 | Ravi | Gangtok |

| 2044 | Karma | Singtam |

| 2199 | Diana | Kolkata |

| 2500 | Bhim | Singtam |

| 3033 | Tom | Kolkata |

| 3120 | Pema | Namchi |

| 3301 | Farhan | Siliguri |

| 3612 | Nima | NULL |

+------+--------+----------+

12 rows in set (0.00 sec)

Singtam appears three times, Gangtok twice, Namchi twice. If what you wanted was the list of places, you now have to read down the column and cross out repeats by eye.

2distinct does it for you

The keyword goes immediately after select, before the column name:

MySQL command line client

mysql> select distinct city from students;

+----------+

| city |

+----------+

| Singtam |

| Gangtok |

| NULL |

| Namchi |

| Kolkata |

| Siliguri |

+----------+

6 rows in set (0.03 sec)

Twelve rows became six. Each city is named once, in the order it was first met going down the table.

MySQL command line client

mysql> select distinct grade from students;

+-------+

| grade |

+-------+

| 10 |

| 12 |

| 11 |

| 8 |

| 9 |

+-------+

5 rows in set (0.00 sec)

Five grades are represented among the twelve students. Note the order is not sorted — distinct removes repeats, it does not arrange anything. Sorting is order by, coming up shortly, and the two are often used together.

3NULL counts as one of the values

Look again at the city list: NULL is in it, on a row of its own. Two students have no city, and distinct reported “no city” once, exactly as it reported Singtam once.

A useful inconsistency to know

This is the one place where NULLs are gathered together rather than ignored. distinct treats all NULLs as the same thing and lists them once — even though null = null is not true. The aggregate functions in the next chapter go the other way and skip NULLs entirely. Both behaviours are worth remembering, because exam questions turn on them.

If you want the list of real cities, exclude the missing ones with the operator from the last lesson: select distinct city from students where city is not null;

4Counting the distinct values

The obvious follow-up question — “how many different cities are there?” — is answered by the row count at the bottom of the resultset: 6 rows in set. There is also a way to get the number by itself, count(distinct city), which will make more sense once count() has been introduced in the next chapter.

5Recap

select distinct col from t;

Lists each different value once.

Position

Straight after select, before the column name.

NULL is included

All the missing values are reported as one NULL row.

It does not sort

Values appear in the order they were first met. Use order by to arrange them.

Quick Check

A table of 12 students has cities Singtam (3), Gangtok (2), Namchi (2), Kolkata (2), Siliguri (1) and 2 with NULL. How many rows does select distinct city from students; return?

Quick Check

Where does the distinct keyword go?

Quick Check

Does distinct sort the values?

← Previous

🔤 Pattern Matching with LIKE

Aliasing with AS 🏷️