5 ms·

But isn't `DISTINCT` sort of shoving an imperative square peg into the round hole of declaritive set relations? At least in my time with MSSQL, seeing `SELECT

But isn't `DISTINCT` sort of shoving an imperative square peg into the round hole of declaritive set relations? At least in my time with MSSQL, seeing `SELECT DISTINCT ...` was a red flag. It nearly always meant something needed to be refactored using GROUP BY.

pdubs 13y ago
SELECT COUNT(DISTINCT ...) is a very different thing. It's counting distinct values of a particular column in a result set and totally not a square peg/round hole situation.
jeffdavis 13y ago
I've never heard DISTINCT described as imperative. It still leaves room to determine how and when to achieve the DISTINCT property, perhaps even doing no work at all. * It can do a sort+unique or a hash, depending on cardinality, availability of memory, and a few other factors. * It can push down a distinct or pull up a distinct, resulting in a faster plan. * If there is another part of the plan that already implies distinct, like a GROUP BY, then it doesn't need to do any work at all. (Or similarly, if you're doing a distinct on a primary key).
schrodinger 13y ago
FYI, in mssql at least, distinct and group by are entirely equivalent to the query planner. I'm not saying you're wrong though, since group by let's you explicitly pick which columns to go by, whereas distinct uses them all.
- alinhan 13y ago
  I don't understand what you mean by "group by let's you explicitly pick which columns to go by, whereas distinct uses them all". Can you give an example?
  - schrodinger 13y ago
    select userid, name from users group by userid, name is equivalent to select distinct userid, name from users It's easy to add another column to the "distinct" based query and change the behavior accidentally. With the "group by" query, you'd get an error saying that the new column needs to be either aggregated or added to the group by.
saosebastiao 13y ago
How is that imperative in the slightest?

pdubs 13y ago

SELECT COUNT(DISTINCT ...) is a very different thing. It's counting distinct values of a particular column in a result set and totally not a square peg/round hole situation.

jeffdavis 13y ago

I've never heard DISTINCT described as imperative. It still leaves room to determine how and when to achieve the DISTINCT property, perhaps even doing no work at all. * It can do a sort+unique or a hash, depending on cardinality, availability of memory, and a few other factors. * It can push down a distinct or pull up a distinct, resulting in a faster plan. * If there is another part of the plan that already implies distinct, like a GROUP BY, then it doesn't need to do any work at all. (Or similarly, if you're doing a distinct on a primary key).

schrodinger 13y ago

FYI, in mssql at least, distinct and group by are entirely equivalent to the query planner. I'm not saying you're wrong though, since group by let's you explicitly pick which columns to go by, whereas distinct uses them all.

alinhan 13y ago
I don't understand what you mean by "group by let's you explicitly pick which columns to go by, whereas distinct uses them all". Can you give an example?
- schrodinger 13y ago
  select userid, name from users group by userid, name is equivalent to select distinct userid, name from users It's easy to add another column to the "distinct" based query and change the behavior accidentally. With the "group by" query, you'd get an error saying that the new column needs to be either aggregated or added to the group by.

saosebastiao 13y ago

How is that imperative in the slightest?