Search code examples
sqlgroup-bydistinct

Is there any difference between GROUP BY and DISTINCT?

The following two queries each give the same result:

SELECT column FROM table GROUP BY column
SELECT DISTINCT column FROM table

Is there anything different in the way these commands are processed, or are they the same thing?

(This is not a question about aggregates. The use of GROUP BY with aggregate functions is understood.)

Solution

  • MusiGenesis' response is functionally the correct one with regard to your question as stated; the SQL Server is smart enough to realize that if you are using "Group By" and not using any aggregate functions, then what you actually mean is "Distinct" - and therefore it generates an execution plan as if you'd simply used "Distinct."

    However, I think it's important to note Hank's response as well - cavalier treatment of "Group By" and "Distinct" could lead to some pernicious gotchas down the line if you're not careful. It's not entirely correct to say that this is "not a question about aggregates" because you're asking about the functional difference between two SQL query keywords, one of which is meant to be used with aggregates and one of which is not.

    A hammer can work to drive in a screw sometimes, but if you've got a screwdriver handy, why bother?

    (for the purposes of this analogy, Hammer : Screwdriver :: GroupBy : Distinct and screw => get list of unique values in a table column)