How to use SQL SELECT DISTINCT

SQL SELECT DISTINCT ensures that only unique records and rows are included in the results of a data query. It removes du­plic­ates from the records and rows in question, enabling clearer and more precise data analysis.

What is SQL SELECT DISTINCT?

SQL SELECT DISTINCT is a pro­cessing operator that’s used with the SQL SELECT command. In queries, SELECT selects one or more columns and records. Combined with the DISTINCT operator, it ensures that your query results contain only unique rows and removes duplicate rows and values. You can combine SELECT DISTINCT with other SQL operators and SQL commands to customise your queries based on your in­di­vidu­al needs.

What’s the dif­fer­ence between SQL SELECT DISTINCT and SELECT UNIQUE

‘Unique’ and ‘distinct’ have similar meaning as words, but the SQL operators UNIQUE and DISTINCT serve different purposes. To use SQL SELECT DISTINCT correctly, you should be aware of how it differs from UNIQUE.

What is the syntax of SQL SELECT DISTINCT?

The basic syntax for SQL SELECT DISTINCT looks as follows:

SELECT  DISTINCT  ColumnA, ColumnB, …
FROM table

It uses the following para­met­ers:

• SELECT DISTINCT: Here is where you specify which columns you’d like to use the SELECT command and the DISTINCT operator on.
• FROM: Specify here which table contains those columns.
• WHERE: In an optional WHERE clause, you can add ad­di­tion­al con­di­tions to narrow down your query.

What is SQL SELECT DISTINCT used for?

There are countless use cases for SQL SELECT DISTINCT. Here are just a few examples:

• Reduce customer, order, patient or product data that appears in multiple rows into one unique entry
• Count and display the number of purchases based on orders and product IDs
• Retrieve unique patient or customer data
• Display all shipping addresses or suppliers in a region

Examples of SQL SELECT DISTINCT

We’ll now look at 2 examples using SELECT DISTINCT.

Retrieve all customers

Let’s say you want to retrieve all unique customers from a table called ‘Orders’. You don’t want to have any duplicate rows if a customer has made multiple orders. The syntax for that would look as follows:

SELECT  DISTINCT  Customer_name
FROM  Orders

Filtering customers from a certain region

You can use a WHERE clause to add ad­di­tion­al con­di­tions to your eval­u­ation of customer data. Let’s say you want to retrieve the names of all customers in the UK from a table called ‘Orders’. Here’s how that would look:

SELECT  DISTINCT  Customer_name, Country
FROM  Orders
WHERE  Country  =  'UK'

Are there al­tern­at­ives to SQL SELECT DISTINCT?

SELECT DISTINCT is a practical way to remove du­plic­ates from your queries, but it’s not always the best solution. When applied in large data sets, it uses a lot of resources comparing each in­di­vidu­al column. In that case, you can look at these al­tern­at­ives with similar functions:

• GROUP BY: SQL GROUP BY clauses group together records from the columns you specify. You can make them more precise using ad­di­tion­al functions and operators.
• EXISTS: SQL EXISTS checks whether values and records are present in another table.
• COUNT: Combine DISTINCT with SQL COUNT to count the unique values in a column.
• SELECT + FROM + WHERE: You can narrow down your results better with a subquery (a query nested inside another query). In the subquery, choose the column using SELECT, specify which table you’re working in with FROM and then set the con­di­tions you want results to satisfy with WHERE.