Difference between Distinct and Unique

Difference between Distinct and Unique March 9, 2007

Posted by Muhammad Habib in Oracle General.
trackback

Specify DISTINCT or UNIQUE if you want Oracle 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.Restrictions on DISTINCT and UNIQUE Queries

When you specify DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.
You cannot specify DISTINCT if the select_list contains LOB columns.

Like this:

Be the first to like this post.

Comments»

1. Idetrorce - December 16, 2007: very interesting, but I don’t agree with you
Idetrorce

Reply
2. music - January 6, 2008: very interesting.
i’m adding in RSS Reader

Reply
3. Mark Mestetskiy - April 9, 2009: I beleive there is another difference, may be not exactly related to Oracle. UNIQUE is Oracle proprietary term. DISTINCT is more standard used in ANSI SQL. While Disctinct will work with MySQL or other similar products, UNIQUE – won’t.

Reply
4. Habib Gohar - April 10, 2009: Thanks for your comments MARK

Reply
5. dbametrix - October 21, 2009: Hi,

Exactly Habib and Mark both are right in way of explain in same topic.

Thanks and regards,
Gitesh
http://www.dbametrix.com

Reply