SQL DISTINCT

Last update on April 10 2024 06:06:12 (UTC/GMT +8 hours)

Select with distinct

Redundancy is the repetition of certain data in a table. With the use of DISTINCT clause data redundancy may be avoided. This clause will eliminate the repetitive appearance of same data. DISTINCT can come only once in a given select statement.

Syntax:

SELECT DISTINCT <column_name> 
FROM <table_name> 
WHERE <conditions>;

Parameters:

Name	Description
column_name	Name of the column.
table_name	Name of the table.
conditions	It may be a condition, a select query or an expression.

Pictorial presentation :

Some important questions related to the SQL DISTINCT keyword

What is the purpose of the DISTINCT keyword in SQL?

The DISTINCT keyword is used to retrieve unique values from a specific column or combination of columns in a result set.

Does using the DISTINCT keyword affect the order of the result set?

No, the DISTINCT keyword does not guarantee the order of the result set. If you need the result set to be sorted, you should use the ORDER BY clause in conjunction with DISTINCT.

Can you use the DISTINCT keyword with multiple columns?

Yes, the DISTINCT keyword can be used with multiple columns to retrieve unique combinations of values across those columns.

Can you use the DISTINCT keyword with aggregate functions?

Yes, the DISTINCT keyword can be used in conjunction with aggregate functions like COUNT(), SUM(), AVG(), etc., to perform calculations on unique values.

How does the DISTINCT keyword differ from the GROUP BY clause?

The DISTINCT keyword is used to retrieve unique values from one or more columns in a result set, while the GROUP BY clause is used to group rows that have the same values into summary rows and perform aggregate functions on them.

Can you use the DISTINCT keyword with subqueries?

Yes, the DISTINCT keyword can be used with subqueries to retrieve unique values from the result of the subquery.

What happens if you use the DISTINCT keyword with a column that contains NULL values?

The DISTINCT keyword treats NULL values as unique, so if a column contains NULL values, they will be considered as distinct values.

What is the performance impact of using the DISTINCT keyword?

Using the DISTINCT keyword may impact performance, especially on large datasets, as it requires sorting and removing duplicate values from the result set.

When should you use the DISTINCT keyword?

The DISTINCT keyword is used when you want to remove duplicate values from the result set and only display unique values.

Are there any alternatives to using the DISTINCT keyword?

Yes, you can achieve similar results using the GROUP BY clause, but it may have different implications depending on the specific requirements of the query. Additionally, some databases offer specific functions or operators for removing duplicates without sorting, such as the UNION operator.

Example-1: SQL DISTINCT

Sample table: orders

You can use the following SQL statement to retrieve all 'agent_code' values from the 'orders' table.

SQL Code:


SELECT agent_code FROM orders;
-- Select the 'agent_code' column from the 'orders' table

Explanation:

SELECT agent_code: This line specifies the column that you want to retrieve data from. It selects the 'agent_code' column from the 'orders' table.

FROM orders: This line specifies the table from which you want to retrieve data. In this case, it's the 'orders' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

AGENT_CODE
----------
A008
A004
A006
A010
A004
A011
A005
A013
A004
A005
A011

...
...

The above picture shows the same 'agent_code' appears more than once.

Example-2: SQL DISTINCT

To retrieve each unique 'agent_code' from the 'orders' table, you can use the following SQL statement.:

SQL Code:


SELECT DISTINCT agent_code 
-- Select distinct values of the 'agent_code' column
FROM orders;
-- From the table 'orders'

Explanation:

SELECT DISTINCT agent_code: This line specifies that you want to retrieve unique/distinct values from the 'agent_code' column. The DISTINCT keyword ensures that only unique values are returned; any duplicate values will be eliminated.

FROM orders: This line specifies the table from which you want to retrieve data. In this case, it's the 'orders' table.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

AGENT_CODE
----------
A004
A002
A007
A009
A011
A012
A010
A013
A001
A008
A006
A005
A003

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.