Interview Question of the Week #034 – What is the Difference Between Distinct and Group By

Here is Interview Question of the Week #034: what is the difference between DISTINCT and GROUP BY? Both can hand you a list of unique rows, so it is tempting to say they are the same thing. They are close cousins, not twins, and explaining that small gap is what makes a good answer.

The short answer I would give

Use DISTINCT when your only goal is to throw away duplicate rows. Use GROUP BY when you want to do some math on each group, such as counting rows, adding up an amount or finding the highest value. That is the heart of it. Everything else is detail you can add if the interviewer keeps asking.

Speed is usually not the deciding factor. For simple queries like the ones below, SQL Server normally builds the same execution plan for both, so neither one wins. Once the query gets more involved, for example with subqueries, the plans can start to differ. In that case do not guess. Turn on the actual execution plan in SQL Server Management Studio, run both versions and compare them.

First, the DISTINCT version. It returns each combination of Employee and Rank only once.

SELECT DISTINCT Employee, Rank
FROM Employees

The GROUP BY version below gives back the very same rows. There is no aggregate in it, so GROUP BY is simply doing the job DISTINCT already does.

SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

Now the query where GROUP BY earns its place. Adding COUNT(*) tells you how many rows fall into each Employee and Rank pair, and that is something DISTINCT cannot do on its own.

SELECT Employee, Rank, COUNT(*) EmployeeCount
FROM Employees
GROUP BY Employee, Rank

A few extra points for the follow up questions

• DISTINCT looks at the whole select list. A row is dropped only when every column in it matches another row.
• With GROUP BY, each column in the select list must either appear in the GROUP BY clause or sit inside an aggregate function such as COUNT or SUM.
• GROUP BY pairs with HAVING, so you can filter the groups after they are built, for example to keep only the pairs that appear more than once.
• Both treat NULLs as duplicates of each other, so they collapse into one row or one group.
• Neither one promises any order. If you want the result sorted, add ORDER BY.

If you remember one line, make it this: DISTINCT removes duplicates, GROUP BY summarizes groups, and the execution plan settles any argument about speed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.