Solved

SQL UNIQUE and DISTINCT

Posted on 2004-10-14
12
512,087 Views
Last Modified: 2012-05-05
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
Comment
Question by:benpung
  • 3
  • 2
  • 2
  • +5
12 Comments
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 50 total points
ID: 12311173
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12311235
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
 
LVL 4

Expert Comment

by:pankajtiwary
ID: 12315786
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12315874
No, please don't mislead.

DISTINCT is ANSI standard,

UNIQUE is Product specific...
0
 
LVL 15

Expert Comment

by:andrewst
ID: 12317406
@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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 12320147
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:andrewst
ID: 12320246
Sorry, I misinterpreted you!
0
 
LVL 1

Author Comment

by:benpung
ID: 12337732
Thanks everyone for your comments.  They were all helpful.
0
 
LVL 4

Expert Comment

by:schubach
ID: 13832101
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 13837240
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
 

Expert Comment

by:ilasky
ID: 23247795
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
 

Expert Comment

by:aspire-eng
ID: 31102931
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now