Today, we will learn the difference between SQL SELECT UNIQUE and SELECT DISTINCT in this article. As we all know that SQL is a query language that is used to access, create, delete, and modify data stored in a database system like SQL Server, Oracle, MySQL, etc. All these database systems have their query language like TSQL, PLSQL, etc. These query languages are based on ANSI standard SQL language which can also be used in these database systems to perform any transaction.
There are a few quite similar syntaxes used in SQL languages to perform database activities. Sometimes we get confused about them where to use what. Today, I will explain one similar syntax UNIQUE and DISTINCT. The meaning of these both words UNIQUE and DISTINCT is the same and the meaning is unique and not any likely or duplicate values. Whenever you use these statements, the output will ignore the duplicate values and display unique values. Both SELECT UNIQUE and SELECT DISTINCT behave in the same way and their output will be the same. We generally use these syntaxes for a field or columns which may not hold unique values and there is the possibility to store duplicate values.
The 3rd similar syntax is the UNIQUE constraint which is different from than SELECT UNIQUE which I explained above. The UNIQUE constraint is used while creating a table and mentioning it as a constraint for a column or set of columns to store only unique values and prevent duplicate values to be stored in that column. Don’t get confused with the UNIQUE constraint. Here, we will talk about SELECT UNIQUE which is used to fetch unique values from a column that may have duplicate values. The UNIQUE constraint is used while creating the table whereas the SELECT UNIQUE statement is used to access the data from the table.
As this article is dedicated to the SQL SELECT UNIQUE and SELECT DISTINCT topics then let’s discuss these both statements in the next section.
SQL SELECT UNIQUE
The SELECT UNIQUE statement is generally used in the Oracle database system to get unique values from a column of a database table. We cannot use this syntax in other RDBMS systems because SELECT UNIQUE is not an ANSI standard SQL statement. The standard ANSI SQL statements are valid to most database systems like Oracle, SQL Server, MySQL, etc. whereas non-standard SQL syntaxes are created by specific database vendors to use them in their own database systems.
The syntax to use this statement in Oracle is given below.
|
1 2 3 |
Select UNIQUE column from table |
Here,
- the column is the name of the column from which you want to fetch unique values
- the table is the name of the table on which the above query is being executed
SQL SELECT DISTINCT
The SQL SELECT DISTINCT is ANSI standard SQL statement that is used for the same purpose to get unique values from a column that may have duplicate values. This syntax can be used in multiple database systems, and it is not dedicated to only a specific database system like SELECT UNIQUE.
The syntax to use this statement in Oracle is given below.
|
1 2 3 |
Select DISTINCT column from table |
Here,
- the column is the name of the column from which you want to fetch unique values
- the table is the name of the table on which the above query is being executed
Use case Demonstration
Let me show you a use case demonstration while running both SQL statements on an Oracle database, where both these statements are valid to run. If we will run it in other database systems like SQL Server, then you will get the error while running SQL SELECT UNIQUE whereas you will get your output in case of SELECT DISTINCT statement and that’s why I have chosen Oracle database to show this demonstration.
First, I will connect to the Oracle database and create a table named Person with three columns ID, Name, and City. You don’t need to create a new table for this validation or demonstration, if you have any table where you can run these select statements then you can use that table as well for this demo.
I have executed the below SQL statements to create a table named Person.
|
1 2 3 4 5 6 7 |
CREATE TABLE Person ( ID varchar(20), Name varchar(50), City varchar(30) ); |
Here, you can see the table has been created by executing the above SQL statements in the below image.
Once the table is created, I inserted a few rows with random duplicate values in that table. Later we will use both SQL statements SELECT UNIQUE and SELECT DISTINCT to display this data.
|
1 2 3 4 5 6 7 |
INSERT INTO Person VALUES (1, 'Kunal', 'Mumbai'); INSERT INTO Person VALUES (2, 'Sachin', 'Mumbai'); INSERT INTO Person VALUES (3, 'Manvendra', 'Delhi'); INSERT INTO Person VALUES (4, 'Manvendra', 'Bangalore'); INSERT INTO Person VALUES (5, 'Kunal', 'Pune'); |
Here, you can validate whether your data has been inserted properly or not by running a simple SELECT statement.
|
1 2 3 4 5 |
Select * from sys.person GO Select count(*) from sys.person |
I executed the above SQL statements to display the inserted data in the below image. Here we can see there are duplicate rows in the table in column Name and City.
Now, let’s run the SQL SELECT UNIQUE statement on this table to display its output. I have executed SQL SELECT UNIQUE statement for both columns one after another to help you understand its output.
|
1 2 3 4 5 |
SELECT UNIQUE Name FROM Person GO SELECT UNIQUE City FROM Person |
As you can see, I have inserted a total of 5 rows so there is a total of 5 names in the column Name. Two names “Manvendra” and “Kunal” has been repeated so when we run SQL SELECT UNIQUE statement on this column, it should return unique values which means only 3 names i.e. Kunal, Sachin, and Manvendra because it will ignore the duplicate entries, and display only the unique once. Similarly, I have taken another example on column City to display all unique city names stored in the column City. We can see 5 values are stored in the column City for each respective row. Here, Mumbai is saved twice along with the other city names. So when we will use SQL SELECT UNIQUE on this column then it should return 4 entries, one for Mumbai and the remaining other unique values.
The below image is showing the outputs of both statements. Let’s have a look at the first statement which I ran on the column Name of the table Person. Here, we can see three names Manvendra, Kunal, and Sachin, we also expected the same values. Another output has also returned as per the expectation and here we can see four entries Pune, Delhi, Bangalore, and Mumbai.
We get the fact that duplicate values will be skipped while running the SQL SELECT UNIQUE statement and only one entry for the duplicate values will be considered by this statement o return in the output.
As I have stated above that both SQL statements SELECT UNIQUE and SELECT DISTINCT work in the same way and return the same output. Let’s have a look and validate it by running the SELECT DISTINCT statement on both columns of table Person. We will use the below SQL statements to get the result for DISTINCT syntax.
Here, the result should come the same as it has returned while running the SQL SELECT UNIQUE statement.
|
1 2 3 4 5 |
SELECT DISTINCT Name FROM Person GO SELECT DISTINCT City FROM Person |
Have a look at the output of the above SQL statements. Both SELECT DISTINCT statements are returning the same output in the below image. You can see both statements have returned the same values which are similar to the SELECT UNIQUE output.
Conclusion
This article has explained SQL SELECT UNIQUE and compared it with another SQL statement SELECT DISTINCT. Both these SQL statements are used to get the same output. The SELECT UNIQUE is an old syntax and is used only in the Oracle database system whereas SELECT DISTINCT is the latest ANSI SQL standard syntax which is used in many other database systems along with the Oracle database system as well. I have demonstrated their use case in the Oracle database and validated their output which returned as same.
Thank you for reading this article and I would encourage you all to write your feedback in the comment section so that we can improve in a better way.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023
- PostgreSQL vs MySQL: Understanding their differences - March 13, 2023