SQL DISTINCT vs GROUP BY: When They're the Same (and When They're Not)

Without an aggregate function, DISTINCT and GROUP BY can produce identical results — which is exactly why people confuse them. The moment an aggregate enters the picture, only one of them can do the job. Here's the actual boundary between the two.

Two tools, one overlapping use case

DISTINCT removes duplicate rows from a result set. GROUP BY collapses rows into groups so aggregate functions (COUNT, SUM, AVG...) can summarise each group. When there's no aggregate involved, they can produce the exact same output — which is where the confusion starts.

When they produce identical results

Both of these return every distinct department_id that appears in the table — same rows, same order-independent result.

SELECT DISTINCT department_id FROM employees;

SELECT department_id FROM employees GROUP BY department_id;

Why they overlap here

With no aggregate function in the SELECT list, GROUP BY department_id just forms one group per distinct department_id value and returns one row per group — which is exactly what DISTINCT does directly. On this specific query shape, most optimizers produce the same or a very similar execution plan for both versions.

Where they stop being equivalent

The overlap only holds when there's no aggregate function. The moment you need a per-group summary value — a count, a total, an average — DISTINCT can't do the job at all. It operates on the final row set; it has no concept of "per-group" calculation.

GROUP BY does something DISTINCT structurally can't

Count employees per department — this requires GROUP BY; there's no DISTINCT equivalent.

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;

-- SELECT DISTINCT department_id, COUNT(*) FROM employees;  -- not the same thing —
-- without GROUP BY, COUNT(*) here would count ALL rows, and DISTINCT would
-- then just remove duplicate (department_id, total_count) pairs, which
-- isn't a per-department count at all

How each handles multiple columns

SELECT DISTINCT department_id, salary FROM employees does not give you "distinct departments with a representative salary" — it gives you every distinct pairing of the two columns, which is rarely what people expect on first read.

	DISTINCT	GROUP BY
Dedups based on	every selected column, together	the columns listed in GROUP BY
Can mix a deduped column with a per-row varying value?	no — that would defeat deduplication	yes, if that value is wrapped in an aggregate
SELECT DISTINCT department_id, salary	keeps a row for every unique (department_id, salary) pair	n/a — salary would need an aggregate or its own GROUP BY entry

Is one faster?

For the no-aggregate, single-column case where they're equivalent, performance is typically similar — both usually require a sort or hash operation to identify duplicates, and modern optimizers often choose comparable plans for either. The real efficiency question is whether you're using the right tool for the job at all: reaching for DISTINCT when you actually need a per-group aggregate produces wrong results, not just a slower query, and reaching for GROUP BY when you just need unique values adds unnecessary conceptual overhead without changing the outcome. Always check an execution plan if performance genuinely matters for a specific query.

Frequently asked questions

Can I use DISTINCT and GROUP BY together?

You can write DISTINCT inside an aggregate function alongside GROUP BY — for example COUNT(DISTINCT job_id) — to count unique values within each group. Using DISTINCT on the whole SELECT list at the same time as GROUP BY is redundant, since GROUP BY already produces one row per group.

Does SELECT DISTINCT * remove fewer duplicates than expected?

DISTINCT considers every selected column together, so SELECT DISTINCT * only removes rows that are identical across every single column — including primary keys. If the table has a primary key, DISTINCT * effectively does nothing, since no two rows can be fully identical.

Why did GROUP BY department_id, job_id return more rows than I expected?

GROUP BY with multiple columns creates a separate group for every unique combination of those columns — not one group per department. If you want department-level totals regardless of job_id, remove job_id from the GROUP BY list (and make sure it isn't in the SELECT list unaggregated).

Is DISTINCT ever required alongside an aggregate?

Yes — DISTINCT works inside an aggregate's parentheses to count or sum only unique values: COUNT(DISTINCT department_id) tells you how many different departments appear, as opposed to COUNT(department_id), which counts every non-NULL row.