Is there any difference between GROUP BY and DISTINCT

sql group-by distinct
Brettski picture Brettski · Oct 2, 2008 · Viewed 243.1k times · Source

I learned something simple about SQL the other day:

SELECT c FROM myTbl GROUP BY C

Has the same result as:

SELECT DISTINCT C FROM myTbl

What I am curious of, is there anything different in the way an SQL engine processes the command, or are they truly the same thing?

I personally prefer the distinct syntax, but I am sure it's more out of habit than anything else.

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

Answer

Skeolan picture Skeolan · Oct 2, 2008

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)

Related questions

What's faster, SELECT DISTINCT or GROUP BY in MySQL?

If I have a table CREATE TABLE users ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, profession varchar(255) NOT NULL, employer varchar(255) NOT NULL, PRIMARY KEY (id) ) and I want to get all unique values of profession …

Read More
SQL: Group by minimum value in one field while selecting distinct rows

Here's what I'm trying to do. Let's say I have this table t: key_id | id | record_date | other_cols 1 | 18 | 2011-04-03 | x 2 | 18 | 2012-05-19 | y 3 | 18 | 2012-08-09 | z 4 | 19 | 2009-06-01 | a 5 | 19 | 2011-04-03 | b 6 | 19 | 2011-10-25 | c 7 | 19 | 2012-08-09 | …

Read More
Huge performance difference when using group by vs distinct

I am performing some tests on a HSQLDB server with a table containing 500 000 entries. The table has no indices. There are 5000 distinct business keys. I need a list of them. Naturally I started with a DISTINCT query: SELECT DISTINCT business_…

Read More