As we speak, we are going to study the distinction between SQL SELECT UNIQUE and SELECT DISTINCT on this article. As everyone knows that SQL is a question language that’s used to entry, create, delete, and modify information saved in a database system like SQL Server, Oracle, MySQL, and many others. All these database programs have their question language like TSQL, PLSQL, and many others. These question languages are primarily based on ANSI commonplace SQL language which can be utilized in these database programs to carry out any transaction.
There are just a few fairly comparable syntaxes utilized in SQL languages to carry out database actions. Typically we get confused about them the place to make use of what. As we speak, I’ll clarify one comparable syntax UNIQUE and DISTINCT. The which means of those each phrases UNIQUE and DISTINCT is similar and the which means is exclusive and never any doubtless or duplicate values. Everytime you use these statements, the output will ignore the duplicate values and show distinctive values. Each SELECT UNIQUE and SELECT DISTINCT behave in the identical means and their output would be the similar. We usually use these syntaxes for a subject or columns which can not maintain distinctive values and there may be the likelihood to retailer duplicate values.
The threerd comparable syntax is the UNIQUE constraint which is completely different from than SELECT UNIQUE which I defined above. The UNIQUE constraint is used whereas making a desk and mentioning it as a constraint for a column or set of columns to retailer solely distinctive values and forestall duplicate values to be saved in that column. Don’t get confused with the UNIQUE constraint. Right here, we are going to discuss SELECT UNIQUE which is used to fetch distinctive values from a column which will have duplicate values. The UNIQUE constraint is used whereas creating the desk whereas the SELECT UNIQUE assertion is used to entry the information from the desk.
As this text is devoted to the SQL SELECT UNIQUE and SELECT DISTINCT subjects then let’s focus on these each statements within the subsequent part.
SQL SELECT UNIQUE
The SELECT UNIQUE assertion is usually used within the Oracle database system to get distinctive values from a column of a database desk. We can not use this syntax in different RDBMS programs as a result of SELECT UNIQUE will not be an ANSI commonplace SQL assertion. The usual ANSI SQL statements are legitimate to most database programs like Oracle, SQL Server, MySQL, and many others. whereas non-standard SQL syntaxes are created by particular database distributors to make use of them in their very own database programs.
The syntax to make use of this assertion in Oracle is given under.
|
Choose UNIQUE column from desk
|
Right here,
-
the column is the title of the column from which you need to fetch distinctive values -
the desk is the title of the desk on which the above question is being executed
SQL SELECT DISTINCT
The SQL SELECT DISTINCT is ANSI commonplace SQL assertion that’s used for a similar objective to get distinctive values from a column which will have duplicate values. This syntax can be utilized in a number of database programs, and it’s not devoted to solely a particular database system like SELECT UNIQUE.
The syntax to make use of this assertion in Oracle is given under.
|
Choose DISTINCT column from desk
|
Right here,
-
the column is the title of the column from which you need to fetch distinctive values -
the desk is the title of the desk on which the above question is being executed
Use case Demonstration
Let me present you a use case demonstration whereas operating each SQL statements on an Oracle database, the place each these statements are legitimate to run. If we are going to run it in different database programs like SQL Server, then you’ll get the error whereas operating SQL SELECT UNIQUE whereas you’ll get your output in case of SELECT DISTINCT assertion and that’s why I’ve chosen Oracle database to indicate this demonstration.
First, I’ll connect with the Oracle database and create a desk named Particular person with three columns ID, Title, and Metropolis. You don’t have to create a brand new desk for this validation or demonstration, in case you have any desk the place you may run these choose statements then you should utilize that desk as properly for this demo.
I’ve executed the under SQL statements to create a desk named Particular person.
|
CREATE TABLE Particular person ( ID varchar(20), Title varchar(50), Metropolis varchar(30) );
|
Right here, you may see the desk has been created by executing the above SQL statements within the under picture.
As soon as the desk is created, I inserted just a few rows with random duplicate values in that desk. Later we are going to use each SQL statements SELECT UNIQUE and SELECT DISTINCT to show this information.
|
INSERT INTO Particular person VALUES (1, ‘Kunal’, ‘Mumbai’); INSERT INTO Particular person VALUES (2, ‘Sachin’, ‘Mumbai’); INSERT INTO Particular person VALUES (3, ‘Manvendra’, ‘Delhi’); INSERT INTO Particular person VALUES (4, ‘Manvendra’, ‘Bangalore’); INSERT INTO Particular person VALUES (5, ‘Kunal’, ‘Pune’);
|
Right here, you may validate whether or not your information has been inserted correctly or not by operating a easy SELECT assertion.
|
Choose * from sys.individual GO Choose rely(*) from sys.individual
|
I executed the above SQL statements to show the inserted information within the under picture. Right here we are able to see there are duplicate rows within the desk in column Title and Metropolis.
Now, let’s run the SQL SELECT UNIQUE assertion on this desk to show its output. I’ve executed SQL SELECT UNIQUE assertion for each columns one after one other that can assist you perceive its output.
|
SELECT UNIQUE Title FROM Particular person GO SELECT UNIQUE Metropolis FROM Particular person
|
As you may see, I’ve inserted a complete of 5 rows so there’s a complete of 5 names within the column Title. Two names “Manvendra” and “Kunal” has been repeated so once we run SQL SELECT UNIQUE assertion on this column, it ought to return distinctive values which suggests solely 3 names i.e. Kunal, Sachin, and Manvendra as a result of it’s going to ignore the duplicate entries, and show solely the distinctive as soon as. Equally, I’ve taken one other instance on column Metropolis to show all distinctive metropolis names saved within the column Metropolis. We are able to see 5 values are saved within the column Metropolis for every respective row. Right here, Mumbai is saved twice together with the opposite metropolis names. So once we will use SQL SELECT UNIQUE on this column then it ought to return 4 entries, one for Mumbai and the remaining different distinctive values.
The under picture is exhibiting the outputs of each statements. Let’s take a look on the first assertion which I ran on the column Title of the desk Particular person. Right here, we are able to see three names Manvendra, Kunal, and Sachin, we additionally anticipated the identical values. One other output has additionally returned as per the expectation and right here we are able to see 4 entries Pune, Delhi, Bangalore, and Mumbai.
We get the truth that duplicate values might be skipped whereas operating the SQL SELECT UNIQUE assertion and just one entry for the duplicate values might be thought of by this assertion o return within the output.
As I’ve acknowledged above that each SQL statements SELECT UNIQUE and SELECT DISTINCT work in the identical means and return the identical output. Let’s take a look and validate it by operating the SELECT DISTINCT assertion on each columns of desk Particular person. We are going to use the under SQL statements to get the outcome for DISTINCT syntax.
Right here, the outcome ought to come the identical because it has returned whereas operating the SQL SELECT UNIQUE assertion.
|
SELECT DISTINCT Title FROM Particular person GO SELECT DISTINCT Metropolis FROM Particular person
|
Take a look on the output of the above SQL statements. Each SELECT DISTINCT statements are returning the identical output within the under picture. You’ll be able to see each statements have returned the identical values that are much like the SELECT UNIQUE output.
Conclusion
This text has defined SQL SELECT UNIQUE and in contrast it with one other SQL assertion SELECT DISTINCT. Each these SQL statements are used to get the identical output. The SELECT UNIQUE is an outdated syntax and is used solely within the Oracle database system whereas SELECT DISTINCT is the newest ANSI SQL commonplace syntax which is utilized in many different database programs together with the Oracle database system as properly. I’ve demonstrated their use case within the Oracle database and validated their output which returned as similar.
Thanks for studying this text and I’d encourage you all to put in writing your suggestions within the remark part in order that we are able to enhance in a greater means.
He loves to speak and write about database applied sciences. He has lead and delivered many tasks from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR options, automation, and main efficiency tuning tasks.
You may as well discover him on LinkedIn
