Hacker News Reader

Comment by 6cxs2hd6 | Hacker News Reader

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.

View on HN

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.

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?

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.

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.

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).

How is that imperative in the slightest?

Reply on news.ycombinator.com