SELECT DISTINCT Query in SQL for Unique Records
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)
CREATE TABLE Users (
Id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Mobile VARCHAR(15) NOT NULL UNIQUE,
DOB DATE NULL,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
// --------------------------------------------------
CREATE TABLE UserAddress (
Id INT AUTO_INCREMENT PRIMARY KEY,
UserId INT NOT NULL,
AddressType ENUM('Home', 'Office', 'Other') NOT NULL,
Address VARCHAR(255) NOT NULL,
Country VARCHAR(100) NOT NULL,
City VARCHAR(100) NOT NULL,
PostalCode VARCHAR(20) NOT NULL,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT FK_UserAddress_Users
FOREIGN KEY (UserId)
REFERENCES Users(Id)
ON DELETE CASCADE
);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;Frequently Asked Questions (FAQs)
1. What does SELECT DISTINCT do in SQL?
SELECT DISTINCT is used to retrieve unique rows from a table by removing duplicate values from the result set. When applied, SQL compares the selected columns together and returns only one occurrence of each unique combination. It does not modify data in the table. it only affects how results are displayed.
2. Why is SELECT DISTINCT commonly used in databases?
Databases often contain duplicate values due to multiple entries, joins or historical records. SELECT DISTINCT helps clean the result set by eliminating duplicates, making reports, analytics and UI displays more accurate and meaningful without changing the underlying data.
3. How does SELECT DISTINCT work with multiple columns?
When multiple columns are specified, SQL treats the combination of those columns as a single unit. A row is considered duplicate only if all selected column values match another row. This is important because DISTINCT does not operate on individual columns independently.
4. What is the difference between SELECT DISTINCT and GROUP BY?
Both can return unique results, but they serve different purposes. SELECT DISTINCT is simpler and used purely to remove duplicates. GROUP BY is more powerful and is used with aggregate functions like COUNT, SUM or AVG. DISTINCT is preferred when aggregation is not required.
5. Can SELECT DISTINCT be used with WHERE conditions?
Yes. WHERE filters rows before DISTINCT is applied. This means SQL first applies the condition to reduce the dataset and then removes duplicates from the filtered results. This order is important for both correctness and performance.
6. How does SELECT DISTINCT work with COUNT?
To count unique values, SQL provides COUNT(DISTINCT column_name). This counts only distinct non-NULL values in the column. It is commonly used in analytics, reporting and interview questions where duplicate values must be ignored.
7. Does SELECT DISTINCT remove NULL values?
No. SELECT DISTINCT does not automatically remove NULL values. If multiple rows contain NULL in the selected column, they are treated as a single distinct value and returned once. If you want to exclude NULLs, you must explicitly use a WHERE clause.
8. Can SELECT DISTINCT be combined with ORDER BY?
Yes. ORDER BY can be used with DISTINCT to sort the final unique result set. However, in most databases, columns used in ORDER BY must also appear in the SELECT list when DISTINCT is used. This ensures predictable and correct sorting behavior.
9. Can SELECT DISTINCT impact performance?
Yes. DISTINCT requires SQL to compare rows and remove duplicates, which can be expensive on large datasets. Proper indexing and filtering with WHERE clauses can reduce performance impact. Avoid using DISTINCT unnecessarily, especially on large joined tables.
10. How does SELECT DISTINCT behave with JOINs?
When used with JOINs, DISTINCT removes duplicate rows that result from table relationships. This is common when one-to-many joins produce repeated values. DISTINCT helps return clean, non-duplicated results but should be used carefully to avoid hiding data issues.
11. Can SELECT DISTINCT be used with LIMIT?
Yes. LIMIT restricts the number of distinct rows returned after duplicates are removed. This is useful for pagination, previews or sampling unique values. The final output depends on the combination of DISTINCT, ORDER BY and LIMIT.
12. Is SELECT DISTINCT safe to use in production queries?
Yes, when used correctly. SELECT DISTINCT is read-only and does not change data. However, overusing it to hide data modeling issues or missing joins can cause performance problems. It should be used intentionally, not as a shortcut.
13. Does SELECT DISTINCT guarantee result order?
No. DISTINCT does not guarantee ordering. If result order matters, you must explicitly use ORDER BY. Relying on implicit order is unsafe and may lead to inconsistent results across executions or database systems.
14. Is SELECT DISTINCT commonly asked in SQL interviews?
Yes. Interviewers frequently ask questions about SELECT DISTINCT to test understanding of duplicates, grouping logic, performance implications and query correctness. Candidates are often asked to compare DISTINCT with GROUP BY or COUNT(DISTINCT).
15. What are common mistakes developers make with SELECT DISTINCT?
Common mistakes include assuming DISTINCT works on individual columns, using it to hide bad joins, ignoring performance impact and forgetting that DISTINCT applies to the entire selected column set. Understanding how DISTINCT works internally helps avoid incorrect results.