How to Use Distinct Clause in SQL

Introduction

One of the most powerful tools in SQL is the Distinct Clause. This clause enables users to select unique/distinct values from a set of duplicate data. In a large database, distinct queries can help in quick data analysis and identifying crucial information.

In this article, we will explore the use of distinct clause in SQL, its syntax, its benefits, and some examples that show how to use distinct clause to retrieve accurate data.

What is DISTINCT Clause in SQL?

Before we move further with how to use distinct clause, let’s first understand what is distinct clause. The DISTINCT keyword is used in a SQL SELECT statement to return only unique/distinct values. It removes duplicate rows from a result set and returns only one row for each distinct group.

The DISTINCT keyword is often used with aggregate functions such as SUM, COUNT, or AVG to calculate specific values after removing duplicates.

Syntax of DISTINCT Clause

The DISTINCT keyword is included in the SELECT statement, before the column name(s) that you want to retrieve unique values for.

SELECT DISTINCT column_name FROM table_name;

Here’s an example of a SQL query that retrieves a distinct list of countries from a table named Customers:

SELECT DISTINCT Country
FROM Customers;

This query retrieves the distinct values from the Country column in the Customers table.

Benefits of using DISTINCT Clause

Using DISTINCT clause in SQL has several advantages such as:

Reducing query results

When you use DISTINCT on the column or columns with repeating data, it will remove the duplicate rows. This means you’ll get a more compressed result.

Simplifying comprehension

A table that contains multiple duplicated rows can be complicated to read and understand. The DISTINCT keyword simplifies the result set by providing only unique values.

Simplifying Analyses

When using DISTINCT with aggregate functions, it makes it easier to analyze your data. The aggregate functions like COUNT, SUM, and AVG will only be calculating distinct values, which leads to more accurate analyses.

Saving time and resource

Using DISTINCT significantly saves processing time and other resources when executing a query. It dramatically reduces the amount of data a query must scan through and return.

Examples of DISTINCT Clause in SQL

Let’s dive into some examples of how to use the DISTINCT clause.

1. Querying a Single Column

Let’s say you want to retrieve a list of all the distinct departments in an organization. You can achieve this by executing the following query.

SELECT DISTINCT department FROM employees;

2. Querying Multiple Columns

Suppose you have a table whose columns contain matching rows but with different values, you can retrieve unique records by combining the DISTINCT clause with multiple columns.

Let’s say you need to retrieve a unique list that has a distinct department and job title. You can do that by executing the following command.

SELECT DISTINCT JobTitle, department FROM employees;

3. Using aggregate functions with DISTINCT

While working with a large amount of data to be analyzed, you can use aggregate functions with DISTINCT to produce a succinct report.

SELECT COUNT(DISTINCT Country) as TotalCountries,
  COUNT(DISTINCT City) as TotalCities,
  COUNT(DISTINCT CompanyName) as TotalCompanies
FROM Customers;

This query will retrieve the number of distinct countries, cities, and company names that are in the Customers table.

4. Using ORDER BY with DISTINCT

You can use ORDER BY to sort the distinct values retrieved from the SELECT statement.

SELECT DISTINCT Country FROM Customers
ORDER BY Country ASC;

This query retrieves all the distinct countries from the Customers table, sorts them alphabetically by Country column in ascending order.

5. Using Join with DISTINCT

You can also use joins with the DISTINCT keyword for querying data from more than one table.

SELECT DISTINCT Orders.OrderID, Customers.Country
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

This query retrieves the OrderID and Country values from Orders and Customers tables, respectively. It uses the DISTINCT keyword to retrieve only one set of unique elements.

Frequently Asked Questions

Conclusion

Using DISTINCT in SQL is a powerful way to retrieve unique values from a large set of data. It simplifies comprehension, reduces query results, and saves resources when executing a query. By including DISTINCT in your SELECT statement, you can retrieve accurate information by filtering out all the redundant values. Hopefully, this article has provided a comprehensive overview of how to use the DISTINCT clause in SQL and its benefits in producing meaningful insights for your organization.

📕 Related articles about MySQL

• MySQL INSERT Statement: How to Insert Data into a Database Table
• MySQL ALTER FUNCTION Statement
• Mysqldump – A Database Backup Program
• How to use GROUP BY in SQL
• mysql — The MySQL Command-Line Client
• Installing MySQL on Solaris: A Comprehensive Guide