SQL SELECT DISTINCT Statement Explained with Examples

SQL, or Structured Query Language, is the cornerstone of many data-related operations. One of its most powerful tools is the SELECT DISTINCT statement. Crucially, DISTINCT dedupes the entire selected row, not just one column — a fact often misunderstood by beginners. This tutorial guides you through the nuances of this statement, ensuring a robust understanding whether you are preparing for SQL interviews, exploring SQL views, or writing queries in your SQL editor.

What SELECT DISTINCT does

The SELECT DISTINCT statement is pivotal when you aim to retrieve unique values from a table, thereby filtering out duplicates.

SELECT DISTINCT on a single column

Sample Table (Students):

Query:

SELECT DISTINCT Name FROM Students;

Result:

Explanation:

The result showcases unique names. Even though 'Alice' appears twice in the table, the DISTINCT clause ensures it appears only once in the result.

SQL DISTINCT on multiple columns

This allows for unique combinations of several columns, rather than unique values from just one column.

Sample Table (Orders):

Query:

SELECT DISTINCT Product, Color FROM Orders;

Result:

Explanation:

The result provides unique combinations of Product and Color. Notice how Apple + Red only appears once, despite being present twice in the original table.

SQL DISTINCT and NULL values

When using SELECT DISTINCT, multiple NULL values are returned as a single distinct NULL in the result.

Sample Table (Employees):

Query:

SELECT DISTINCT Address FROM Employees;

Result:

Explanation:

While there are two NULL values in the Address column of the table, the result only displays one. This illustrates the principle that DISTINCT treats NULL values as equal to one another, so repeated NULLs collapse into a single row.

SQL DISTINCT vs GROUP BY

While both provide unique results, GROUP BY is designed for aggregating data based on specific columns.

Example:

Sample Table (ProductSales):

Using DISTINCT:

To retrieve unique combinations of Product and SaleDate:

SELECT DISTINCT Product, SaleDate FROM ProductSales;

Result:

Using GROUP BY:

To aggregate the total units sold for each product on each date:

SELECT Product, SaleDate, SUM(UnitsSold) as TotalUnits
FROM ProductSales
GROUP BY Product, SaleDate;

Result:

Explanation:

• Using DISTINCT, we can fetch unique combinations of Product and SaleDate.
• With GROUP BY, not only can we group data based on columns, but we can also perform aggregate functions (like SUM) to understand data at a more granular level. In this case, we aggregated the total units sold for each product on a specific date.

SELECT COUNT(DISTINCT column)

To measure the number of unique values in a column, DISTINCT can be paired with the COUNT function.

Query:

SELECT COUNT(DISTINCT Name) FROM Students;

Result:

Explanation:

The result indicates there are two unique student names in the 'Students' table.

SELECT vs SELECT DISTINCT

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Validate unique-result queries in DbSchema

Before using DISTINCT in reporting logic, inspect the raw rows and then the deduplicated result to verify what is actually being removed. DbSchema helps you compare both views quickly and avoid false assumptions around duplicates.

Common mistakes

• Applying DISTINCT to the wrong columns.
• Inefficient performance due to excessive use of DISTINCT on large datasets.
• Misinterpretation of NULL values with DISTINCT.

Frequently asked questions (FAQs)

• Q: Can I combine DISTINCT with other SQL functions? A: Yes, DISTINCT can be used alongside functions like COUNT, SUM, and others.

Practice questions

1. Retrieve unique colors of products in the "Orders" table.
2. Determine the count of unique student names in the "Students" table.
3. Extract unique combinations of product and color for the product "Apple" from the "Orders" table.

To truly master the SELECT DISTINCT statement in SQL, regular practice and real-world application are essential. This guide offers a comprehensive overview, but hands-on experience will solidify your understanding.

Download DbSchema and run these DISTINCT queries against your own database in its SQL Editor, which is part of the free Community Edition.