Home
PostgreSQL

PostgreSQL SELECT DISTINCT

The SELECT DISTINCT statement in PostgreSQL is a valuable tool for extracting unique values from one or more columns in a table. Whether you're...

The PostgreSQL SELECT DISTINCT statement is a powerful tool for retrieving unique values from a specific column or a combination of columns in a table. This statement is particularly useful when you want to eliminate duplicate records from your query results. 

PostgreSQL SELECT DISTINCT

Let's explore the syntax and usage of in PostgreSQL with examples to illustrate its functionality.

Basic Syntax 

The basic syntax of the statement is straightforward:

SELECT DISTINCT column1, column2, ...
FROM table_name;
  • : Specifies that only distinct (unique) values should be retrieved.
  • : Columns from which you want to retrieve distinct values.
  • : Specifies the table from which to retrieve the data.

Distinct Values from a Single Column

Obtaining distinct values from a single column is a common use case, and the SELECT DISTINCT statement in PostgreSQL is employed for this purpose. This statement allows you to retrieve unique values from a specified column in a table, eliminating any duplicates.

SELECT DISTINCT department
FROM employees;

In this example, the query retrieves distinct department names from the "employees" table. This is useful when you want to see a unique list of departments without duplicates.

Distinct Values from Multiple Columns

When you need unique combinations of values from multiple columns, SELECT DISTINCT allows you to obtain a distinct set based on the specified columns.

SELECT DISTINCT first_name, last_name
FROM employees;

This query retrieves distinct combinations of first names and last names from the "employees" table. It ensures that each combination is unique, helping you identify individuals without duplicates.

Distinct Values with Aggregation

PostgreSQL SELECT DISTINCT  can be combined with aggregation functions like , , , etc. This allows you to obtain unique values along with aggregate information, such as the average or total of a particular column.

SELECT COUNT(DISTINCT product_category) AS unique_categories
FROM products;

Here, the query counts the number of distinct product categories in the "products" table. The is used in conjunction with the function to get the count of unique categories.

Distinct Values with Filtering

You can use the SELECT DISTINCT statement in combination with the clause to retrieve distinct values from a column based on specific filtering conditions. 

SELECT DISTINCT city
FROM customers
WHERE country = 'USA';

In this example, the query retrieves distinct city names from the "customers" table but only for customers located in the USA. This showcases how can be combined with other clauses like to filter results.

Distinct Values with Sorting

You can use the SELECT DISTINCT statement in combination with the clause to retrieve distinct values from a column and sort them in a specified order. 

SELECT DISTINCT product_name
FROM inventory
ORDER BY product_name ASC;

This query retrieves distinct product names from the "inventory" table and orders the results in ascending alphabetical order. The clause can be combined with to control the sorting of unique values.

Distinct Values with NULL Handling

It is common to use in conjunction with clauses to filter out values, ensuring that only non-null unique values are retrieved.

SELECT DISTINCT city
FROM employees
WHERE region IS NOT NULL;

Here, the query retrieves distinct city names from the "employees" table, excluding rows where the "region" column is . This demonstrates how you can use in combination with a `WHERE` clause to handle values.

Distinct Values with Joins

PostgreSQL SELECT DISTINCT  can be seamlessly integrated with other SQL operations such as , enabling you to retrieve unique records from joined tables. This flexibility enhances its utility in complex queries.

SELECT DISTINCT customers.customer_id, customers.customer_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

In this example, the query retrieves distinct combinations of customer ID, customer name, and order date from the "customers" and "orders" tables using an . This illustrates how can be used in more complex queries involving multiple tables.

In conclusion, the statement in PostgreSQL is a valuable tool for extracting unique values from one or more columns in a table. Whether you're dealing with a single column or multiple columns, combining with other SQL clauses and functions allows you to tailor your queries to meet specific requirements. It's particularly useful for obtaining a clear, concise view of unique data within your database.