sqloracledistinct

Should I use distinct in my queries


Where I am working I have been recently told that using distinct in your queries is a bad sign of a programmer. So I am wondering I guess the only way to not use this function is to use a group by .

It was my understanding that the distinct function works very similarly to a group by except in how its read. A distinct function checks each individual selection criteria vs a group by which does the same thing only done as a whole.

Keep in mind I only do reporting . I do not create/alter the data. So my question is for best practices should I be using distinct or group by. If neither then is there an alternative. Maybe the group by should be used in more complex queries than my non-real example here, but you get the idea. I could not find an answer that really explained why or why not I should use distinct in my queries

select distinct
    spriden_user_id as "ID",
    spriden_last_name as "last",
    spriden_first_name as "first",
    spriden_mi_name as "MI",
    spraddr_street_line1 as "Street",
    spraddr_street_line2 as "Street2",
    spraddr_city as "city",
    spraddr_stat_code as "State",
    spraddr_zip as "zip"
from spriden, spraddr
where spriden_user_id = spraddr_id
and spraddr_mail_type = 'MA'

VS

select
    spriden_user_id as "ID",
    spriden_last_name as "last",
    spriden_first_name as "first",
    spriden_mi_name as "MI",
    spraddr_street_line1 as "Street",
    spraddr_street_line2 as "Street2",
    spraddr_city as "city",
    spraddr_stat_code as "State",
    spraddr_zip as "zip"
from spriden, spraddr
where spriden_user_id = spraddr_id
and spraddr_mail_type = 'MA'
group by "ID","last","first","MI","Street","Street2","city","State","zip"     

Solution

  • Databases are smart to recognize what you mean. I expect both of your queries to perform equally well. It is important for someone else maintaining your query to know what you meant. If you really meant to retrieve distinct records, use DISTINCT. If your intention was to do aggregation, use GROUP BY

    Take a look at this question. There are some nice answers that might help.