Difference between Select Unique and Select Distinct

I thought these were synonomous, but I wrote the following in Microsoft SQL:

Select Unique col from 
     (select col from table1 union select col from table2) alias

And it failed. Changing it to

Select Distinct col from 
     (select col from table1 union select col from table2) alias

fixed it. Can someone explain?

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

Only In Oracle =>

SELECT DISTINCT and SELECT UNIQUE behave the same way. While DISTINCT is ANSI SQL standard, UNIQUE is an Oracle specific statement.

In other databases (like sql-server in your case) =>

SELECT UNIQUE is invalid syntax. UNIQUE is keyword for adding unique constraint on the column.

SELECT DISTINCT

1. Unique was the old syntax while Distinct is the new syntax,which is now the Standard sql.

2. Unique creates a constraint that all values to be inserted must be different from the others. An error can be witnessed if one tries to enter a duplicate value. Distinct results in the removal of the duplicate rows while retrieving data.

3. Example: SELECT DISTINCT names FROM student ;

   CREATE TABLE Persons ( Id varchar NOT NULL UNIQUE, Name varchar(20) );