SQL DISTINCT Query

Overview: In This tutorial you will learn and understand how to use SQL DISTINCT query with examples.

The DISTINCT SQL query is used to return only distinct (unique) values from SQL table. Inside a table, a column often contains many duplicate values and sometimes you only want to list the unique values. Distinct query always used with SELECT statements in SQL.

Why you need SQL Distinct SQL?

This command is most usable command among SQL administrator or developers. This query can easily find the unique values in huge SQL table. You can treat it as pivot table as you do in Excel. So in excel once you drag column in pivot it always shows the unique values of that columns same goes with SQL Distinct query.

In below examples you will see how you can use distinct query for single or multiple columns in SQL table

Below are the syntax of SELECT DISTINCT in SQL

Syntax:

SELECT DISTINCT        
           column_name,        
FROM        
           table_name;

The query returns only unique values in the specified column. It removes the duplicate values in the column from the result.

Below is the syntax for SELECT DISTINCT in SQL for multiple columns:

SELECT DISTINCT
       column_name1,
       column_name2,
       ...
FROM
       table_name;

The query uses the combination of values in all specified columns in the SELECT command to extract the unique values.

If you apply the DISTINCT clause to a column that has NULL, the DISTINCT clause will keep only one NULL and eliminates the other. The DISTINCT clause treats all NULL “values” as the same value.

Example #1: SQL Distinct for one column

Below example is from “Customers_Tbl” to display the distinct/unique values of Province

SELECT DISTINCT
       Province
FROM
       Customers_Tbl

Result:

Below results shows the unique values of Province from “Customers_Tbl” .

you can also use the Count function to see the numbers of rows appear in front of Province, you need to do GROUP BY if you are using Count aggregated function in statement.

Example #2: SQL Distinct query for Multiple columns

Belwo example id for Multiple columns used in SELECT Distinct command, This statement returns all Province and Product Category of all customers:

SELECT DISTINCT
       Province, 
       Product_Category
FROM
       Customers_Tbl;

Result:

Example #3: SQL Distinct query with Count function

Lets take below table which have [Product category] column

If you want to count the numbers of distinct rows for [Product category] column in the table then you need to use COUNT function along with distinct query. As shown below Syntax.

SELECT COUNT(DISTINCT Product_Category)
FROM Customers_Tbl;

The above query will return “3” as output for unique row count.

Example #4: Distinct query with ORDER BY

You can use ORDER by clause also in the Distinct query just to sort the data in ASC and DESC order. Suppose you want to find the Distinct Age of the Customer but in sorted order from lowest to largest age, then you can use Order by Clause, as shown below syntax.

SELECT DISTINCT Age 
FROM Customers_Tbl
ORDER BY Age DESC;

This will return the age of customer in descending order.