DISTINCT in sql

5 pts.
Tags:
Distinct
what are the features of DISTINCT other than eliminating duplicate values,does it allows NULL values more than once?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I'm sure you can run a test and find that out. You will learn a lot more that way.
    69,045 pointsBadges:
    report
  • Kccrosser
    My $0.02. Avoid DISTINCT wherever possible. IMHO, most uses of DISTINCT are by lazy programmers who fail to understand why they are getting duplicate rows, and they are using the DISTINCT modifier to eliminate the duplicates at the end of the query process instead of much earlier. DISTINCT does allow nulls - you can have as many null columns in the result rows as you like. Simple (but ridiculous) example: Select DISTINCT Null, Null, Null, ..., Null from (anytable) This would return one row with a lot of nulls. The biggest problem (IMHO) with the use of DISTINCT is that it will mask Cartesian products, resulting in what looks like a good result set while thrashing the database. For example, consider the following bad query. Select DISTINCT People.LastName, People.FirstName, Employer.Name, Employer.City from People, Employer where People.Age = 30 and Employer.ZIP in (92121, 92122, 92123); The user was probably expecting to find all 30-year old people who were employed by employers in three zip codes in the City of San Diego. However, since there was no join between People and Employer, the result set is a cartesian product of all people who are 30 and all employers who are in any of those zip codes. Unfortunately, since the three Zip values all connect to "San Diego" as a city, the DISTINCT modifier causes all the duplicate values to be discarded, so you get what looks like a valid result - a bunch of 30-year old persons associated to one City. BUT - if you take off the DISTINCT modifier, suddenly you would see multiple rows - if there were 20 employers in the three zip codes, and there were 50 30-year old people, you would suddenly see 1000 result rows, with the same people/City combinations repeated 20 times. This is what is known as a "hint" that your query is wrong. Avoid DISTINCT until and unless you determine that the query is working perfectly and the duplicates are an acceptable byproduct that cannot be otherwise eliminated.
    3,830 pointsBadges:
    report
  • Kccrosser
    Minor oops in my example above. I meant to remove the "Employer.Name" from the select statement, which then makes the rest of the analysis clearer. With "Employer.Name" in the list, you would likely see obvious duplicates. The rest of my comments are valid, but the example will work better with just the people and the city.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following