Browse Teach Curate

A SQL Result Is a Bag of Rows Until You Ask for DISTINCT

SQL does not remove duplicates for you: a query returns every row that matched, including identical ones, which is called bag semantics. DISTINCT turns a bag into a set, and it is not free, because the engine must sort the rows or build a hash table to find the duplicates. A DISTINCT that is there to paper over duplicate rows produced by a join is one of the most common avoidable costs in a slow query.

Questions this Concept answers

Why is `DISTINCT` not free?

jeremy Text

EXPLAIN ANALYZE: The Hidden Cost of DISTINCT

This page explains what really happens when you add DISTINCT to a SQL query. The database has to collect every row the query produces and then remove duplicates, usually by sorting them or building a…