Hello and welcome to our guide on SQL Server Select Distinct. In this article, we’ll take you through everything you need to know about using the Select Distinct statement in SQL Server for better database management and optimization. Whether you’re an experienced database administrator or a beginner, this guide will help you understand the nuances of Select Distinct and make the most out of its features.
Table of Contents
- What is the Select Distinct Statement?
- How Does Select Distinct Work?
- When to Use Select Distinct?
- Select Distinct with Multiple Columns
- Select Distinct with Where Clause
- Select Distinct with Having Clause
- Select Distinct with Order By Clause
- Select Distinct with Group By Clause
- Select Distinct with Inner Join
- Select Distinct with Left Join
- Select Distinct with Right Join
- How to Optimize Select Distinct?
- Common Select Distinct Mistakes to Avoid
- Frequently Asked Questions
- Conclusion
What is the Select Distinct Statement?
The Select Distinct statement is one of the most commonly used SQL statements for retrieving unique values from a database table. With Select Distinct, you can retrieve distinct or unique values from one or more columns of a table, eliminating duplicate rows. This helps you to manage your data more efficiently and achieve better database performance.
Example:
| Employee ID | First Name | Last Name | City |
|---|---|---|---|
| 1001 | John | Doe | New York |
| 1002 | Jane | Doe | Chicago |
| 1003 | John | Smith | Los Angeles |
| 1004 | William | Johnson | New York |
If you want to retrieve the unique cities in the above table, you can use the following Select Distinct statement:
SELECT DISTINCT City FROM Employees;The above Select Distinct statement will return the following result set:
| City |
|---|
| New York |
| Chicago |
| Los Angeles |
How Does Select Distinct Work?
The Select Distinct statement works by retrieving distinct or unique values from one or more columns of a table, eliminating duplicate rows. When you execute a Select Distinct statement, the database engine first sorts the data based on the specified columns and then eliminates any duplicate rows that have the same values in those columns.
The Select Distinct statement works by comparing the values in the specified columns and returning only the unique values. If you specify multiple columns in the Select Distinct statement, the database engine will consider the combination of values in all those columns as unique.
Example:
Let’s say you have the following table:
| Customer ID | Order ID | Order Date |
|---|---|---|
| 1001 | 101 | 2021-01-01 |
| 1001 | 102 | 2021-01-02 |
| 1002 | 103 | 2021-01-03 |
| 1002 | 103 | 2021-01-03 |
If you want to retrieve the unique customer IDs and order IDs from the above table, you can use the following Select Distinct statement:
SELECT DISTINCT [Customer ID], [Order ID] FROM Orders;The above Select Distinct statement will return the following result set:
| Customer ID | Order ID |
|---|---|
| 1001 | 101 |
| 1001 | 102 |
| 1002 | 103 |
When to Use Select Distinct?
The Select Distinct statement is most commonly used when you want to retrieve unique values from one or more columns of a table. Here are some scenarios where you might want to use Select Distinct:
- When you need to count the number of unique values in a column.
- When you need to retrieve a list of unique values from a column.
- When you need to filter out duplicate rows from a result set.
Select Distinct with Multiple Columns
You can use the Select Distinct statement with multiple columns to retrieve unique combinations of values from those columns. When you specify multiple columns in the Select Distinct statement, the database engine will consider the combination of values in all those columns as unique.
Example:
Let’s say you have the following table:
| Order ID | Customer ID | Product Name |
|---|---|---|
| 101 | 1001 | Product A |
| 102 | 1001 | Product B |
| 103 | 1002 | Product A |
| 104 | 1002 | Product B |
If you want to retrieve the unique combinations of customer IDs and product names from the above table, you can use the following Select Distinct statement:
SELECT DISTINCT [Customer ID], [Product Name] FROM Orders;The above Select Distinct statement will return the following result set:
| Customer ID | Product Name |
|---|---|
| 1001 | Product A |
| 1001 | Product B |
| 1002 | Product A |
| 1002 | Product B |
Select Distinct with Where Clause
You can use the Where clause with the Select Distinct statement to filter the result set based on a condition. The Where clause specifies a condition that must be met by the rows in the table before they are included in the result set.
Example:
Let’s say you have the following table:
| Order ID | Customer ID | Order Date |
|---|---|---|
| 101 | 1001 | 2021-01-01 |
| 102 | 1001 | 2021-02-02 |
| 103 | 1002 | 2021-03-03 |
| 104 | 1002 | 2021-04-04 |
If you want to retrieve the unique customer IDs from the above table for orders that were placed after a certain date, you can use the following Select Distinct statement with the Where clause:
SELECT DISTINCT [Customer ID] FROM Orders WHERE [Order Date] > '2021-03-01';The above Select Distinct statement will return the following result set:
| Customer ID |
|---|
| 1002 |
Select Distinct with Having Clause
You can use the Having clause with the Select Distinct statement to filter the result set based on a condition that is applied to an aggregated value. The Having clause is used in conjunction with the Group By clause to filter the groups based on a condition that is applied to the aggregated values of each group.
Example:
Let’s say you have the following table:
| Order ID | Customer ID | Product Name | Quantity | Price |
|---|---|---|---|---|
| 101 | 1001 | Product A | 2 | 10.00 |
| 102 | 1001 | Product A | 3 | 10.00 |
| 103 | 1002 | Product B | 1 | 20.00 |
| 104 | 1002 | Product B | 2 | 20.00 |
If you want to retrieve the unique combinations of customer IDs and product names from the above table for orders where the total price is greater than $25, you can use the following Select Distinct statement with the Having clause:
SELECT [Customer ID], [Product Name], SUM(Quantity * Price) AS TotalPrice FROM Orders GROUP BY [Customer ID], [Product Name] HAVING SUM(Quantity * Price) > 25;The above Select Distinct statement will return the following result set:
| Customer ID | Product Name | Total Price |
|---|---|---|
| 1001 | Product A | 40.00 |
| 1002 | Product B | 60.00 |
Select Distinct with Order By Clause
You can use the Order By clause with the Select Distinct statement to sort the result set by one or more columns in ascending or descending order. The Order By clause sorts the result set based on the values in the specified columns.
Example:
Let’s say you have the following table:
| Customer ID | Order ID | Order Date |
|---|---|---|
| 1002 | 103 | 2021-01-03 |
| 1001 | 101 | 2021-01-01 |
| 1002 | 104 | 2021-01-04 |
| 1001 | 102 | 2021-01-02 |
If you want to retrieve the unique customer IDs from the above table, sorted in descending order of the latest order date, you can use the following Select Distinct statement with the Order By clause:
SELECT DISTINCT [Customer ID] FROM Orders ORDER BY [Order Date] DESC;The above Select Distinct statement will return the following result set:
| Customer ID |
|---|
| 1002 |
| 1001 |
Select Distinct with Group By Clause
You can use the Group By clause with the Select Distinct statement to group the result set by one or more columns. The Group By clause groups the rows based on the values in the specified columns and applies an aggregate function (such as Sum, Count, or Avg) to each group.
Example:
Let’s say you have the following table:
Source :
| Order ID |
|---|