Is Distinct adding any value or doing anything in this Query?

1

Assuming the Users.Id field is unique auto incremented MY MAIN QUESTION: Is "Distinct", in this query, doing anything, is it completely pointless having it in this specific query?

SELECT  DISTINCT users.id, CONCAT(users.firstname, ' ', users.surname) AS name FROM users 
WHERE users.id!=$me AND (LOWER(users.firstname) LIKE '$term%' AND (email='$email' OR username='$email'))

-

If the query was changed to:

SELECT  DISTINCT users.surname, firstname, surname
FROM users WHERE surname = 'Smith''

Then I might only get Jane Smith or John Smith as my result but not both of them or anyone else with the name Smith, Correct?

(The Top query is the original)

NZ Dev

Posted 2018-03-21T00:08:35.157

Reputation: 113

Yes, that is correct, if you change to use DISTINCT users.surname, firstname, surname then you can be assured that you will only get distinct values for those selected fields. If you have Jane Smith and John Smith, both of those records will still be listed. If you have 2 or more John Smith's though for example, you would only get the one distinct record for John Smith, not both so using DISTINCT can be used for one or more fields ensuring those field records are not duplicate values (i.e. distinct) where all values are the same. https://www.w3schools.com/sql/sql_distinct.asp

– Pimp Juice IT – 2018-03-21T00:49:04.317

So what you are saying is: "all selected fields are used in the distinct". So in the first query the id field would make the distinct useless because every id is unique? and in the second query 2 John Smiths would be returned as one result , but John and Jane would be returned as 2 result? – NZ Dev – 2018-03-21T01:04:29.680

This sounds correct to me but you can easily test this to confirm, correct? I am almost certain that is the same so give it a test run and confirm that, that's how I recall it working the last I used it but you can easily test and confirm with a simple test select query. – Pimp Juice IT – 2018-03-21T01:12:53.350

Answers

0

DISTINCT clause, removes duplicate record, from the output of select query. So, if any of the SELECT columns in your query is UNIQUE, then there is no point to use DISTINCT. It might slow down the performance as db server will try to find out the duplicate records which are not there. In first query, since you are returning user id which is Unique – So even if you remove Unique, the results will not change and you should also see better performance.

In your second query, if two or more users are having same name, you will end up having one result.

RajBedi

Posted 2018-03-21T00:08:35.157

Reputation: 16

Thank you. I chose your answer because I liked your first paragraph, Your final sentence is a little ambiguous, It possibly should say "In your second query, if two or more users have the same FullName, you will end up returning one result."

Thanks again – NZ Dev – 2018-04-04T14:05:46.543

Asked: 2018-03-21T00:08:35.157

Viewed: 78 times

Active: 2018-04-01T17:16:04.477