SQL SELECT UNIQUE and SELECT DISTINCT

In the world of SQL, retrieving unique data from a table is a common requirement. While both SELECT UNIQUE and SELECT DISTINCT serve this purpose, they have slight historical differences. Let’s delve into their background, syntax, and practical use cases to clarify their roles.

Complete Advance AI topics:- CLICK HERE
Complete Python Course with Advance topics:-Click here

The History of SELECT UNIQUE vs. SELECT DISTINCT

The SELECT UNIQUE keyword is an older syntax introduced in Oracle databases. Over time, the ANSI SQL standard defined SELECT DISTINCT as the official keyword for retrieving unique rows from a table. Oracle later adopted DISTINCT in adherence to the standard but retained support for UNIQUE for backward compatibility. This means that although UNIQUE and DISTINCT perform the same function, the latter is now the preferred and standardized syntax.

In simple terms, both SELECT UNIQUE and SELECT DISTINCT are used to fetch distinct rows from a table by eliminating duplicates. However, modern SQL practitioners are encouraged to use SELECT DISTINCT for consistency and clarity.

Syntax of SELECT UNIQUE

The syntax for using SELECT UNIQUE is straightforward:

SELECT UNIQUE column_name  
FROM table_name;  

Syntax of SELECT DISTINCT

The syntax for SELECT DISTINCT is nearly identical:

SELECT DISTINCT column_name, column_name  
FROM table_name;  

Both commands achieve the same goal of filtering out duplicate values. However, SELECT DISTINCT is widely used today due to its alignment with modern SQL standards.

Practical Example: Retrieving Unique Data

Consider the following example. Suppose we have a table named students with the following data:

Student_Name	Gender	Mobile_Number	HOME_TOWN
Arjun Sharma	Male	7589462310	Jaipur
Priya Singh	Female	9823145690	Delhi
Kabir Mehta	Male	8897643251	Jaipur

If we want to retrieve a list of unique home towns, we can write the query as:

SELECT DISTINCT home_town  
FROM students;  

This query will return the following result:

HOME_TOWN
Jaipur
Delhi

This demonstrates how duplicate values are filtered, leaving only distinct entries in the result.

Key Takeaway

While both SELECT UNIQUE and SELECT DISTINCT achieve the same result, the use of DISTINCT is recommended for better readability and alignment with SQL standards. However, if you’re working with legacy systems, you might still encounter the UNIQUE keyword. Understanding the equivalence of these two keywords ensures smooth navigation in different SQL environments.

For more updates on SQL best practices, visit UpdateGadh, your go-to platform for professional SQL guidance!

Download New Real Time Projects :-Click here

---

sql select distinct multiple columns
select distinct on one column, with multiple columns returned sql
select unique sql
difference between unique and distinct in sql with example
select unique postgres
select distinct * from table
distinct vs unique meaning
select unique mysql
sql select unique and select distinct w3schools
sql select unique and select distinct values
sql select unique and select distinct example
sql select unique and select distinct oracle