Объединение данных по одному за раз

Спасибо Джереми Чоу за умный субтитр!

Код, который я использовал для этого блога, можно найти на моем GitHub.

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

В этом блоге мы рассмотрим четыре различных типа объединений:

  • INNER JOIN (или JOIN)
  • LEFT JOIN (или LEFT OUTER JOIN)
  • RIGHT JOIN (или RIGHT OUTER JOIN)
  • ПОЛНОЕ СОЕДИНЕНИЕ

Особое примечание:. Не все программы SQL, с которыми вы сталкиваетесь, будут иметь ПРАВИЛЬНЫЕ или ПОЛНЫЕ СОЕДИНЕНИЯ, поэтому я добавлю обходные пути для каждой из них в соответствующих разделах.

Настройка

Для этих примеров мы будем использовать старую добрую базу данных Chinook, которая также используется в блоге об основах SQL, а также в большинстве других блогов, связанных с SQL, которые у меня есть.

Давайте настроим файл базы данных, библиотеки и функции:

База данных загружена, и теперь мы готовы работать с объединениями!

ВНУТРЕННЕЕ СОЕДИНЕНИЕ / СОЕДИНЕНИЕ

Ключевое слово INNER JOIN выбирает все строки из обеих таблиц, если выполняются условия. Это ключевое слово создает новый набор, объединяющий все строки из обеих таблиц, в которых значения общие поля такие же.

Давайте рассмотрим пример и посмотрим, как это работает:

SELECT a.name, a.composer, b.name
FROM tracks AS a
INNER JOIN genres AS b
ON a.genreid = b.genreid
WHERE b.name LIKE 'b%'
LIMIT 10;

После установки tracks таблицы на имя переменной a и genres таблицы на имя переменной b, эти две таблицы связаны общим ключом, который является ключом genreid, который существует в обеих таблицах. Затем он находит название песни и композитора из таблицы tracks, а затем находит соответствующее название жанра из таблицы genres.

Затем он ограничивает результаты только теми, название песни которых начинается с буквы «b», и дополнительно ограничивает результаты первыми 10. Результатом будет 10 песен в жанре блюз с названием песни и именем композитора.

Теперь, когда мы можем немного лучше объяснить, что происходит с ключевым словом INNER JOIN, давайте перейдем к ключевому слову LEFT JOIN!

ЛЕВОЕ СОЕДИНЕНИЕ / ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Ключевое слово LEFT JOIN возвращает все строки таблицы с левой стороны соединения и соответствующие строки для таблицы с правой стороны соединения. Если есть строки с левой стороны без соответствующих строк с правой стороны, значение null заполнит пространство для правой стороны.

SELECT a.trackid, a.name, a.composer, b.invoicelineid, b.invoiceid
FROM tracks AS a
LEFT JOIN invoice_items AS b
ON a.trackid = b.trackid;

В приведенном выше примере запрос устанавливает для таблицы tracks имя переменной a, а для таблицы invoice_items - имя переменной b. Он связывает их с помощью общего ключа trackid и возвращает идентификатор трека, имя и композитор из таблицы треков с идентификатором строки счета-фактуры и идентификатором счета-фактуры из таблицы invoice_items.

Глядя на данные, вы можете подумать: «Куда делись нули?». А если нет, мы все равно покажем, как их найти!

SELECT a.trackid, a.name, a.composer, b.invoicelineid, b.invoiceid
FROM tracks AS a
LEFT JOIN invoice_items AS b
ON a.trackid = b.trackid
WHERE b.invoiceid IS NULL;

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

ПРАВОЕ СОЕДИНЕНИЕ / ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Как и следовало ожидать, RIGHT JOIN похож на LEFT JOIN. Это соединение возвращает все строки таблицы с правой стороны соединения и соответствующие строки для таблицы с левой стороны соединения. И для любых строк, в которых теперь есть совпадающие строки на в левой части эти значения заменяются на null.

В большинстве запросов RIGHT JOIN будет выглядеть так:

SELECT a.trackid, a.name, a.composer, b.invoicelineid, b.invoiceid
FROM tracks AS a
RIGHT JOIN invoice_items AS b
ON a.trackid = b.trackid;

Обратите внимание, что эта настройка выглядит почти так же, как пример в примере LEFT JOIN, за исключением того, что теперь вместо этого указано RIGHT JOIN.

Теперь для тех из вас, кто использует программы SQL, которые не поддерживают RIGHT JOIN , ключевое слово RIGHT JOIN по сути является перевернутым LEFT JOIN. Вот как это выглядит с обходным решением:

SELECT b.trackid, b.name, b.composer, a.invoicelineid, a.invoiceid
FROM invoice_items AS a
LEFT JOIN tracks AS b
ON a.trackid = b.trackid;

Оно немного изменено по сравнению с ключевым словом RIGHT JOIN, но в результате будет получено то же самое, что и в примере RIGHT JOIN.

Результат этого запроса покажет счета-фактуры с фактически заказанными песнями. Песни, не заказанные ни в одном из счетов, не будут отображаться в этом запросе.

Теперь, когда у нас есть для них примеры, давайте перейдем к FULL JOIN!

ПОЛНОЕ СОЕДИНЕНИЕ / ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Ключевое слово FULL JOIN создает результат путем объединения результатов как LEFT JOIN , так и RIGHT JOIN. Для любых несовпадающих строк результат будет содержать нулевые значения.

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

Если ваша программа SQL поддерживает это, ваш запрос будет выглядеть очень просто:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

Полезно знать, что FULL JOIN - это, по сути, LEFT JOIN в сочетании с RIGHT JOIN. Если предположить, что наша программа SQL не поддерживает ни FULL JOIN, ни RIGHT JOIN, наш запрос будет выглядеть так:

SELECT * 
FROM invoice_items AS a
LEFT JOIN tracks AS b
ON a.trackid = b.trackid
UNION
SELECT *
FROM tracks AS a
LEFT JOIN invoice_items AS b
ON a.trackid =b.trackid
WHERE a.trackid IS NULL;

В этом примере происходят три основных события:

  1. Первый LEFT JOIN
  2. Второй LEFT JOIN (псевдо-RIGHT JOIN)
  3. UNION, который объединяет два вместе

Строка IS NULL предотвращает попадание повторяющихся строк в набор результатов и очень полезна для получения удобочитаемых результатов.

В результате будет выведено 2240 строк данных, в которых данные счета будут объединены с данными отдельных песен.

Заключение

Уф! Было много вопросов, и я ценю, что вы так долго торчали!

Вкратце, вот как действует каждое ключевое слово JOIN:

  • INNER JOIN: выбирает все строки из обеих таблиц, которые имеют совпадающий общий ключ.
  • LEFT JOIN: использует все строки таблицы с левой стороны и находит совпадающие строки из таблицы с правой стороны.
  • RIGHT JOIN: использует все строки таблицы с правой стороны и находит совпадающие строки из таблицы с левой стороны.
  • ПОЛНОЕ СОЕДИНЕНИЕ: объединяет все строки из обеих таблиц.

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