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
