PostgreSQL: SELECT DISTINCT Tutorial

Welcome to The Coding College, your go-to resource for all things coding and programming! In this tutorial, we’ll cover the SELECT DISTINCT statement in PostgreSQL, which is essential for retrieving unique values from your database tables.

What is SELECT DISTINCT?

The SELECT DISTINCT statement is used to eliminate duplicate rows from the query results and return only unique values from the specified columns.

Syntax

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

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

Why Use SELECT DISTINCT?

When dealing with large datasets, you may encounter duplicate values in your tables. Using SELECT DISTINCT ensures your query results contain only unique records, making your data analysis more meaningful.

Example: Sample Table

Let’s consider a table named students:

Retrieve Unique Values

Example 1: Unique Grades

SELECT DISTINCT grade FROM students;

Result:

Example 2: Unique Names

SELECT DISTINCT name FROM students;

Result:

Using SELECT DISTINCT on Multiple Columns

When you specify multiple columns, SELECT DISTINCT considers the combination of those columns to determine uniqueness.

Example 3: Unique Name and Grade Combinations

SELECT DISTINCT name, grade FROM students;

Result:

Combining SELECT DISTINCT with Other Clauses

You can combine SELECT DISTINCT with clauses like WHERE, ORDER BY, and LIMIT for more refined queries.

Example 4: Unique Grades for Students Aged 22 or Older

SELECT DISTINCT grade  
FROM students  
WHERE age >= 22;

Result:

Example 5: Sort Unique Names

SELECT DISTINCT name  
FROM students  
ORDER BY name ASC;

Result:

Limitations of SELECT DISTINCT

• Performance: For large datasets, SELECT DISTINCT can be slower because PostgreSQL must compare all rows to find unique values.
• Specificity: It identifies uniqueness based on the exact combination of specified columns.

Common Use Cases

• Removing Duplicates in Data Analysis

SELECT DISTINCT city FROM customers;

• Generating Reports with Unique Combinations

SELECT DISTINCT department, salary FROM employees;

• Filtering Unique Categories in E-commerce

SELECT DISTINCT category FROM products;

Learn More at The Coding College

Explore more PostgreSQL tutorials and resources at The Coding College. We focus on creating expert, accurate, and user-beneficial content in alignment with Google’s E-E-A-T principles.

Conclusion

The SELECT DISTINCT statement is a simple yet powerful tool for ensuring your query results are free from duplicate entries. By understanding its functionality and combining it with other SQL clauses, you can craft precise and efficient queries.

Need more help? Reach out to The Coding College for expert guidance and support!