When working with databases, there are times when we need to retrieve unique values from a table instead of getting duplicate entries. This is where the SELECT DISTINCT statement in SQL comes into play. It allows us to filter out duplicate records and return only unique values from the specified columns. Let’s explore how SELECT DISTINCT works in SQL and go through some of the best SELECT DISTINCT examples.
How SELECT DISTINCT Works in SQL?
The SELECT DISTINCT statement is used in SQL to remove duplicate values from the result set. It works by scanning the specified column(s) and ensuring that each value appears only once in the final output.
The basic syntax of SELECT DISTINCT is:
SELECT DISTINCT column1, column2, ...
FROM table_name;
In this statement:
- column1, column2, … – The columns from which we want to retrieve distinct values.
- table_name – The table where the query is executed.
Best SELECT DISTINCT Examples
1. Removing Duplicates from a Single Column
Let’s say we have a table named employees with the following data:
| id | name | department |
|---|---|---|
| 1 | John | HR |
| 2 | Jane | IT |
| 3 | Michael | HR |
| 4 | Sarah | Finance |
| 5 | John | HR |
If we want to select unique department names, we can run:
SELECT DISTINCT department FROM employees;
The result will be:
| department |
|---|
| HR |
| IT |
| Finance |
2. Using SELECT DISTINCT on Multiple Columns
If we apply SELECT DISTINCT to multiple columns, it considers the combination of values to determine uniqueness.
SELECT DISTINCT name, department FROM employees;
The result will be:
| name | department |
|---|---|
| John | HR |
| Jane | IT |
| Michael | HR |
| Sarah | Finance |
Even though we have duplicate names, since the rows contain unique combinations of name and department, all records remain.
3. Using SELECT DISTINCT with COUNT()
If we want to count the number of distinct values in a column, we can use the COUNT() function with DISTINCT:
SELECT COUNT(DISTINCT department) FROM employees;
The output will be:
| COUNT(DISTINCT department) |
|---|
| 3 |
Performance Considerations
While SELECT DISTINCT helps in retrieving unique records, it does come with performance implications:
- Sorting Overhead: The database sorts the results to determine uniqueness, which can be slow for large datasets.
- Index Usage: If an index exists on the column, it can speed up DISTINCT operations.
- Alternative Approaches: In some cases, using
GROUP BYinstead ofDISTINCTmay offer better performance.
When Should You Use SELECT DISTINCT?
The SELECT DISTINCT command is useful in several scenarios, including:
- Eliminating duplicate records from query results.
- Finding unique values in a particular column.
- Analyzing diversity within dataset fields.
Final Thoughts
Understanding how SELECT DISTINCT works in SQL is crucial for writing efficient queries that retrieve only the necessary unique data. It helps in eliminating redundant entries and ensures more precise results. However, always consider performance implications and explore alternative approaches like GROUP BY when necessary. By using SELECT DISTINCT effectively, you can streamline your queries and improve database performance.
Other interesting article:
How FOREIGN KEY works in SQL? Best FOREIGN KEY examples