The main difference between Unique and Distinct in SQL is that Unique helps to ensure that all the values in a column are different while Distinct helps to remove all the duplicate records when retrieving the records from a table.
The UNIQUE keyword in SQL plays the role of a database constraint; it ensures there are no duplicate values stored in a particular column or a set of columns. On the other hand, the DISTINCT keyword is used in the SELECT statement to fetch distinct rows from a table.
So, what are distinct and what are unique values in Excel? Unique values are the items that appear in a dataset only once. Distinct values are all different items in a list, i.e. unique values and 1st occurrences of duplicate values.
The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
SELECT UNIQUE
is old syntax supported by Oracle's flavor of SQL. It is synonymous with SELECT DISTINCT
.
Use SELECT DISTINCT
because this is standard SQL, and SELECT UNIQUE
is non-standard, and in database brands other than Oracle, SELECT UNIQUE
may not be recognized at all.
Unique is a keyword used in the Create Table() directive to denote that a field will contain unique data, usually used for natural keys, foreign keys etc.
For example:
Create Table Employee(
Emp_PKey Int Identity(1, 1) Constraint PK_Employee_Emp_PKey Primary Key,
Emp_SSN Numeric Not Null Unique,
Emp_FName varchar(16),
Emp_LName varchar(16)
)
i.e. Someone's Social Security Number would likely be a unique field in your table, but not necessarily the primary key.
Distinct is used in the Select statement to notify the query that you only want the unique items returned when a field holds data that may not be unique.
Select Distinct Emp_LName
From Employee
You may have many employees with the same last name, but you only want each different last name.
Obviously if the field you are querying holds unique data, then the Distinct keyword becomes superfluous.
select unique is not valid syntax for what you are trying to do
you want to use either select distinct or select distinctrow
And actually, you don't even need distinct/distinctrow in what you are trying to do. You can eliminate duplicates by choosing the appropriate union statement parameters.
the below query by itself will only provide distinct values
select col from table1
union
select col from table2
if you did want duplicates you would have to do
select col from table1
union all
select col from table2
SELECT DISTINCT
and SELECT UNIQUE
behave the same way. While DISTINCT is ANSI SQL standard, UNIQUE is an Oracle specific statement.
SELECT UNIQUE
is invalid syntax. UNIQUE
is keyword for adding unique constraint on the column.
SELECT DISTINCT
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With