Difference between Select Unique and Select Distinct

Question

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

User · Answer

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

User · Answer

Only In Oracle   gt  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    gt  SELECT UNIQUE is invalid syntax  UNIQUE is keyword for adding unique constraint on the column  SELECT DISTINCT

User · Answer

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

User · Answer

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

User · Answer

Unique was the old syntax while Distinct is the new syntax which is now the Standard sql  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  Example  SELECT DISTINCT names FROM student    CREATE TABLE Persons   Id varchar NOT NULL UNIQUE  Name varchar 20

User · Answer

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

User · Answer

Unique was the old syntax while Distinct is the new syntax which is now the Standard sql  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  Example  SELECT DISTINCT names FROM student    CREATE TABLE Persons   Id varchar NOT NULL UNIQUE  Name varchar 20

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Only In Oracle   gt  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    gt  SELECT UNIQUE is invalid syntax  UNIQUE is keyword for adding unique constraint on the column  SELECT DISTINCT

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Difference between Select Unique and Select Distinct

Examples related to sql

Examples related to sql-server