SQL and MySQL course · Module 2: Querying data (SELECT)

DISTINCT - unique values only

4 min read ·
In this lesson5

The merchant from Rhodes asks: "Which countries do your authors come from?". The answer lies in the kraj field of the autorzy shelf. But when you ask a shelf for a single field, the same value repeats many times - many authors come from the same country, many readers from the same city. The merchant wants a list of countries, not a list of authors. DISTINCT removes the duplicates.

Without DISTINCT - with repetitions

Let us start with an ordinary query for a single field. The Library gives the country of every author, row by row:

SELECT kraj FROM autorzy;

Eight authors, eight rows:

kraj
Grecja
Grecja
Grecja
Grecja
Grecja
Polska
Polska
USA

Five authors come from Greece, so the word Grecja appears five times. Such a list tells the merchant nothing.

With DISTINCT - no repetitions

Now let us add one word right after SELECT. The rest of the query stays the same:

SELECT DISTINCT kraj FROM autorzy;

Each country now appears only once:

kraj
Grecja
Polska
USA

Of eight rows, three remain. It is like the list of sections in the Pinakes - every subject named a single time. The shelf still holds eight authors: DISTINCT removes nothing from the table, it only sifts the answer.

DISTINCT and multiple columns

When you give several fields, DISTINCT compares the whole row of the result, not single values. First look at the readers' first names and surnames without DISTINCT:

SELECT imie, nazwisko FROM czytelnicy;

Two different readers with the same first name and surname are enrolled in the Library: Anna Nowak from Kraków and Anna Nowak from Gdańsk. There is also Jan Nowak. Here is the beginning of the result:

imienazwisko
AnnaNowak
JanKowalski
AnnaNowak
PiotrWiśniewski
EwaZielińska
JanNowak
……

Now the same query with DISTINCT:

SELECT DISTINCT imie, nazwisko FROM czytelnicy;

The second "Anna Nowak" has disappeared, because she has the same first name and surname as the first row:

imienazwisko
AnnaNowak
JanKowalski
PiotrWiśniewski
EwaZielińska
JanNowak
……

Notice three things. Of two identical rows, one remains - they do not both vanish. Jan Nowak stays, although he bears the same surname as Anna, because his first name is different: a row disappears only when first name and surname together repeat in another row. And DISTINCT sees only the fields on the list - we did not choose the city, so two different Annas merged into one row. Of sixteen rows, fifteen remain.

Where does DISTINCT stand?

We write DISTINCT once, right after SELECT, and it applies to every field on the list. Writing SELECT imie, DISTINCT nazwisko FROM czytelnicy; is a syntax error - you cannot sift one column and leave another untouched.

Common uses

  • Which cities do readers come from? → SELECT DISTINCT miasto FROM czytelnicy;
  • Which author countries do we have? → SELECT DISTINCT kraj FROM autorzy;

My advice: when a request asks "which ... do we have" or "where do ... come from", or says "without repeats", reach for DISTINCT. Remember too that DISTINCT does not sort. The countries came out in alphabetical order only because that is how they happen to lie on the shelf. In the lesson "All together" you will add sorting to DISTINCT and be sure.

DISTINCT is the scribe's sieve - it sifts the scrolls and keeps only the unique catalog cards.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is the DISTINCT keyword used for in a SELECT query?

  2. 2. What does DISTINCT remove when you give several columns, e.g. "SELECT DISTINCT imie, nazwisko FROM czytelnicy;"?

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves the unique values of the miasto column from the czytelnicy table (with no repetitions).

  • Horizontal ordering

    Arrange the elements in the correct order to build a query for the unique countries of authors (left to right):

  • Code editor

    Write a query that retrieves the unique values of the kraj column from the autorzy table (with no repetitions).

  1. QuestionWhat DISTINCT is for
  2. QuestionDISTINCT with multiple columns
  3. Coding taskUnique reader cities
  4. Put in orderA query for countries without repeats
  5. Coding taskUnique author countries

Useful articles