SQL Distinct keyword
In this lesson, you will learn how to remove duplicated rows from a result set using SQL.
Distinct Keyword In SQL
When selecting data from a database table, the result may contain unwanted duplicate data. However, if you want to remove any duplicate rows that may returned you can add the DISTINCT keyword right after the SELECT keyword as follows.
SELECT column_list DISTINCT FROM table_name;
So in other words, we can say that DISTINCT is used to retrieve unique data.
Preparing A Database
Since you will learn how to retrieve data from tables without any duplicate rows, you must have a database table with some duplicate data to practice retrieving them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "users" inside the selected database
CREATE TABLE users (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
nickname VARCHAR(50),
country VARCHAR(100)
);
-- Insert 10 records in the "users" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO users VALUES (null, "Omar", "France");
INSERT INTO users VALUES (null, "Olivia", "France");
INSERT INTO users VALUES (null, "Adam", "France");
INSERT INTO users VALUES (null, "Sarah", "Egypt");
INSERT INTO users VALUES (null, "Sarah", "Egypt");
INSERT INTO users VALUES (null, "Laura", "United Kingdom");
INSERT INTO users VALUES (null, "Emily", "United Kingdom");
INSERT INTO users VALUES (null, "Omar", "Germany");
INSERT INTO users VALUES (null, "Kate", "Canada");
INSERT INTO users VALUES (null, "James", "Australia");
SELECT DISTINCT Values Examples
The following query returns all countries in the users table without a repetation.
Example 1
SELECT DISTINCT country
FROM users;
Output
| country |
|---|
| France |
| Egypt |
| United Kingdom |
| Germany |
| Canada |
| Australia |
The following query returns all nicknames and countries in the users table without a repetation.
Example 2
SELECT DISTINCT nickname, country
FROM users;
Output
| nickname | country |
|---|---|
| Omar | France |
| Olivia | France |
| Adam | France |
| Sarah | Egypt |
| Laura | United Kingdom |
| Emily | United Kingdom |
| Omar | Germany |
| Kate | Canada |
| James | Australia |
In the previous example, if we didn't add the DISTINCT keyword, there will be 2 records have "Sarah" as nickname and "Egypt" as country.
DISTINCT Keyword Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTfirst_name VARCHAR(50)last_name VARCHAR(50)city VARCHAR(30)
3- Write a query that inserts 5 rows in the "customers" table.
4- Write a query that returns a unique list of all cities in the customers table.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
city VARCHAR(30)
);
INSERT INTO customers (first_name, last_name, city) VALUES
('Jack', 'Reed', 'New York'),
('Emma', 'Clark', 'New Jersy'),
('Luke', 'Lee', 'San Francisco'),
('Jim', 'Wilson', 'New York'),
('Lily', 'Brooks', 'San Francisco');
SELECT DISTINCT city
FROM customers;
