PostgreSQL SELECT DISTINCT

PostgreSQL SELECT DISTINCT

The SELECT DISTINCT statement in PostgreSQL is used to return unique (non-duplicate) values from a column or a set of columns. It ensures that only distinct or unique values are returned, filtering out any repeated entries.

1. Basic Syntax of SELECT DISTINCT

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

• column1, column2, ...: The columns from which you want to retrieve unique values.
• table_name: The name of the table from which you are retrieving data.

2. Example: Retrieve Unique Values from a Single Column

Example 1: Get Unique Cities

SELECT DISTINCT city
FROM customers;

• This returns a list of unique cities from the customers table, removing any duplicates.

Example 2: Get Unique Product Categories

SELECT DISTINCT category
FROM products;

• This returns the unique categories of products available in the products table.

3. Example: Retrieve Unique Combinations of Multiple Columns

Example 3: Get Unique Combinations of First and Last Names

SELECT DISTINCT first_name, last_name
FROM employees;

• This query returns unique combinations of first and last names in the employees table, ensuring that duplicate names are removed.

Example 4: Get Unique Department and Salary Combinations

SELECT DISTINCT department, salary
FROM employees;

• Returns the unique combinations of department and salary, removing duplicates where both values are the same.

4. Example: Using DISTINCT with Aggregates

Example 5: Find the Distinct Count of Cities

SELECT COUNT(DISTINCT city)
FROM customers;

• This query counts the number of distinct cities in the customers table.

5. Performance Considerations

• Efficiency: Using DISTINCT can slow down queries, especially when applied to multiple columns or large datasets. The database engine needs to sort or hash the rows to remove duplicates.
• Indexes: If the column you're applying DISTINCT on is indexed, the operation will be faster. For multiple columns, composite indexes may improve performance.

6. Difference Between DISTINCT and GROUP BY

Both DISTINCT and GROUP BY can be used to eliminate duplicate results, but there are differences:

• DISTINCT: Removes duplicates in the selected columns.
• GROUP BY: Groups rows with similar values and allows aggregation, like COUNT(), SUM(), etc.

Example 6: Using GROUP BY for Distinct Values

SELECT department
FROM employees
GROUP BY department;

• This query returns distinct departments using GROUP BY. However, it can also be used with aggregation, which is not possible with DISTINCT.

7. Summary

Operation	SQL Command
Unique Values from One Column	SELECT DISTINCT column FROM table;
Unique Combinations of Multiple Columns	SELECT DISTINCT col1, col2 FROM table;
Distinct Count of Values	SELECT COUNT(DISTINCT column) FROM table;
Performance Considerations	Can slow down large queries. Use indexing for optimization.
Difference with GROUP BY	DISTINCT filters duplicates, while GROUP BY groups rows and can perform aggregation.

Would you like an example with more complex data, like filtering duplicates with joins? 🚀