Why is GROUP BY faster than DISTINCT in SQL?

Question

This article explains that GROUP BY is faster than DISTINCT https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct

Why doesn't the implementation of DISTINCT just use the same underlying logic if it's faster?

question from:https://stackoverflow.com/questions/65838429/why-is-group-by-faster-than-distinct-in-sql

Answer 1

A very key part of this article – easily overlooked – is the reference to "execution plans." As presented by the EXPLAIN *query* verb.

Every time you present an SQL query to the database engine, it "compiles it" into an "execution plan," which is what the underlying logic actually "executes" to give you the results that you want. The formulation of this plan is entirely "case by case," and it can be based on quite a number of things, including table sizes and "statistics" which the engine is constantly accumulating about the data which each table contains.

(The EXPLAIN verb will present this plan-information to you, for any query. The format, content, and purpose of it is entirely engine-dependent, but the principle is not.)

Therefore, the "take-away" that I suggest that you take from this article is not that GROUP BY is [always ...] "faster than DISTINCT," but rather, how the engine goes about its business such that [sometimes] this is the case. It goes without saying that "every engine is different," but the principles governing all of them, by now, are more-or-less the same.

Whether or not "this actually turns out to be true, for you, with your data, in your case," is less important than your understanding of what actually happens behind-the-scenes when you submit any SQL query to a database engine. There's much more to it than meets the eye ...