Possible Duplicate:
Unique vs distinct multi-column in Oracle 9i
Proper difference between SELECT DISTINCT and SELECT UNIQUE with points and examples.
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.
The main difference between unique and distinct is that UNIQUE is a constraint that is used on the input of data and ensures data integrity. While DISTINCT keyword is used when we want to query our results or in other words, output the data.
“Distinct” means total number of different values regardless how many times it appears in the dataset. A name appears in the list multiple times is counted as 1 distinct count. Whereas, the “Unique” value is total number of values that only appear once.
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.
SELECT DISTINCT
and SELECT UNIQUE
behave the same way in oracle.
While DISTINCT
is ANSI SQL standard, UNIQUE
is an Oracle specific statement.
Look here: http://psoug.org/definition/DISTINCT.htm
Oracle official documentation
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#sthref9346
States that:
DISTINCT | UNIQUE
Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.
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