DISTINCT vs GROUP BY
IntermediateDISTINCT and GROUP BY often produce identical results, but they diverge on ORDER BY interaction, HAVING clauses, and when aggregate functions are involved.
Overview
For simple deduplication with no aggregate functions, most query planners treat SELECT DISTINCT and GROUP BY as equivalent and produce the same execution plan. Differences emerge in practice: GROUP BY is required when you want to filter groups with HAVING, GROUP BY communicates intent more clearly, and DISTINCT interacts differently with ORDER BY in some databases. Index usage also differs — GROUP BY can leverage a sorted index to avoid a hash aggregate, while DISTINCT may trigger a separate sort step. Understanding when the optimizer treats them the same versus when they diverge prevents subtle correctness bugs and performance surprises.
Equivalent Cases and When the Optimizer Agrees
For simple column deduplication with no aggregates, GROUP BY and DISTINCT compile to the same plan in PostgreSQL and MySQL. Always prefer GROUP BY when you anticipate adding aggregates later.
-- Equivalent: list distinct cities where we have users
SELECT DISTINCT city FROM users;
-- Same plan as:
SELECT city FROM users GROUP BY city;
-- EXPLAIN (PostgreSQL) — both produce:
-- HashAggregate (cost=310.00..320.00 rows=1000)
-- Group Key: city
-- -> Seq Scan on users
-- Rule of thumb: if you need only deduplication, DISTINCT is fine.
-- If you need aggregates, counts, or HAVING, use GROUP BY.Where They Diverge: HAVING, Aggregates, ORDER BY
HAVING is only valid with GROUP BY. Mixing DISTINCT with aggregate functions produces unexpected results. ORDER BY on a non-selected column works with GROUP BY but is restricted with DISTINCT.
-- HAVING requires GROUP BY — cannot use with DISTINCT
-- Anti-pattern (syntax error in most DBs):
SELECT DISTINCT city FROM users HAVING COUNT(*) > 5; -- ERROR
-- Correct: GROUP BY + HAVING
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 5;
-- DISTINCT with aggregate: only one value returned per row (often confusing)
-- How many distinct products were ordered per user?
-- Wrong: DISTINCT + COUNT mixes dedup levels
SELECT DISTINCT user_id, COUNT(product_id) FROM orders GROUP BY user_id;
-- This is redundant; the GROUP BY already deduplicates user_id.
-- ORDER BY a non-selected column: works with GROUP BY, restricted with DISTINCT
-- PostgreSQL allows this for GROUP BY:
SELECT city FROM users GROUP BY city ORDER BY MAX(created_at) DESC;
-- DISTINCT version must include the ORDER BY column in the SELECT list:
SELECT DISTINCT city FROM users ORDER BY city; -- OK
-- SELECT DISTINCT city FROM users ORDER BY created_at; -- ERROR in PostgreSQLIndex Usage Differences
GROUP BY can exploit a B-tree index for a streaming GroupAggregate (no sort needed). DISTINCT may force a separate Sort node even when an index exists, depending on the planner version.
-- Create index on city for demonstration
CREATE INDEX idx_users_city ON users(city);
-- GROUP BY can use Index Scan + GroupAggregate (streaming, no extra sort):
EXPLAIN SELECT city FROM users GROUP BY city;
-- GroupAggregate
-- -> Index Only Scan using idx_users_city on users
-- DISTINCT may produce the same plan or add a Sort step:
EXPLAIN SELECT DISTINCT city FROM users;
-- HashAggregate (may not use the index as efficiently in all versions)
-- For COUNT(DISTINCT col) on large tables, neither is fast without specialized indexes.
-- Consider a covering index:
CREATE INDEX idx_orders_user_product ON orders(user_id, product_id);
-- Allows index-only scan for COUNT(DISTINCT product_id) per user_id.Key Points to Remember
- 1For pure deduplication with no aggregates, DISTINCT and GROUP BY produce the same query plan in most databases.
- 2HAVING filters groups and is only valid with GROUP BY — you cannot use HAVING with DISTINCT.
- 3ORDER BY a non-selected column is valid after GROUP BY but restricted after DISTINCT in PostgreSQL.
- 4GROUP BY can exploit a sorted B-tree index for a streaming GroupAggregate; DISTINCT may require an extra Sort step.
- 5Prefer GROUP BY over DISTINCT when the query may later grow aggregates or HAVING clauses.
Interview Questions
Sign in to ask AriaWhen do DISTINCT and GROUP BY produce different results? Give a concrete example.
Why can you use HAVING with GROUP BY but not with DISTINCT?
Explain how a B-tree index can help GROUP BY but may not help DISTINCT in the same way.
Which would you choose for a deduplication query in a high-traffic API and why?
Ask Aria about DISTINCT vs GROUP BY
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.