intro
SQL DISTINCT is a valuable tool for obtaining unique or distinct values from a database table.
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:
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.
Now, to get a list of unique cities where the customers are located, run the query below.
1
SELECT DISTINCT city FROM orders
The query above will return a list of unique cities where the customers are located, as shown below.
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.
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.
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.
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.
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.
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.
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.
