The SELECT DISTINCT statement retrieves distinct values from a database table.
Example
-- select the unique ages from the Customers table
SELECT DISTINCT age
FROM Customers;Here, the SQL command selects only the unique values of age from the Customers table.
Syntax of SQL SELECT DISTINCT
SELECT DISTINCT column1, column2 ...
FROM table;
Here,
column1, column2, ...are the table columnstableis table name from where we retrieve the distinct columns
Example: SQL SELECT DISTINCT
-- select the unique countries from the customers table
SELECT DISTINCT country
FROM Customers;Here, the SQL command selects unique countries from the Customers table.
SQL DISTINCT on Multiple Columns
We can also use SELECT DISTINCT with multiple columns. For example,
-- select rows if the first name and country of a customer is unique
SELECT DISTINCT country, first_name
FROM Customers;Here, the command selects rows if combinations of country and first_name are unique. Meaning, the result will include each pair of country and first_name only once.
DISTINCT With COUNT
We can use SQL DISTINCT with the COUNT() function to count the number of unique rows.
Let's look at an example.
-- count the unique countries where customers are from
SELECT COUNT(DISTINCT country)
FROM Customers;Here, the SQL command returns the count of unique countries.
More SQL DISTINCT
Let's take a look at an example,
-- with distinct
SELECT DISTINCT country
FROM Customers;
-- with group by
SELECT country
FROM Customers
GROUP BY country;
Here, both of the SQL commands are similar and return unique countries from the Customers table.
DISTINCT- selects unique country names from the Customers table, ensuring each country is listed only onceGROUP BY- also selects unique country names from Customers using grouping to achieve the same result asDISTINCT
To learn more, visit SQL GROUP BY.
Let's take a look at an example,
-- with order by
SELECT DISTINCT age
FROM Customers
ORDER BY age DESC;
Here, the SQL command selects unique ages and orders them in descending order from the Customers table.
To learn more, visit SQL ORDER BY.
The SELECT DISTINCT statement is used when you want to return only unique (distinct) values in the result set.
Whereas, a regular SELECT statement without the DISTINCT keyword retrieves all rows from the specified columns, including duplicate values.
Let's look at an example.
Example: SELECT DISTINCT
-- select distinct countries from the Customers table
SELECT DISTINCT country
FROM Customers;
This command will return each country only once, regardless of how many times it appears in the Customers table.
Example: SELECT
-- select all countries from the Customers table
SELECT country
FROM Customers;
This SQL command will return all country entries, including duplicates, from the Customers table.
To learn more, visit SQL SELECT.