• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512688
  • Last Modified:

SQL UNIQUE and DISTINCT

Oracle 8i, HP UX server.  Can someone tell me the difference (if any) between the oracle UNIQUE and DISTINCT keywords?  They seem to work exactly the same.  If I say:

SELECT UNIQUE(COLUMN) FROM TABLE

i get the same results as saying

SELECT DISTINCT(COLUMN) FROM TABLE

Is there some internal difference in how to two of them operate on the tables they are selecting from or something?  thanks.
0
benpung
Asked:
benpung
  • 3
  • 2
  • 2
  • +5
1 Solution
 
Mark GeerlingsDatabase AdministratorCommented:
I've never tried the "unique" keyword in a select statement, so I don't know.  (I have used it often in a "create unique index..." statement, but I didn't even know it was valid in queries.)

Be careful with the "distinct" keyword.  That can give you the results you expect when you select only one column.  If you try it with multiple columns being selected, the results may surprise you - they may not be what you expect.

Also, the "distinct" keywork in a query always forces a sort operation, but depending on the data and the query, that may not be needed.  If it is not needed, it is just a performance penalty to add it.  Some people (especially those with SQL Server experience) tend to add the word "distinct" to most queries apparently out of habit.  That is not a good habit in Oracle.  Usually in Oracle, a group operator (like: count, sum, min, max, avg, etc.) on one column along with a "group by" clause for the other column(s) is more reliable than "distinct" when multiple columns are being returned by a query.
0
 
seazodiacCommented:
benpung:

THere is a story behind this:

Long, long time ago, Oracle RDBMS has this UNIQUE keyword even before ANSI SQL standard come into birth.

And no doubt, this UNIQUE is the oracle way of get distinct records.

but then ANSI SQL comes along, and say ok, every RDBMS gonna have to use DISTINCT, because this is the standard,

So Oracle add DISTINCT into its sql bank, but Did NOT decommission the UNIQUE.

there you go, so they are coexisting in ORacle.

you won't see anywhere else...
0
 
pankajtiwaryCommented:
Hi benpung,

Well, there is no difference between distinct and unique. I have seen people who come from Oracle background use distinct and people from other database who follow the holy ANSI sql use unique, but there are no differences as far as the usage is concerned.

Cheers!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
seazodiacCommented:
No, please don't mislead.

DISTINCT is ANSI standard,

UNIQUE is Product specific...
0
 
andrewstCommented:
@markgeer,

I feel sure we have had this debate before, but I can't recall the outcome.  You always say that DISTINCT performs worse than GROUP BY; I don't believe that, and in experiments I can't make it so .  Can you provide an example that proves it?
0
 
Mark GeerlingsDatabase AdministratorCommented:
No, I have not claimed that "distinct" performs worse than "group by".  I think their performance is equal.  My problem with "distinct" is that the results are often not what I expect, especially when multiple columns are being selected.  If the query is retrieving one column only (as this question is) then there may be no difference.  I wanted the questioner to be aware though that if he/she tries to select multiple columns with "distinct", the results may be surprising and disappointing.  I find the results of "group by" to always be what I expect them to be.
0
 
andrewstCommented:
Sorry, I misinterpreted you!
0
 
benpungAuthor Commented:
Thanks everyone for your comments.  They were all helpful.
0
 
schubachCommented:
Could you give an example of when "distinct" doesn't return the results that you would expect with multiple columns?  I often use "distinct" with long queries selecting multiple columns, because it forces uniqueness and also provides ordering, without re-listing the columns in an ORDER BY clause.
0
 
Mark GeerlingsDatabase AdministratorCommented:
No, I don't have any examples of when "distinct" gave me different result sets than I expected, mainly because I probably haven't used "distinct" in a multi-column query for some years.  As long as you expect the "distinct" operator to be applied to every column being selected, and not just to the first column, the results you get should match your expectations.  Just remember though that "distinct" will force a sort operation, and in some queries that may not be needed and may add a significant performance penalty.
0
 
ilaskyCommented:
Distinct means different. Consider this: If in your database, you had shipping methods. Inside the database you have "UPS", "FEDEX", "UPS". If you used distinct in your sql query as stated above, you would get 2 for ups and 1 for fedex. This operator counts different entries in that column and reports to you how many of each category there are. Unique does the same thing. The only operator that I can think of that would give you a different output would be count. Count would just count the items under the column without regard for differences.
0
 
aspire-engCommented:
far away from the dfiference between Distinct and unique, i just want to know is there any way to use Distinct and sorting the order according to another collumn which has been picked by default,
You know guys that we cannot use Distinct a way explained below:

SELECT DISTINCT CONTENT_ID FROM CONTENT_FEATURE
WHERE OBSOLETE_FLAG = 'N'

ORDER BY FEATURE_DATE.

and it is not helpful at all to put the feature_date in the select because this will mislead ...

Just a thought and same i have faced when i used Group_by, it brings down the pefromace as well as it doesn't give us the result we want particularly if we want tp pick the top 10 records orederd by date ... ..!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now