SELECT DISTINCT Query in SQL

Published : 06 July 2024
Updated : 06 July 2024

125 Views

SQL (Structured Query Language) is the cornerstone of database management. It allows users to retrieve and manipulate data stored in relational databases. that means SQL allows users to perform CURD operation on database. CURD is fundamental operation of SQL.

Among these fundamental operations, SELECT DISTINCT query is used to fetch distinct rows from database. you can specify criteria of fetching data and can also apply some other operations like grouping , ordering etc. it is useful where result set rows contains duplicate values and you only want to fetch the unique (distinct) values.

Syntax

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

SELECT DISTINCT Clause is used to retrieve unique (distinct) value of specifies columns set from the database table.
FROM Clause is used to specifies the tables from which to retrieve data.

Let's assume we have 2 tables Users (Id, Name , Mobile ,DOB) and UserAddress (Id, UserId, AddressType, Address , Country , City and PostalCode)

Selecting Distinct specifies columns from Users table.

If you want to fetch distinct specifies columns from a table . you can use SELECT DISTINCT statement

SQL query to fetch a list of unique user names from the Users table

SELECT DISTINCT Name FROM Users;

Distinct Count

If you want to get distinct count of specific column from a table . you can use SELECT count (DISTINCT column) statement.

SELECT count(DISTINCT Name) FROM Users;

SELECT DISTINCT with WHERE

if you want to fetch data without duplicity from table based on certain condition. you have to use WHERE clause. Specify your condition in where clause.

SQL query to fetch distinct user names which mobile number start with 987.

SELECT DISTINCT Name FROM Users WHERE Mobile like "987%";

SQL query to fetch distinct user name and mobile combinations which mobile number start with 987.

SELECT DISTINCT Name, Mobile FROM Users WHERE Mobile like "987%";

SELECT DISTINCT with ORDER BY

if you want to fetch distinct data from table in ascending or descending order of certain columns . you have to use ORDER BY clause. The ORDER BY clause sort the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order that you specify in query. Default order of fetching data is ASC.

SQL query to fetch distinct user names that are sorted in name as ascending and DOB as descending order.

SELECT DISTINCT Name FROM Users ORDER BY Name ASC, DOB DESC;
                               OR
SELECT DISTINCT Name FROM Users ORDER BY Name , DOB DESC;

SELECT DISTINCT with LIMIT

if you want to fetch n distinct rows of data from table. you should use LIMIT clause.

SQL query for fetching 4 distinct or unique user name.

SELECT DISTINCT Name FROM Users LIMIT 4;

SELECT DISTINCT with JOIN

if you want to fetch distinct data from different tables. you should use JOIN clause.

SQL query for fetching distinct user name which Permanent address is India.

SELECT DISTINCT u.Name
FROM Users as u 
JOIN UserAddress as ua on u.Id=ua.UserId
WHERE AddressType = "Pre" and Country = "india";

SELECT DISTINCT with Multiple Clause

if you want to fetch distinct data where you need to apply multiple clause. you can write a single query with multiple clause based on your requirement below is the simple query structure of such cases.

SELECT DISTINCT column1, column2,...... 
FROM table_name t1
JOIN table_name2 t2 
      ON t1.columnP = t2.columnQ and t1.columnR = t2.columnS .........
WHERE columnX like "___" 
order by columnY 
LIMIT n;