sql_distinct

SQL SELECT DISTINCT: Retrieving Unique Values

The SELECT DISTINCT statement is used to return only unique (different) values for one or more specified columns. It automatically filters out duplicate rows from your result set.

The Power of SELECT DISTINCT

In a table, a column (or a combination of columns) might contain many duplicate values. Sometimes, you only need a list of the unique values present. For example, you might want to find all unique countries where your customers are located, or all unique product categories you offer.

The DISTINCT keyword, when added to a SELECT statement, instructs the database to eliminate these duplicate rows before returning the results.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition; -- Optional: to filter rows before finding distinct values

When DISTINCT is applied to multiple columns, a row is considered a duplicate only if all selected columns have identical values to another row.

Demonstration Database: Northwind

We’ll continue using the Northwind sample database.

Here’s a selection from the “Customers” table:

CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country


1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany


2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico


3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico


4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
UK


5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden

SELECT DISTINCT Example: Listing Unique Cities

The following SQL statement selects only the unique values from the “City” column in the “Customers” table:

SELECT DISTINCT City
FROM Customers
ORDER BY City; -- Optional: to sort the unique cities alphabetically

Practical Application: This is useful for populating dropdown lists in user interfaces, understanding the geographical distribution of customers without redundancy, or identifying all unique product types sold.

Performance Note: Using DISTINCT can have performance implications, especially on large tables without appropriate indexes, as the database needs to sort or hash the data to identify duplicates. Use it judiciously.