SQL Basics
Querying Data
Filtering Data
Joining Tables
SQL Functions
Modifying Data
Defining Data
SQL DISTINCT
In this tutorial, you will learn how to remove duplicate data in the result set using the SQL DISTINCT keyword.
As default SELECT statement will not remove duplicates in the result set.
Sometimes you may be in situation when you have mutiple duplicate rows in a table and you want to remove these duplicate values, then you can specify the keyword DISTINCT directly after the SELECT keyword.Â
Syntax
The basic syntax of the DISTINCT statement to select all columns, can be given with:
SELECT DISTINCT column_names
FROM table_name
Examples
To understand the SELECTÂ statement in a better way, let’s look at the following customers table in our tutorial database:
| customer_id | first_name | last_name | country | score |
|---|---|---|---|---|
| 1 | Maria | Cramer | Germany | 350 |
| 2 | John | Steel | USA | 900 |
| 3 | Georg | Pipps | UK | 750 |
| 4 | Martin | Müller | Germany | 500 |
| 5 | Peter | Franken | USA | NULL |
Now, let’s check out some examples that demonstrate how it actually works.
We have the following task be to solve using SQL statements
First, lets retrive all data from country column in customers table by executing the following SQL Statement:
SELECT country
FROM customers
After executing the above query, you’ll get the result set something like this:
| country |
|---|
| Germany |
| USA |
| UK |
| Germany |
| USA |
As you can see the output contains a list of all countries of customers, we are almost there but the problem you’ll find the country “Germany” and “USA” twice and that is duplicate in the result, which is not good.
If you see the result carefully, in column country there are only 3 countries “Germany”, “UK”, and “USA”.
Well, let’s fix it, by just adding the magic word DISTINCT like this:
SELECT DISTINCT country
FROM customers
After executing the above query, you’ll get the result set something like this:
| country |
|---|
| Germany |
| USA |
| UK |
As you see this time there is no duplicate values and we accomplished the task by having a list of all countries of customers excluding without having duplicates.
