SQL SELECT DISTINCT

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 columns
  • table is 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.

The DISTINCT clause with SELECT in SQL returns unique rows only
Example: Selecting unique countries

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.

The DISTINCT clause with two columns returns unique data by combining two rows
Example: Selecting the unique combined fields

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.

DISTINCT and COUNT used together to count unique values
Example: Counting unique countries

More SQL DISTINCT

DISTINCT vs GROUP BY

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 once
  • GROUP BY - also selects unique country names from Customers using grouping to achieve the same result as DISTINCT

To learn more, visit SQL GROUP BY.

DISTINCT With ORDER 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.

SELECT DISTINCT vs SELECT

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.

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges