Select Distinct Statement in SQL

In this article, we will explore different aspects of “Select Distinct Statement in SQL”. Let’s get started.

Table of Contents

Select Distinct Statement in SQL Syntax

The SELECT DISTINCT statement in SQL is used to retrieve unique values from one or more columns of a table. Here is the syntax for using the SELECT DISTINCT statement in SQL with a specific table:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Here’s an example to illustrate the syntax. Suppose you have a table named “employees” with columns “employee_id,” “first_name,” and “last_name,” and you want to retrieve distinct first names from this table. The SQL query would look like this:

SELECT DISTINCT first_name
FROM employees;

This query will return a list of unique first names from the “employees” table. If there are multiple employees with the same first name, only one instance of that first name will be included in the result set.

Let’s assume we have a sample “employees” table with the following records:

employee_id	first_name	last_name
1	John	Doe
2	Jane	Smith
3	John	Smith
4	Sarah	Johnson
5	Michael	Brown
6	Jane	Doe

If you run the SQL query SELECT DISTINCT first_name FROM employees;, it will retrieve the distinct values of the “first_name” column from the “employees” table. Here’s the output:

first_name
John
Jane
Sarah
Michael

Remember that the SELECT DISTINCT statement considers the entire row when determining uniqueness. If you want to find distinct combinations of values from multiple columns, simply include those columns in the SELECT clause.

Here’s an example that retrieves distinct combinations of “city” and “state” from a table named “locations”:

SELECT DISTINCT city, state
FROM locations;

This query will return unique combinations of city and state values from the “locations” table.

Combine SELECT DISTINCT Statement with the WHERE clause

You can combine the SELECT DISTINCT statement with the WHERE clause to retrieve distinct values that meet specific conditions. Here’s an example:

Let’s use the same “employees” table with the following records:

employee_id	first_name	last_name
1	John	Doe
2	Jane	Smith
3	John	Smith
4	Sarah	Johnson
5	Michael	Brown
6	Jane	Doe

Suppose you want to retrieve distinct “first_name” values for employees who belong to the “Smith” last name. You can use the following SQL query:

SELECT DISTINCT first_name
FROM employees
WHERE last_name = 'Smith';

The result of this query will be:

first_name
Jane
John

In this example, we used the WHERE clause to filter the rows where the “last_name” is ‘Smith,’ and then we applied SELECT DISTINCT to get unique “first_name” values from the filtered rows.

Combining SELECT DISTINCT with subqueries

Combining SELECT DISTINCT with subqueries in SQL can be useful when you want to retrieve distinct values from the result of a subquery. Subqueries are queries nested inside other queries, and they can return a set of rows that you want to filter for distinct values. Here are some examples of how to use SELECT DISTINCT with subqueries:

Using SELECT DISTINCT with a Subquery in the WHERE Clause:Suppose you have two tables, “orders” and “customers,” and you want to find all distinct customer names who have placed an order. You can use a subquery to get the distinct customer IDs from the “orders” table and then retrieve their names from the “customers” table.

SELECT DISTINCT customer_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

In this example, the subquery (SELECT DISTINCT customer_id FROM orders) retrieves distinct customer IDs from the “orders” table, and the outer query retrieves the customer names based on those IDs.

Using SELECT DISTINCT with a Subquery in the FROM Clause:Let’s say you have a table called “sales” with columns “product_id” and “sales_amount,” and you want to find the total sales amount for each distinct product. You can use a subquery to calculate the sum of sales amounts for each product ID and then retrieve the distinct product IDs and their corresponding total sales.

SELECT DISTINCT product_id, total_sales
FROM (
    SELECT product_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY product_id
) AS subquery_result;

In this example, the subquery calculates the total sales amount for each product ID and groups the results by product ID. The outer query then retrieves the distinct product IDs and their corresponding total sales from the subquery result.

Using SELECT DISTINCT with a Subquery in the HAVING Clause:Suppose you have a table called “employees” with columns “department” and “salary,” and you want to find departments where the average salary is greater than a certain threshold. You can use a subquery to calculate the average salary for each department and then retrieve the distinct department names based on the average salary.

SELECT DISTINCT department
FROM employees
GROUP BY department
HAVING AVG(salary) > (
    SELECT DISTINCT AVG(salary) AS avg_salary
    FROM employees
    WHERE department = 'Sales'
);

In this example, the subquery (SELECT DISTINCT AVG(salary) AS avg_salary FROM employees WHERE department = 'Sales') calculates the average salary for the ‘Sales’ department. The outer query then retrieves the distinct department names where the average salary is greater than the value calculated in the subquery.

Use-Cases of Select Distinct Statement in SQL

Let’s delve into the key applications of the SELECT DISTINCT statement in SQL:

De-Duplication: One of the most common use cases for SELECT DISTINCT is eliminating duplicate values from query results. For example, in a customer database, you might have multiple entries with the same last name. Using SELECT DISTINCT, you can retrieve a unique list of last names to avoid redundancy.

SELECT DISTINCT last_name FROM customers;

Counting Unique Values: You can use SELECT DISTINCT in combination with aggregate functions like COUNT() to determine the number of unique occurrences within a column. This is useful for generating summary reports.

SELECT COUNT(DISTINCT product_category) AS unique_categories FROM products;

Preparation for Aggregation: Before applying aggregate functions such as SUM, AVG, or MAX, you may want to ensure that you’re working with unique data. SELECT DISTINCT allows you to prepare your data for meaningful calculations.

SELECT DISTINCT product_id, MAX(price) AS max_price FROM sales GROUP BY product_id;

Best Practices for Using Select Distinct Statement in SQL

Using the SELECT DISTINCT statement in SQL can be valuable for retrieving unique values from a dataset. However, it’s important to follow best practices to ensure efficient and effective use of this statement. Here are some best practices for using SELECT DISTINCT in SQL:

Understand When to Use It:
- Use SELECT DISTINCT when you want to retrieve unique or distinct values from one or more columns in a table.
Consider Alternative Approaches:
- Evaluate whether SELECT DISTINCT is the most efficient way to achieve your goal. In some cases, using GROUP BY or other techniques might be more suitable.
Limit Its Use:
- Avoid using SELECT DISTINCT excessively in your queries, especially in large datasets, as it can be resource-intensive.
Use Indexes:
- Ensure that columns you use with SELECT DISTINCT are properly indexed to improve query performance.
Combine with Other Clauses:
- Combine SELECT DISTINCT with other SQL clauses like WHERE and ORDER BY to refine your results.
Avoid Combining Multiple Columns:
- Using SELECT DISTINCT with multiple columns can lead to complex and inefficient queries. Be cautious when using it on multiple columns simultaneously.
Use Subqueries Wisely:
- When using SELECT DISTINCT with subqueries, make sure the subquery is optimized and returns a manageable result set.

Conclusion: Select Distinct Statement in SQL

We’ve explored the versatile “SELECT DISTINCT Statement in SQL.” It’s a valuable tool for retrieving unique values from one or more columns, helping you eliminate duplicates, count unique occurrences, and prepare data for aggregation. To use it effectively, remember to:

Master the Syntax: Understand the basic syntax of SELECT DISTINCT for querying unique values.
Combine with WHERE: Enhance its power by combining it with the WHERE clause to filter and retrieve specific distinct values.
Leverage Subqueries: Use it with subqueries to work with more complex datasets and conditions.

By following these best practices, you can harness the full potential of SELECT DISTINCT in your SQL queries and efficiently handle unique data requirements.

Related Articles :