DbSchema Database Designer

DbSchema Tutorial | SQL SELECT DISTINCT Statement

SQL, or Structured Query Language, is the cornerstone of many data-related operations. One of its most powerful statements is the SELECT DISTINCT statement. This tutorial aims to guide fresh students through the nuances of this statement, ensuring a robust understanding by its conclusion.

1. Introduction to SQL SELECT DISTINCT

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

Example:

Sample Table (Students):

StudentID Name
1 Alice
2 Bob
3 Alice

Query:

SELECT DISTINCT Name FROM Students;

Result:

Name
Alice
Bob

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.

2. SQL DISTINCT on Multiple Columns

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

Example:

Sample Table (Orders):

OrderID Product Color
1 Apple Red
2 Apple Green
3 Orange Orange
4 Apple Red

Query:

SELECT DISTINCT Product, Color FROM Orders;

Result:

Product Color
Apple Red
Apple Green
Orange Orange

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.

3. SQL DISTINCT on (Multiple) NULL Values

NULL values are always considered unique from one another in SQL.

Sample Table (Employees):

ID Name Address
1 John NULL
2 Michael 123 Lane St
3 Sarah NULL

Query:

SELECT DISTINCT Address FROM Employees;

Result:

Address
NULL
123 Lane St

Explanation:

While there are two NULL values in the Address column of the table, the result only displays one. This illustrates the principle that all NULLs are distinct.

4. 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):

Product SaleDate UnitsSold
Apple 2023-01-01 5
Apple 2023-01-01 3
Orange 2023-01-02 2
Apple 2023-01-03 4

Using DISTINCT:

To retrieve unique combinations of Product and SaleDate:

SELECT DISTINCT Product, SaleDate FROM ProductSales;

Result:

Product SaleDate
Apple 2023-01-01
Orange 2023-01-02
Apple 2023-01-03

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:

Product SaleDate TotalUnits
Apple 2023-01-01 8
Orange 2023-01-02 2
Apple 2023-01-03 4

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.

5. SELECT COUNT on DISTINCT

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:

COUNT(DISTINCT Name)
2

Explanation:

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

7. SQL SELECT VS SELECT DISTINCT

Aspect SELECT Statement SELECT DISTINCT Statement
Purpose Fetches all data from specified columns. Fetches unique data, removing duplicates.
Performance Generally faster as it retrieves data as-is. Might be slower on large datasets due to filtering.
Use Cases When needing all data, including duplicates. When distinct values are required.
Example Result Could get ‘Apple’, ‘Apple’, ‘Orange’. Would get ‘Apple’, ‘Orange’.

8. 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.

9. 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.

10. 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.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys.


Query Builder alt >

Query Builder

Create SQL Queries using the mouse.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.