Существует много способов объединения данных из двух таблиц базы данных и фильтрации необходимой информации. Крейг Баклер написал популярную статью о понимании JOIN ; а именно ВНУТРЕННЯЯ, ЛЕВАЯ, ПРАВАЯ И ПОЛНАЯ НАРУЖНАЯ. Эта статья является продолжением этой.
Давайте вспомним это очень быстро. Изобразите две таблицы, одну для customers и одну для books чтобы создать базу данных книжных займов.
таблица customers
| Я бы | Имя | фамилия | book_id |
|---|---|---|---|
| 1 | Джо | Дуть | 1 |
| 2 | Джейн | лань | 2 |
| 3 | Гарри | ворона | 2 |
| 4 | Джеффри | Снег | 0 |
стол с books
| Я бы | заглавие |
|---|---|
| 1 | Звездные войны |
| 2 | парк Юрского периода |
| 3 | Маленькая женщина |
| 4 | Том Сойер |
Таблица books имеет один ряд для каждой книги.
Таблица customers имеет по одной строке для каждого клиента, который может иметь одновременно только одну книгу. Если у них нет книги в аренде, book_id будет 0 или пустая строка.
Это очень простой пример, чтобы сделать СОЕДИНЕНИЯ максимально понятными!
СЛЕДУЮЩЕЕ СОЕДИНЕНИЕ здесь может быть в том случае, если вы хотите задать вопрос, такой как « покажи мне всех клиентов, включая любые книги в кредит. »
На изображении видно, что ВСЕ данные в левом круге или таблице включены в набор результатов. Только данные, которые перекрываются из таблицы книг, включены из правой таблицы. Это означает, что при использовании LEFT JOIN некоторые данные в правой таблице могут быть исключены.
ПРАВИЛЬНОЕ СОЕДИНЕНИЕ было бы похоже на просьбу « показать мне все книги в моей библиотеке вместе с покупателями, которые их одолжили». »
На этом изображении вы видите, что ВСЕ данные в правой таблице включены в набор результатов. Включены только данные, которые перекрываются из таблицы customers . Это означает, что при использовании RIGHT JOIN некоторые данные в левой таблице могут быть исключены.
ВНЕШНЕЕ СОЕДИНЕНИЕ было бы похоже на просьбу « показать мне все кредиты и все книги, независимо от связей между ними. »
На этом изображении видно, что будут включены ВСЕ данные из обеих таблиц независимо от того, перекрываются ли некоторые данные. Когда такой запрос будет выполнен, у вас будут непересекающиеся данные в результате, и эти поля будут установлены в NULL.
ВНУТРЕННЕЕ СОЕДИНЕНИЕ похоже на просьбу « показывать только клиентов с кредитом». »
Здесь вы можете видеть, что данные могут быть исключены из левой и правой таблиц. Вы не увидите никаких клиентов, если у них нет книги, и вы не увидите никаких книг, если они НЕ взяты в аренду!
Это наиболее распространенный тип данных, который используется по умолчанию при использовании ключевого слова JOIN . Добавленное слово «ВНУТРЕННИЙ» обычно не требуется.
Что означает «левый» и «правый»?
Вы можете думать о «левом» и «правом» как о не более чем исходном порядке. Посмотрите на этот запрос:
SELECT * FROM customers LEFT JOIN books ON customers . book_id = books . id
Обратите внимание, что я упомянул таблицу customers до того, как использовал ключевое слово JOIN . Это означает, что customers — мой «левый» стол. Еще один способ думать об этом — спросить, какая таблица осталась от ключевого слова JOIN , а какая справа от него.
Пара предостережений:
- То, что одна таблица является «левой», не означает, что вы выбираете ВСЕ ее записи. Это функция
LEFT JOINилиRIGHT JOIN, а не исходный порядок! - Левая таблица будет иметь столбцы, перечисленные первыми в наборе результатов, если вы специально не выберете столбцы в разделе
SELECT. - Порядок таблиц после ключевого слова
ONне имеет значения, он будет возвращать те же результаты, если поменять местами.
Достаточно обзор. Каковы эти новые соединения?
Ну, они не «новые», они просто способ задать дополнительные вопросы. Как бы вы нашли ответы на вопросы со словами «не» или «нет» в запросе?
Покажите мне всех клиентов, у которых нет книг в долг.
Покажите мне все книги, которые НЕ сданы в аренду.
СЛЕДУЕТ ПРИСОЕДИНЯТЬСЯ с исключением
Посмотрите на этот рисунок. Обратите внимание на отличие от вышеуказанных СОЕДИНЕНИЙ в затененной области.
Это выглядит как LEFT JOIN , но больше не включает данные «перекрытия». Почему? Это то же самое, что просить « показать мне клиентов, у которых нет книг в кредит». Возможно, вы хотите выбрать всех клиентов без книги и отправить им рассылку со специальной скидкой?
Если вы сообразительны, вы можете подумать, что можете просто найти в таблице customers значение book_id 0, чтобы сделать то же самое. Это будет работать в этом примере, но в большинстве случаев это не так; это зависит от того, как оформлены ваши столы.
Запрос выглядит так:
SELECT * FROM customers LEFT JOIN books ON customers . book_id = books . id WHERE books . id IS NULL
Теперь мы включили предложение WHERE . Каждый раз, когда у вас есть предложение WHERE, вы исключаете данные или фильтруете их, почти как поиск. Так почему же мы ищем books.id как NULL? Почему это будет NULL? Давайте запустим стандарт LEFT JOIN и посмотрим, какие данные он возвращает, у нас будет ответ:
LEFT JOIN вернуть данные
| Я бы | Имя | фамилия | book_id | id1 | заглавие |
|---|---|---|---|---|---|
| 1 | Джо | Дуть | 1 | 1 | Звездные войны |
| 2 | Джейн | лань | 2 | 2 | парк Юрского периода |
| 3 | Гарри | ворона | 2 | 2 | парк Юрского периода |
| 4 | Джеффри | Снег | 0 | значение NULL | значение NULL |
Вы видите проблему? Это LEFT JOIN что означает, что ВСЕ данные из таблицы customers включены независимо от их совпадения с книгами. Джеффри Сноу включен, но у него нет книги в аренде, поэтому столбцы «id1» и «title» установлены в NULL. Когда таблицы объединены, с ним не будет связано название книги или идентификатор книги.
Если мы попросим « показать мне всех клиентов, включая книги, которые у них есть », вам понадобятся вышеуказанные данные, потому что Джеффри является клиентом независимо от того, есть у него книга в долг или нет. Это было бы функцией LEFT JOIN в этом случае.
Если мы зададим вопрос « покажи мне клиентов без книг в кредит », то теперь имеет смысл, что искать. Нам нужно только выбрать клиентов, для которых мы видим NULL для столбца books.id (когда JOINed будет помечен как id1 поскольку есть два столбца с именем «id»). Мы делаем это с помощью стандартного WHERE , если добавляем WHERE books.id IS NULL . Теперь результат фильтруется до следующего:
| Я бы | Имя | фамилия | book_id | id1 | заглавие |
|---|---|---|---|---|---|
| 4 | Джеффри | Снег | 0 | значение NULL | значение NULL |
Теперь у вас есть все клиенты, у которых нет книг в долг.
Право присоединиться с исключением
Давайте сделаем то же самое с RIGHT JOIN . Давайте найдем все книги, которые НЕ сданы никому.
Обычное RIGHT JOIN будет возвращать каждую книгу независимо от того, заимствована ли она, набор результатов будет выглядеть следующим образом:
| Я бы | Имя | фамилия | book_id | id1 | заглавие |
|---|---|---|---|---|---|
| 1 | Джо | Дуть | 1 | 1 | Звездные войны |
| 2 | Джейн | лань | 2 | 2 | парк Юрского периода |
| 3 | Гарри | ворона | 2 | 2 | парк Юрского периода |
| значение NULL | значение NULL | значение NULL | значение NULL | 3 | Маленькая женщина |
| значение NULL | значение NULL | значение NULL | значение NULL | 4 | Том Сойер |
Это выглядит немного по-другому. Во-первых, вы можете заметить, что Парк Юрского периода указан дважды. Это потому, что у двух человек есть книга в аренде, и база данных возвращает строку для каждого совпадения.
Обратите внимание, что все соответствующие столбцы из таблицы customers равны NULL для Little Women и Tom Sawyer, потому что никто не заимствовал эти названия, поэтому нет перекрывающихся данных.
Если мы хотим выбрать все книги, которые не были сданы в аренду, мы просто используем WHERE , чтобы найти «NULL» в столбце customers.id .
SELECT * FROM customers RIGHT JOIN books ON customers . book_id = books . id WHERE customers . id IS NULL
Результат должен быть предсказуемым. Мы получаем только книги, которые не сданы в аренду.
| Я бы | Имя | фамилия | book_id | id1 | заглавие |
|---|---|---|---|---|---|
| значение NULL | значение NULL | значение NULL | значение NULL | 3 | Маленькая женщина |
| значение NULL | значение NULL | значение NULL | значение NULL | 4 | Том Сойер |
НАРУЖНОЕ СОЕДИНЕНИЕ с исключениями
Последний JOIN выглядит следующим образом.
Это JOIN не очень полезно, но, по сути, даст вам список как клиентов без кредита, так и книг, которые не были заимствованы одновременно.
Странное JOIN подобное этому, может быть полезно в тех случаях, когда вам буквально нужно выбирать данные без связи между таблицами. Возможно, вы ищете потерянные данные или ищете несоответствия в какой-то старой базе данных, которую вы конвертировали.
На самом деле, этот тип JOIN настолько странен, что вы даже не можете сделать это в MySQL, он не поддерживает OUTER JOIN . Обычный SQL делает, и запрос будет выглядеть так (MSSQL не MySQL):
SELECT * FROM customers FULL OUTER JOIN books ON customers . id = books . id WHERE customers . id IS NULL OR books . id IS NULL
Результат этого запроса вернет данные, выглядящие примерно так:
| Я бы | Имя | фамилия | book_id | id1 | заглавие |
|---|---|---|---|---|---|
| 1 | Джеффри | Снег | 0 | значение NULL | значение NULL |
| значение NULL | значение NULL | значение NULL | значение NULL | 3 | Маленькая женщина |
| значение NULL | значение NULL | значение NULL | значение NULL | 4 | Том Сойер |
Вы можете добиться OUTER JOIN используя метод, который Крейг Баклер объяснил с помощью UNION , но это не без потенциальных проблем. Лучший результат Google для имитации FULL OUTER JOIN в MySQL — с 2006 года, и его можно найти здесь . Это может стать несколько сложным.
Главное, на что нужно обратить внимание в приведенном выше коде, это проверка на NULL с обеих сторон JOIN, потому что мы хотим исключить из обеих таблиц. Не проверяя обе стороны, мы бы просто получили один из других JOIN, о котором только что говорили.
Независимо от того, насколько странным или поддерживаемым является такой запрос, я хотел включить его, потому что это допустимый тип JOIN , если вы можете подумать о причине его использования.
другие мысли
Вы всегда должны использовать WHERE полей, которые не могут иметь значение NULL в качестве фактического значения! Мы всегда проверяли поля идентификаторов, которые не могут иметь значение NULL в качестве значения. Представьте, что в нашей таблице книг есть поле ISBN, которое допускает NULL. Если бы мы проверили NULL, используя это поле, оно включало бы строки, которые нам могут не понадобиться!
Есть еще одно JOIN называемое CROSS JOIN которое также странно и уникально. Представьте, что вместо сопоставления одного пользователя с одной книгой КАЖДЫЙ пользователь сопоставляется с КАЖДОЙ книгой! Да, это означает, что если у вас есть 20 книг и 30 клиентов, CROSS JOIN приведет к 30 * 20 строкам данных! Для примера того, как это может быть полезно, посмотрите эту статью .
Обратите внимание, что в MySQL JOIN , INNER JOIN и CROSS JOIN являются синтаксическими эквивалентами и могут заменять друг друга. Это потому, что JOIN и INNER JOIN делают одно и то же и должны использовать ключевое слово ON для сопоставления столбцов. При использовании CROSS JOIN ключевое слово ON отсутствует, так как оно сопоставляет каждую строку в таблице A с каждой строкой в таблице B.
Вывод
Я надеюсь, что эти дополнительные несколько JOIN-ов имели для вас смысл. Подумайте об их использовании каждый раз, когда вы запрашиваете данные между таблицами, где что-то «не соответствует» другому.
- «Найти всех клиентов, которые НЕ заказывали раньше».
- «Найти всех клиентов НЕ в черном списке».
- «Найти все товары, которые НЕ были проданы».
- «Найти всех клиентов, НЕ занимая книги».
- «Найдите всех собак, которых в последнее время не гуляли».
- «Найти сотрудников, которые НИКОГДА не отправляли в службу поддержки».
Таким образом, в зависимости от того, как определены ваша база данных и таблицы, вам может потребоваться использовать WHERE для проверки значений NULL, чтобы исключить совпадения, а не включать их, как при обычном поведении JOIN.
Итак … тебе когда-нибудь нужно было перекрестное соединение? Какие-либо другие конкретные случаи использования, о которых вы хотели бы рассказать или о которых нам хотелось бы рассказать? Дайте нам знать!