DISTINCT и уникальность строк

DISTINCT выглядит самым безобидным словом в SQL: убери повторы, и всё. На собеседовании его спрашивают ради трёх тонкостей: что считается повтором, как DISTINCT обращается с NULL и чем он отличается от GROUP BY. В прошлом уроке группировка уже схлопывала строки, теперь сравним её с DISTINCT. Примеры на учебной базе блока.

Как спрашивают

Что делает DISTINCT? Он убирает повторы по первой колонке или по всей строке? Чем SELECT DISTINCT city отличается от SELECT city ... GROUP BY city?

Короткий ответ

DISTINCT убирает строки, которые совпадают по всем колонкам списка SELECT, а не по первой. NULL при этом считается равным NULL, поэтому пустой город останется одной строкой. Для простого списка уникальных значений DISTINCT и GROUP BY дают одно и то же, но GROUP BY нужен, когда к уникальным значениям надо приложить агрегат.

Разбор: стопка визиток

Представь, что после конференции у тебя стопка визиток, и многие люди дали их по два раза. Ты раскладываешь их по столу и убираешь копии. Копией считается визитка, где совпадает всё: имя, компания, телефон. Если у Ивана две визитки с разными телефонами, это две разные визитки, и обе остаются.

DISTINCT работает так же. Он смотрит на строку результата целиком.

SELECT DISTINCT customer_id, amount
FROM orders;

customer_id | amount
------------+-------
          1 |   3000
          1 |   5000
          2 |   7000
          3 |   1200

У Ольги (клиент 1) три заказа: на 5000 и два по 3000. Пара (1, 3000) встречалась дважды и схлопнулась. Пара (1, 5000) другая, поэтому клиент 1 в результате дважды. Частая ошибка новичка: ждать, что DISTINCT customer_id, amount даст по одной строке на клиента.

Ещё нагляднее: добавь в список id, и DISTINCT перестанет что-либо убирать, потому что id у каждой строки свой.

SELECT DISTINCT id, customer_id, amount
FROM orders
WHERE customer_id = 1;

id | customer_id | amount
---+-------------+-------
 1 |           1 |   5000
 2 |           1 |   3000
 4 |           1 |   3000

NULL в DISTINCT

SELECT DISTINCT city FROM customers;

city
------
Казань
Пермь
NULL

Город Светы неизвестен, и он попал в результат одной строкой. В уроке про трёхзначную логику сравнение NULL = NULL давало «неизвестно». Но DISTINCT и GROUP BY живут по своему правилу: при поиске повторов все NULL считаются одинаковыми.

А теперь посчитаем города:

SELECT count(DISTINCT city) AS cities, count(*) AS rows
FROM customers;

cities | rows
-------+-----
     2 |    4

Тут NULL уже не учитывается: агрегатные функции пропускают NULL. Итого SELECT DISTINCT city даёт три строки, а count(DISTINCT city) даёт два. Это любимая ловушка интервьюеров, подробнее про подсчёты в уроке про варианты COUNT.