SELECT DISTINCT in SQL eliminates all duplicate records from the result returned by the SQL query. The DISTINCT keyword is used in combination with the SELECT statement. Only unique records are returned when the DISTINCT keyword is used while fetching records from a table having multiple duplicate records.
Introduction to SQL SELECT DISTINCT
The SQL DISTINCT keyword is used in conjunction with the SELECT statement to fetch unique records from a table.
We use the DISTINCT keyword with the SELECT statement when there is a need to avoid duplicate values present in any specific columns/tables. When we use the DISTINCT keyword, SELECT statement returns only the unique records available in the table.
Syntax
SELECT DISTINCT expressions
FROM tables
[WHERE conditions]; Note
- When one expression is provided in the DISTINCT clause then the query will return the unique values of the expressions.
- The query will retrieve the unique combinations for the listed expressions if more than one expression is provided in the DISTINCT clause here.
- In SQL, the DISTINCT clause cannot ignore the NULL values. So when we use the DISTINCT clause in the SQL statement, our result set will include NULL as a distinct value.
Example
Consider the following EMPLOYEES table.
First, let us see the following SELECT query returns the duplicate salary records.
SELECT SALARY FROM EMPLOYEES
ORDER BY SALARY; Output
Example: Finding Unique Values in the Column using SQL SELECT DISTINCT
We have a table called suppliers with the following data:
From the above table, we are going to find the unique states.
SELECT DISTINCT state
FROM suppliers
ORDER BY state; Output
Conclusion
In conclusion, the SQL SELECT DISTINCT statement is a powerful tool for querying unique values from a database table. By using DISTINCT, duplicates are eliminated, allowing for cleaner and more focused results. Whether retrieving unique entries from a single column or across multiple columns, SELECT DISTINCT provides flexibility and efficiency in data retrieval, contributing to better analysis and decision-making processes. However, it’s important to use DISTINCT judiciously, considering its potential impact on performance and understanding the underlying data structure to ensure accurate and meaningful results.
Frequently Asked Questions
SQL SELECT DISTINCT is a statement used to retrieve unique records from a database table. It eliminates duplicate rows from the result set, ensuring that each row returned is distinct.
SELECT DISTINCT works by scanning the specified columns in a table and returning only unique values. It compares each row’s values and excludes any duplicates from the result set.
You should use SELECT DISTINCT when you need to retrieve unique values from a table, such as obtaining a list of distinct categories, unique customer names, or unique product IDs.
Yes, you can use SELECT DISTINCT with multiple columns. When using DISTINCT with multiple columns, it considers combinations of values across those columns and returns unique combinations.
Using SELECT DISTINCT may impact performance, especially on large tables, as it requires scanning the entire table to identify and eliminate duplicates. It’s important to use DISTINCT judiciously and consider indexing columns for improved performance.
