SQL DISTINCT

What is SQL DISTINCT?

The SQL DISTINCT keyword is used to retrieve unique values from a specified column or set of columns in a database table. It eliminates duplicate records, ensuring that only distinct, non-repeated values are returned.

When you would use it

You would use the DISTINCT keyword when you want to eliminate duplicate values and retrieve a list of unique values from one or more columns in a table. It is especially useful when working with datasets that may contain redundant or repeated information.

Syntax

The syntax for using DISTINCT is as follows:

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

• column1, column2, ...: The column(s) from which you want to retrieve distinct values.
• table_name: The name of the table containing the data.
• condition (optional): An optional condition to filter the rows.

Parameter values

• column1, column2, ...: The column(s) for which you want to retrieve distinct values. These columns should be part of the specified table.
• table_name: The table where the data is stored.
• condition (optional): A condition that filters the rows if you want to apply additional filtering before returning distinct values.

Example query

Suppose we have a table named "employees" with columns "department" and "employee_name." We want to retrieve a list of distinct departments:

SELECT DISTINCT department
FROM employees
-- Comment the line below to show it doesn't affect the query.
-- WHERE salary > 50000;

In the above query, we use the DISTINCT keyword to retrieve a list of distinct departments from the "employees" table.

Example table response

Assuming the "employees" table contains the following data:

| department   | employee_name |
|------------  | ------------- |
| HR           | John          |
| Sales        | Mary          |
| HR           | Alice         |
| Marketing    | Bob           |
| Sales        | Carol         |

The query mentioned earlier would return the following result:

| department   |
|-------------  |
| HR           |
| Sales        |
| Marketing    |

This result contains only the distinct department values from the "employees" table.

Use cases

• Removing duplicate entries from query results.
• Generating lists of unique values, such as product categories, cities, or customer names.
• Ensuring data integrity by identifying unique keys or identifiers.

SQL languages this is available for

The SQL DISTINCT keyword is a standard SQL feature and is available in most relational database management systems (RDBMS) that support SQL. This includes popular RDBMS like MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The specific syntax and behavior may vary slightly between database systems, but the fundamental functionality remains the same.