SQL - Distinct Clause

SQL DISTINCT: Selecting Unique Values

The SQL DISTINCT keyword is used in a SELECT statement to remove duplicate rows from the result set. It ensures that every row returned is unique.

Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

DISTINCT on a Single Column

This is the most common use case. It returns a list of all unique values from a single column.

Example

Imagine a CUSTOMERS table. We want to find all the unique cities where our customers live.

-- Let's assume the CUSTOMERS table has these records:
-- ID | NAME    | CITY
-- ---|---------|--------
-- 1  | John    | London
-- 2  | Jane    | Paris
-- 3  | Peter   | London
-- 4  | Mary    | New York

-- This query will list all cities where customers reside
SELECT CITY FROM CUSTOMERS;
-- Result: London, Paris, London, New York

-- This query will list only the unique cities
SELECT DISTINCT CITY FROM CUSTOMERS;
-- Result: London, Paris, New York

DISTINCT on Multiple Columns

When DISTINCT is used with multiple columns, it applies to the combination of values in those columns. A row is considered a duplicate only if the values in all selected columns are identical to another row.

Example

Let’s find the unique combinations of CITY and COUNTRY from an ORDERS table.

-- Assume ORDERS table has:
-- OrderID | City      | Country
-- --------|-----------|--------
-- 101     | London    | UK
-- 102     | London    | USA      -- (London, Ohio, etc.)
-- 103     | Paris     | France
-- 104     | London    | UK

SELECT DISTINCT City, Country
FROM ORDERS;

-- Result:
-- City   | Country
----------|--------
-- London | UK
-- London | USA
-- Paris  | France

Notice that (‘London’, ‘UK’) appears only once in the result, even though it was in the original table twice. (‘London’, ‘USA’) is treated as a different, unique combination.

Using COUNT with DISTINCT

A very powerful pattern is using DISTINCT inside the COUNT() aggregate function to count the number of unique values in a column.

-- To count how many unique cities our customers are from:
SELECT COUNT(DISTINCT CITY) AS NumberOfUniqueCities
FROM CUSTOMERS;

Based on our first example, this would return 3.

DISTINCT and NULL Values

SQL treats all NULL values as a single group when using DISTINCT. If a column contains multiple NULLs, they will be collapsed into a single NULL row in the result set.

-- Assume a `Products` table where some products have no category:
-- ProductID | Category
-- ----------|----------
-- 1         | Electronics
-- 2         | Books
-- 3         | NULL
-- 4         | Books
-- 5         | NULL

SELECT DISTINCT Category FROM Products;

-- Result:
-- Category
------------
-- Electronics
-- Books
-- NULL

DISTINCT vs. GROUP BY

SELECT DISTINCT column is functionally equivalent to SELECT column FROM table GROUP BY column. Both return the unique values from that column.

• Use DISTINCT when you simply want a list of unique values.
• Use GROUP BY when you want to perform an aggregate function (like COUNT, SUM, AVG) on the groups of unique values. GROUP BY is more flexible and powerful.

Performance Considerations

To find unique values, the database usually needs to perform a sort or hashing operation on the result set, which can be computationally expensive on very large tables. If an index exists on the column(s) you are selecting, the database may be able to use the index to retrieve the distinct values much more efficiently.