Why would someone use a group by versus distinct when there are no aggregations done in the query?
Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I'm guessing that SQL Server has a better optimizer and they might be close to equivalent there, but in MySQL, I expect a significant performance advantage to distinct.
I'm interested in dba answers.
EDIT:
Bill's post is interesting, but not applicable. Let me be more specific...
select a, b, c
from table x
group by a, b,c
versus
select distinct a,b,c
from table x
Bill Karwin :
GROUP BY maps groups of rows to one row, per distinct value in specific columns, which don't even necessarily have to be in the select-list.\n\nSELECT b, c, d FROM table1 GROUP BY a;\n\n\nThis query is legal SQL (correction: only in MySQL; actually it's not standard SQL and not supported by other brands). MySQL accepts it, and it trusts that you know what you're doing, selecting b, c, and d in an unambiguous way because they're functional dependencies of a. \n\nHowever, Microsoft SQL Server and other brands don't allow this query, because it can't determine the functional dependencies easily. edit: Instead, standard SQL requires you to follow the Single-Value Rule, i.e. every column in the select-list must either be named in the GROUP BY clause or else be an argument to a set function.\n\nWhereas DISTINCT always looks at all columns in the select-list, and only those columns. It's a common misconception that DISTINCT allows you to specify the columns:\n\nSELECT DISTINCT(a), b, c FROM table1;\n\n\nDespite the parentheses making DISTINCT look like function call, it is not. It's a query option and a distinct value in any of the three fields of the select-list will lead to a distinct row in the query result. One of the expressions in this select-list has parentheses around it, but this won't affect the result.",
2009-01-09T02:48:44
Cowan :
A little (VERY little) empirical data from MS SQL Server, on a couple of random tables from our DB.\n\nFor the pattern:\n\nSELECT col1, col2 FROM table GROUP BY col1, col2\n\n\nand\n\nSELECT DISTINCT col1, col2 FROM table \n\n\nWhen there's no covering index for the query, both ways produced the following query plan:\n\n|--Sort(DISTINCT ORDER BY:([table].[col1] ASC, [table].[col2] ASC))\n |--Clustered Index Scan(OBJECT:([db].[dbo].[table].[IX_some_index]))\n\n\nand when there was a covering index, both produced:\n\n|--Stream Aggregate(GROUP BY:([table].[col1], [table].[col2]))\n |--Index Scan(OBJECT:([db].[dbo].[table].[IX_some_index]), ORDERED FORWARD)\n\n\nso from that very small sample SQL Server certainly treats both the same.",
2009-01-09T04:22:40
karl :
In MySQL I've found using a GROUP BY is often better in performance than DISTINCT.\n\nDoing an \"EXPLAIN SELECT DISTINCT\" shows \"Using where; Using temporary \" MySQL will create a temporary table.\n\nvs a \"EXPLAIN SELECT a,b, c from T1, T2 where T2.A=T1.A GROUP BY a\" just shows \"Using where\" ",
2011-06-30T11:13:15
Andre Gallo :
Both would generate the same query plan in MS SQL Server.... If you have MS SQL Server you could just enable the actual execution plan to see which one is better for your needs ...\n\nPlease have a look at those posts:\n\nhttp://blog.sqlauthority.com/2007/03/29/sql-server-difference-between-distinct-and-group-by-distinct-vs-group-by/\n\nhttp://www.sqlmag.com/Article/ArticleID/24282/sql_server_24282.html",
2009-01-09T01:42:08