SQL JOIN: как соединить две таблицы без потери или удвоения строк

1. Что на самом деле делает соединение с вашими строками

Перед пояснением: вы объединяете две таблицы и получаете результат с большим количеством строк, чем было в любой из них. Ничего не ошибочно. Что сделала база данных, чтобы создать дополнительные строки из ниоткуда?

Диаграмма Венна — это то место, где большинство людей встречаются с соединениями, и это разумное начало. Это просто описывает неправильную вещь. Он показывает, какой Ключи выживать. Вам нужно предсказать, сколько Строки выходите, и это разные вопросы.

Вот правило, которое отвечает обоим. Соединение проходит левую таблицу и для каждой строки находит каждую строку справа, ключ которой совпадает. Затем он записывает одну выходную строку для каждой пары. Это единственное предложение объясняет все, что есть на этой странице.

• Левая строка, которая находит Нет спичка производит ноль выходные строки. Оно исчезает.
• Левая строка, которая находит три спички производят три выходные строки. Он дублируется, и каждое значение его столбца дублируется вместе с ним.

Таким образом, количество выходных строк не является ни левым, ни правым. Это количество совпадающих пар, которое может быть меньше, чем любая таблица, или больше, чем обе.

Уровень детализации: идея, благодаря которой соединения имеют смысл

тот grain таблицы — это то, что означает одна строка. Скажите это вслух, прежде чем присоединиться к чему-либо.

• games имеет уровень детализации одной строки за игру. 125 855 строк, 125 855 игр.
• recommendations имеет долю в одну строку на отзыв игрока. 41 154 794 строки, а в популярной игре их тысячи.

Объединив эти две, вы получите таблицу, зернистость которой составляет одну строку на обзор, а не одну строку на игру. Столбцы игр по-прежнему присутствуют, но теперь они повторяются один раз для каждого полученного обзора на игру. В тот момент, когда ты забудешь это, SUM в колонке игры перестает означать то, что говорит ее название.

Каждый запрос на этой странице присваивает своим таблицам псевдонимы. FROM hidden_gems AS g означает «для оставшейся части этого запроса вызовите эту таблицу g", так g.Name столбец «Имя» hidden_gems. Двум объединенным таблицам почти всегда нужны эти псевдонимы, поскольку обе могут содержать столбец с одинаковым именем. Псевдонимы SQL и чтение запроса вслух охватывает всю привычку, если она новая.

2. Шесть типов соединений и что сохраняет каждый из них

Перед объяснением: пять из шести типов соединений ниже соответствуют строкам ключа. Один из них вообще ни с чем не совпадает. Как вы думаете, что это такое и для чего оно может быть?

Каждый тип хранит свой набор строк. Правило пары из первого раздела по-прежнему применимо ко всем из них. Все, что они меняют, — это то, что происходит со строками, которые не нашли партнера.

CROSS JOIN — это ответ на вопрос, и его стоит узнать именно из-за несчастного случая. Две таблицы по 1000 строк каждая, перекрестно соединенные, дают миллион строк. Если вы когда-нибудь напишете соединение и забудете ON некоторые базы данных дают именно это, и первым признаком этого является запрос, который никогда не завершается.

Примечание к слову «внешний». LEFT, RIGHT и FULL все являются внешними соединениями, и вы увидите, что они записаны как LEFT OUTER JOIN. Слово OUTER является необязательным и означает то же самое. Аналогично, равнина JOIN без слова перед ним - это INNER JOIN, о чем стоит помнить, поскольку именно это значение по умолчанию вызывает первый сбой ниже.

Постоянно появляются еще два фрагмента синтаксиса. ON устанавливает условие соответствия, как в ON g.AppID = r.app_id. USING (app_id) это сокращение, обозначающее, что столбец имеет одно и то же имя в обеих таблицах. А когда условие соединения не является простым равенством, например, сопоставление даты с диапазоном, это неэквивалентное соединение. Он работает таким же образом и с энтузиазмом расходится веерами, так что считайте свои ряды.

3. Ошибка первая: тихое удаление

Перед пояснением: вы объединяете две таблицы и получаете обратно меньше строк, чем начали. Ни ошибки, ни предупреждения. Куда делись недостающие строки и кто решил, что они должны исчезнуть?

Равнина JOIN это INNER JOIN. Он сохраняет только строки, совпадающие с обеих сторон. Строки, не совпавшие ни с чем, не помечаются, не учитываются и не упоминаются. Они просто отсутствуют.

Для моих двух наборов данных это имело огромное значение. Две таблицы были собраны разными людьми в разное время, поэтому их охват не совпадает. Проверка того, сколько игр вообще имели соответствующий обзор:

SELECT COUNT(DISTINCT g.AppID) AS games_with_reviews
FROM games_raw AS g
JOIN recommendations AS r ON g.AppID = r.app_id;

Ответ — 36 255 из 125 855 игр, это около 29%. Примерно семь из десяти игр в левой таблице исчезнут из любого внутреннего соединения с таблицей обзора. Если ограничиться 175 играми, которые меня действительно интересовали, около 57 из них не имели обзоров. Внутреннее соединение незаметно уменьшило мою популяцию на треть, прежде чем я что-либо рассчитал, и набор результатов выглядел законченным. Столбцы были правильными, порядок был правильным, цифры были правдоподобными.

Проверка, которая ловит это

Две строки: запускайте, прежде чем доверять чему-либо нижестоящему. Во-первых, сколько строк вошло. Во-вторых, сколько разных ключей получилось.

SELECT COUNT(*) AS rows_in FROM hidden_gems;

SELECT COUNT(DISTINCT g.AppID) AS keys_out
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id;

если keys_out меньше, чем rows_in, ваше присоединение теряет записи, и теперь вам нужно принять решение, а не случайно узнать об этом позже. Либо дроп в порядке и вы об этом письменно говорите, либо нет и вы переходите на дроп. LEFT JOIN.

Запишите коэффициент совпадения в любом случае. «Анализ игрового времени охватывает 118 из 175 игр, или 67%, поскольку набор данных обзора не включает остальные» — это предложение, которое заставляет рецензента доверять всему документу. Если оставить это в стороне, они перестанут ему доверять.

Анти-соединение и почему пустой столбец — это находка

A LEFT JOIN сохраняет каждую строку из левой таблицы независимо от того, совпала она или нет, заполняя правую часть NULL. Это дает вам возможность задать вопрос, на который не может ответить внутреннее соединение: какие строки ничего не нашли? Сохраните все, а затем оставьте только те, правая сторона которых оказалась пустой. Этот шаблон называется запрет на присоединениеи это постоянно проявляется в интервью.

SELECT g.Name, g.pct_positive
FROM hidden_gems AS g
LEFT JOIN recommendations AS r ON g.AppID = r.app_id
WHERE r.app_id IS NULL
ORDER BY g.pct_positive DESC;

Прочтите WHERE внимательно, потому что это та часть, которая удивляет людей. r.app_id IS NULL не может быть истинным для любой совпавшей строки, поскольку совпавшая строка имеет там реальное значение. Это справедливо только для строк LEFT JOIN изобретен, чтобы сохранить непревзойденный левый ряд. Таким образом, фильтр означает «сохранить строки, которые ничего не нашли».

Когда я запустил его, 57 пропущенных игр не были шумом. Они разделились на три группы, и каждая группа что-то мне рассказала. Некоторые из них были массовыми блокбастерами, которые при более раннем этапе проверки качества данных уже были отмечены как невозможные цена и стоимость владения, а совершенно отдельный метод, проверяющий одни и те же строки, является убедительным подтверждением того, что эти строки повреждены. Другие были выпущены после того, как обзоры были собраны в августе 2024 года, поэтому они не могли совпадать, что превратило расплывчатое ограничение в список, который можно назвать. Остальные были действительно обнаруженными названиями, которые никогда не использовались в наборе обзорных данных.

Ничего из этого не существовало бы во внутреннем соединении. Пустой столбец – это не дыра в вашей работе. Часто это самый интересный результат, который у вас есть, и он должен быть в вашей рецензии: Документирование ограничений данных рассказывает о том, как.

4. Ошибка вторая: молчаливое умножение

Перед пояснением: у одного покупателя три заказа. Вы присоединяете клиентов к заказам, а затем суммируете столбец, который относится к клиенту, например, его кредитный лимит. Какая цифра выходит, и почему это не кредитный лимит?

Когда к таблице обзоров присоединяется игра с 2498 отзывами, в этой игре становится 2498 строк. Его название повторяется 2498 раз. Его цена повторяется 2498 раз. Это называется Разветвление, и проблемы начинаются в тот момент, когда вы объединяете столбец, полученный со стороны одной строки на игру.

Вот неправильный запрос. Это выглядит вполне разумно.

SELECT g.Name, SUM(g.Price) AS total_price
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name;

По моим данным, для одной игры по цене 3,49 доллара США с 2498 совпадающими отзывами это SUM возвращает 8718,02. Цена игры по-прежнему составляет $3,49. Запрос добавлял ее один раз за проверку, поскольку после объединения имеется 2498 строк, содержащих эту цену. Никаких ошибок, никаких предупреждений, а в столбце «Общая цена» находится число примерно в 2500 раз больше.

Средние значения скрывают это лучше, чем суммы, что делает их более опасными. AVG(g.Price) по 2498 повторяющимся строкам возвращается 3,49 доллара, правильный ответ, поэтому все выглядит не так. Затем тот же образец встречается с игрой с двумя строками во второй объединенной таблице. Дублирование перестает быть равномерным, и среднее значение незаметно начинает придавать некоторым записям больший вес, чем другим. Прежде чем читать дальше, скажите вслух, почему неравномерное дублирование нарушает средний показатель, а даже дублирование — нет. Это основа всего руководства.

Проверка, которая ловит это

Сравните общее количество строк с отдельными ключами. Если они различаются, у вас есть разветвление, и вам следует знать его размер, прежде чем агрегировать.

SELECT COUNT(*) AS joined_rows,
       COUNT(DISTINCT g.AppID) AS distinct_games
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id;

если joined_rows намного больше, чем distinct_games, объединение расширило вашу таблицу, и это совершенно нормально, если вы это имели в виду. Разветвление не является ошибкой. Для этого и нужно соединение «один-ко-многим». Это становится ошибкой только тогда, когда вы суммируете, как будто этого не произошло.

Другая половина исправления HAVING. После группировки фильтр по агрегату должен выполняться после группировки, что и является HAVING делает и WHERE не могу.

SELECT g.Name,
       g.Price,
       COUNT(r.review_id) AS num_reviews,
       ROUND(AVG(r.hours), 1) AS avg_hours
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name, g.Price
HAVING COUNT(r.review_id) >= 50
ORDER BY avg_hours DESC;

Этот 50-й этаж значит больше, чем кажется. Без этого игра с тремя обзорами и одним навязчивым игроком превосходит все остальное. Благодаря этому на вершине моего списка оказались игры, у которых сотни или тысячи обзоров отстают от среднего показателя, что и отличает находку от артефакта. Подробнее о том, как честно выбрать это число, читайте в Пороги, управляемые данными.

5. Полное до и после

Те же две таблицы, тот же вопрос. Первая версия работает и лежит. Второй работает, говорит правду и излагает собственное освещение.

До

SELECT g.Name,
       SUM(g.Price) AS total_price,
       AVG(r.hours) AS avg_hours
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name
ORDER BY avg_hours DESC;

Три проблемы, ни одна из которых не приводит к ошибке. SUM(g.Price) умножается на количество отзывов. Внутреннее объединение удалило все игры без обзоров, не говоря уже о них. А без минимального количества обзоров игра с двумя отзывами может возглавить рейтинг.

после

-- ============================================================
-- STEP 6: Which games do players actually keep playing?
-- WHY: A high rating says people liked it once. Average hours
--      says they stayed. Joining the game list to real playtime
--      is the only way to tell those apart. The 50-review floor
--      keeps a single obsessive player from topping the chart.
-- COVERAGE: 118 of 175 games matched the review data (67%).
--      The 57 unmatched games are listed separately, not dropped.
-- ============================================================
--SELECT the game name, its price, how many reviews matched,
--   and the average hours those reviewers played:
--   COUNT(r.review_id) = number of matched review rows per game
--   AVG(r.hours)       = mean hours across those reviews
--   ROUND(..., 1)      = round to one decimal place
--FROM the hidden_gems list (g), joined to reviews (r) on the game id
--GROUP BY the game, so each game collapses to one row
--HAVING at least 50 matched reviews, so the average is trustworthy
--ORDER BY average hours, highest first
SELECT g.Name,
       g.Price,
       COUNT(r.review_id) AS num_reviews,
       ROUND(AVG(r.hours), 1) AS avg_hours
FROM hidden_gems AS g
JOIN recommendations AS r ON g.AppID = r.app_id
GROUP BY g.AppID, g.Name, g.Price
HAVING COUNT(r.review_id) >= 50
ORDER BY avg_hours DESC;

Столбец цен сгруппирован, а не суммирован, поэтому он остается равным 3,49 доллара. В строке покрытия указано, что было удалено в результате объединения, чтобы читатель знал масштаб. Нижний этаж не позволяет ранжированию определяться выборкой из двух человек. Формат комментариев тот же, что и у Как комментировать SQL, чтобы он обучал.

Теперь представьте себе последнее объединение, которое вы написали для своих собственных данных. Какая из этих трех проблем, скорее всего, уже присутствует в нем и что вам скажет подсчет строк?

6. Пограничные случаи, нарушающие соединения в реальных данных

Перед объяснением: соединение возвращает ноль строк. Не мало. Ноль. Обе таблицы заполнены, и идентификаторы на экране выглядят одинаково. Что бы вы проверили в первую очередь?

Семь вещей, каждая из которых кого-то остановила на целый день.

Ключевые столбцы называются по-другому. games_raw.AppID и recommendations.app_id — это одно и то же число из одного и того же источника, написанное двумя способами, поскольку наборы данных создавали два человека. Важно то, что они имеют один и тот же идентификатор, а не то, что у них общее имя.

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

Логическое значение сохраняется в виде текста. В обзорной таблице is_recommended содержит текст в нижнем регистре 'true' и 'false', а не 1 и 0. Фильтрация включена is_recommended = 1 ничего не соответствует и возвращает нулевое значение, что воспринимается как реальный ответ.

NULL в ключе соединения. A NULL ключ никогда не соответствует ничему, включая другой NULL, потому что NULL означает неизвестное, а не пустое. Строки с отсутствующим ключом выпадают из внутреннего соединения без слов. Сначала посчитайте их с помощью SELECT COUNT(*) FROM t WHERE key_column IS NULL.

Объединение многих со многими. Если обе стороны содержат несколько строк на клавишу, выходные данные умножаются в обе стороны: три строки слева и четыре справа дают двенадцать. Это почти никогда не то, чего кто-либо хочет, и это классическая причина завышения итоговой суммы. Сначала агрегируйте одну сторону вниз до одной строки на ключ, а затем присоединяйтесь.

Соединение происходит медленно, потому что одна сторона огромна. Сопоставление 125 855 строк с 41 миллионом требует серьезной работы. Индекс ключевого столбца большой таблицы превращает полное сканирование в поиск: CREATE INDEX idx_rec_appid ON recommendations(app_id);. Создайте его один раз, и каждое последующее соединение будет быстрым. Обработка больших наборов данных охватывает остальную часть работы в этом размере.

Две таблицы описывают разные моменты. Мой список игр и список обзоров были собраны в разное время, поэтому некоторые несовпадающие строки являются не ошибками данных, а календарем. Объединение упрощает объединение источников, которые никогда не предназначались для объединения, и именно поэтому в описании должно быть указано, откуда взялся каждый источник.