SQL Select Unique 和 Select Distinct 之间的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/336268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between Select Unique and Select Distinct
提问by user42712
I thought these were synonomous, but I wrote the following in Microsoft SQL:
我认为这些是同义词，但我在 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?
修复。有人可以解释一下吗？
回答by Bill Karwin
SELECT UNIQUEis old syntax supported by Oracle's flavor of SQL. It is synonymous with SELECT DISTINCT.
SELECT UNIQUE是 Oracle 风格的 SQL 支持的旧语法。它是 的同义词SELECT DISTINCT。
Use SELECT DISTINCTbecause this is standard SQL, and SELECT UNIQUEis non-standard, and in database brands other than Oracle, SELECT UNIQUEmay not be recognized at all.
使用SELECT DISTINCT是因为这是标准 SQL，并且SELECT UNIQUE是非标准的，并且在 Oracle 以外的数据库品牌中，SELECT UNIQUE可能根本无法识别。
回答by BenAlabaster
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.
Unique 是 Create Table() 指令中使用的关键字，用于表示字段将包含唯一数据，通常用于自然键、外键等。
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 来通知查询，当字段包含可能不唯一的数据时，您只希望返回唯一项。
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 Distinctkeyword becomes superfluous.
显然，如果您查询的字段包含唯一数据，那么Distinct关键字就变得多余了。
回答by mson
select unique is not valid syntax for what you are trying to do
select unique 不是您尝试执行的操作的有效语法
you want to use either select distinct or select distinctrow
您想使用 select distinct 或 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.
实际上，您甚至不需要在您尝试做的事情中使用 distinct/distinctrow。您可以通过选择适当的联合语句参数来消除重复项。
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
回答by Somnath Muluk
Only In Oracle =>
仅在 Oracle =>
SELECT DISTINCTand SELECT UNIQUEbehave the same way. While DISTINCT is ANSI SQL standard, UNIQUE is an Oracle specific statement.
SELECT DISTINCT并SELECT UNIQUE以同样的方式行事。DISTINCT 是 ANSI SQL 标准，而 UNIQUE 是 Oracle 特定的语句。
In other databases (like sql-server in your case) =>
在其他数据库中（如您的情况下的 sql-server）=>
SELECT UNIQUEis invalid syntax. UNIQUEis keyword for adding unique constraint on the column.
SELECT UNIQUE是无效的语法。UNIQUE是在列上添加唯一约束的关键字。
回答by aashna mahajan
- Uniquewas the old syntax while Distinctis the new syntax,which is now the Standard sql.
- Uniquecreates 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. Distinctresults in the removal of the duplicate rows while retrieving data.
Example: SELECT DISTINCTnames FROM student ;
CREATE TABLE Persons ( Id varchar NOT NULL UNIQUE, Name varchar(20) );
- Unique是旧语法，而Distinct是新语法，现在是标准 sql。
- Unique创建了一个约束，即所有要插入的值必须与其他值不同。如果尝试输入重复值，则可能会出现错误。 Distinct导致在检索数据时删除重复的行。
示例：SELECT DISTINCTnames FROM student ;
CREATE TABLE Persons (Id varchar NOT NULL UNIQUE, Name varchar(20));
