DISTINCT
POSTGRESQL
SQL

SQL DISTINCT: A Comprehensive Guide

intro

SQL DISTINCT is a valuable tool for obtaining unique or distinct values from a database table.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Assume your company has a database that contains orders placed by customers from different cities. Now, you are tasked with getting a list of unique cities where the customers are located to plan order deliveries. In this case, you can use SQL DISTINCT to filter out unique cities that the customers come from. For that reason, in this blog, you will learn:

  • What is SQL DISTINCT?
  • How to use SQL DISTINCT with single columns
  • How to use DISTINCT with multiple columns
  • How to use SQL DISTINCT with aggregate functions
  • How to use DISTINCT with WHERE clause

Prerequisites

To follow through with this article, you need the following:

  • A database SQL client: In this case, we will use DbVisualizer. To install DbVisualizer, navigate to the DbVisualizer download page. Then, download the recommended installer for your operating system. After that, execute the installer you downloaded and follow the instructions. 
  • A database management system: Here, we will use Postgres. To install it, navigate to the PostgreSQL download page and download it for your operating system. You can follow this guide to install PostgreSQL on Windows, this guide to install it on Linux, and this guide to install it on macOS.

What Is SQL DISTINCT?

The SQL DISTINCT statement returns only distinct (different) values from a table. It is used when a column contains many duplicate values, and you only want to list the different (distinct) values. 

The basic syntax of the SQL DISTINCT keyword is as follows:

Copy
        
1 SELECT DISTINCT column1, column2,.....columnN 2 FROM table_name;

Column1, column2, etc. are the columns you want to retrieve the unique or distinct values from, and table_name represents the table's name containing the data.

The DISTINCT clause can be used on a single column to retrieve all unique values in that column or on multiple columns to retrieve all unique combinations of values across those columns. The DISTINCT clause can also be used inside aggregate functions. Do not worry, you will see examples of those use cases in the next chapters!

Using SQL DISTINCT With Single Columns

Consider the following orders table with columns customerID, customercame, address, city, phone, email, and product.

Orders table in DbVisualizer
Orders table in DbVisualizer

Now, to get a list of unique cities where the customers are located, run the query below.

Copy
        
1 SELECT DISTINCT city FROM orders

The query above will return a list of unique cities where the customers are located, as shown below.

Running the SQL DISTINCT query on multiple columns in DbVisualizer

Using DISTINCT With Multiple Columns

When you want to retrieve unique combinations of values from multiple columns, simply list the columns after the DISTINCT keyword, separated by commas.

For example, if you want to get the unique names of the customers, the city they are located in, and the product they ordered, you can run the query below.

Copy
        
1 SELECT DISTINCT customername, city, product FROM orders

The query above will return the unique names of the customers, their city, and the product they ordered, as shown below.

Running the SQL DISTINCT query in DbVisualizer

Using SQL DISTINCT With Aggregate Functions

You can use DISTINCT with SQL aggregate functions to calculate aggregated values over distinct values in a column.

For example, if you want to find the number of customers who placed an order from each unique city, you can run the query below.

Copy
        
1 SELECT city, COUNT(DISTINCT customername) AS UniqueCustomersCount 2 FROM orders 3 GROUP BY city;

The query above will return unique cities and the number of customers that come from each city as shown below.

Running a SQL Query in DbVisualizer

Using DISTINCT With a WHERE Clause

DISTINCT can be combined with the WHERE clause to filter and show only unique values based on specific conditions.

For example, if you want to find a list of unique customer names that come from New York together with their addresses and phone numbers, you can run the query below.

Copy
        
1 SELECT DISTINCT customername, address, phone 2 FROM public.orders 3 WHERE city = 'New York';

The query above will return unique customer names that come from New York together with their addresses and phone numbers as shown below.

Note the WHERE condition in the DISTINCT SQL query

Conclusion

In conclusion, SQL DISTINCT is a valuable tool for obtaining unique or distinct values from a database table. It allows you to filter out duplicate values and retrieve only the different values, making it useful for a variety of data analysis tasks. 

You can use DISTINCT with single columns to retrieve unique values from a specific column, with multiple columns to retrieve unique combinations of values, and even in conjunction with aggregate functions to perform calculations on distinct values. 
Additionally, it can be combined with the WHERE clause to filter results based on specific conditions. By following this tutorial and utilizing tools like DbVisualizer, you can get extensive support for most popular data sources. We hope that you’ve enjoyed this tutorial and that you will stick around our blog for more tutorials to come.

FAQ

What is DISTINCT in SQL?

DISTINCT is a keyword in SQL that eliminates duplicate rows from a result set. This means unique values will be displayed, even if they appear multiple times in the table.

How do I use DISTINCT in SQL?

You can use the DISTINCT keyword by placing it immediately after the SELECT keyword in a SQL query. It is followed by the column name or columns from which you want to eliminate duplicates.

What is the difference between Unique and DISTINCT in SQL?

The difference between Unique and DISTINCT in SQL is that Unique means values exist only once in the dataset, while DISTINCT means values repeated several times

Can I combine SQL DISTINCT with other clauses?

Yes! SQL DISTINCT can be combined with other clauses like WHERE, ORDER BY, and GROUP BY to obtain unique values based on specific conditions.

Can I use the DISTINCT keyword on multiple columns?

Yes, you can use the DISTINCT keyword on multiple columns. Simply list the column names separated by commas after the DISTINCT keyword.

Dbvis download link img
About the author
Bonnie
Bonnie
Bonnie is a web developer and technical writer creating easy-to-understand technical articles.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

SQL Server DATEADD: The Complete Guide

author Ochuko Onojakpor tags SQL SQL SERVER 7 min 2024-06-10
title

mysqldump: How to Backup and Restore MySQL Databases

author Antonello Zanini tags Backup MySQL SQL 11 min 2024-06-06

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗