MySQL – сведения о соединении

Автор оригинала: David Wong.

0 индекс

• ПРИСОЕДИНИТЬСЯ Порядок выполнения инструкции
• ВНУТРЕННЕЕ/ЛЕВОЕ/ПРАВОЕ/ПОЛНОЕ СОЕДИНЕНИЕ Разница
• НА и ГДЕ Разница

1 Обзор

Полная инструкция SQL будет разделена на несколько предложений, и виртуальные таблицы (VTS) будут созданы во время выполнения предложений, но будет возвращена только последняя виртуальная таблица. С этой точки зрения мы пытаемся понять процесс выполнения запроса на соединение и ответить на некоторые распространенные вопросы. Если раньше не было понятия о результатах выполнения различных соединений, вы можете прочитать его в сочетании с этой статьей

2 последовательность выполнения соединения

Ниже приведена общая структура запроса на соединение

SELECT  
  FROM  
     JOIN  
      ON  
        WHERE 

Его последовательность выполнения выглядит следующим образом (первым выполняемым оператором SQL всегда является предложение from) :

• ИЗ : Декартово произведение выполняется в левой и правой таблицах для создания первой таблицы VT1. Количество строк равно n * m (n-количество строк в левой таблице, а Make-количество строк в правой таблице
• ВКЛ : отфильтруйте VT1 построчно в соответствии с условием вкл и вставьте результат в vt2
• СОЕДИНЕНИЕ : добавьте внешнюю строку, если указано ЛЕВОЕ СОЕДИНЕНИЕ ( ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ ), сначала пройдите Левую таблицу Каждая строка, которой нет в vt2, будет вставлена в vt2 , а остальные поля этой строки будут заполнены как NULL , форма VT3; если указано ПРАВОЕ СОЕДИНЕНИЕ То же самое верно. Но если вы укажете ВНУТРЕННЕЕ СОЕДИНЕНИЕ , внешняя строка не будет добавлена, и описанный выше процесс вставки игнорируется (поэтому ВНУТРЕННЕЕ СОЕДИНЕНИЕ Условия фильтрации ВКЛ или ГДЕ Нет никакой разницы в результатах внедрения, которые будут подробно рассмотрены ниже)
• ГДЕ : фильтр VT3 условно, и квалифицированные строки выводятся в vt4
• ВЫБЕРИТЕ : перенесите указанное поле из vt4 в vT5

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

3 пример

Создайте таблицу сведений о пользователе:

CREATE TABLE `user_info` (
  `userid` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Создайте другую таблицу баланса пользователя:

CREATE TABLE `user_account` (
  `userid` int(11) NOT NULL,
  `money` bigint(20) NOT NULL,
 UNIQUE `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Небрежно импортируйте некоторые данные:

select * from user_info;
+--------+------+
| userid | name |
+--------+------+
|   1001 | x    |
|   1002 | y    |
|   1003 | z    |
|   1004 | a    |
|   1005 | b    |
|   1006 | c    |
|   1007 | d    |
|   1008 | e    |
+--------+------+
8 rows in set (0.00 sec)

select * from user_account;
+--------+-------+
| userid | money |
+--------+-------+
|   1001 |    22 |
|   1002 |    30 |
|   1003 |     8 |
|   1009 |    11 |
+--------+-------+
4 rows in set (0.00 sec)

В общей сложности у 8 пользователей есть имена пользователей, а у 4 пользователей есть балансовые счета. Выведите имя пользователя и баланс с идентификатором пользователя 1003. SQL выглядит следующим образом :

SELECT i.name, a.money 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON i.userid = a.userid 
        WHERE a.userid = 1003;

Шаг 1: выполните из предложения в декартово произведение двух таблиц

После операции декартова произведения будет возвращена комбинация всех строк в двух таблицах. В левой таблице пользователя 8 строк? Информация и 4 строки в правой таблице пользователя? Счет. Сгенерированная виртуальная таблица VT1 состоит из 8 * строк

SELECT * FROM user_info as i LEFT JOIN user_account as a ON 1;
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1001 |    22 |
|   1003 | z    |   1001 |    22 |
|   1004 | a    |   1001 |    22 |
|   1005 | b    |   1001 |    22 |
|   1006 | c    |   1001 |    22 |
|   1007 | d    |   1001 |    22 |
|   1008 | e    |   1001 |    22 |
|   1001 | x    |   1002 |    30 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1002 |    30 |
|   1004 | a    |   1002 |    30 |
|   1005 | b    |   1002 |    30 |
|   1006 | c    |   1002 |    30 |
|   1007 | d    |   1002 |    30 |
|   1008 | e    |   1002 |    30 |
|   1001 | x    |   1003 |     8 |
|   1002 | y    |   1003 |     8 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   1003 |     8 |
|   1005 | b    |   1003 |     8 |
|   1006 | c    |   1003 |     8 |
|   1007 | d    |   1003 |     8 |
|   1008 | e    |   1003 |     8 |
|   1001 | x    |   1009 |    11 |
|   1002 | y    |   1009 |    11 |
|   1003 | z    |   1009 |    11 |
|   1004 | a    |   1009 |    11 |
|   1005 | b    |   1009 |    11 |
|   1006 | c    |   1009 |    11 |
|   1007 | d    |   1009 |    11 |
|   1008 | e    |   1009 |    11 |
+--------+------+--------+-------+
32 rows in set (0.00 sec)

Шаг 2. выполните предложение on, чтобы отфильтровать неквалифицированные строки

На.После фильтрации vt2 выглядит следующим образом:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Шаг 3: Присоединение добавляет внешнюю строку

ЛЕВОЕ СОЕДИНЕНИЕ Строки, которые не отображаются в vt2 в левой таблице, будут вставлены в vt2 , а остальные поля в каждой строке будут заполнены null, ПРАВОЕ СОЕДИНЕНИЕ Сочувствие В этом случае ЛЕВОЕ СОЕДИНЕНИЕ , поэтому левая таблица user_info Добавьте сгенерированную таблицу VT3 к остальным строкам:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

Шаг 4: где условная фильтрация

Где создать таблицу vt4:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Шаг 5: Выберите

Выберите i.name, a.деньги генерируют vT5:

+------+-------+
| name | money |
+------+-------+
| z    |     8 |
+------+-------+

Виртуальная таблица vT5 возвращается клиенту в качестве конечного результата

После представления процесса связывания таблиц давайте рассмотрим общее ОБЪЕДИНЕНИЕ Различие

4 разница между внутренним/левым/правым/полным соединением

• ВНУТРЕННЕЕ СОЕДИНЕНИЕ…НА… : возвращает все строки, соответствующие левой и правой таблицам (поскольку выполняется только второй шаг фильтрации, а третий шаг не выполняется для добавления внешних строк)
• ЛЕВОЕ СОЕДИНЕНИЕ…НА… : возвращает все строки в левой таблице. Если некоторые строки не имеют соответствующих совпадающих строк в правой таблице, установите для столбцов в правой таблице значение null в новой таблице
• ПРАВОЕ СОЕДИНЕНИЕ…НА… : возвращает все строки правой таблицы. Если некоторые строки не имеют соответствующих совпадающих строк в левой таблице, для столбца левой таблицы в новой таблице будет установлено значение null

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

Выполните третий шаг выше Добавьте внешнюю строку Например, если ЛЕВОЕ СОЕДИНЕНИЕ заменить на ВНУТРЕННЕЕ СОЕДИНЕНИЕ Он пропустит этот шаг, и сгенерированная таблица VT3 будет точно такой же, как и vt2.

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

ПРАВИЛЬНО ПРИСОЕДИНЯЙТЕСЬ

если ЛЕВОЕ СОЕДИНЕНИЕ заменить на ПРАВОЕ СОЕДИНЕНИЕ , сгенерированная таблица VT3 выглядит следующим образом:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
+--------+------+--------+-------+

Поскольку в учетной записи пользователя (правая таблица) есть строка, но запись этой строки не может быть найдена в информации о пользователе (левая таблица), на шаге 3 будет вставлена следующая строка:

|   NULL | NULL |   1009 |    11 |

ПОЛНОЕ ПРИСОЕДИНЕНИЕ

Стандартный SQL определил ПОЛНОЕ СОЕДИНЕНИЕ , которое не поддерживается в mysql , но мы можем использовать ЛЕВОЕ СОЕДИНЕНИЕ + ОБЪЕДИНЕНИЕ + ПРАВОЕ СОЕДИНЕНИЕ Для достижения ПОЛНОГО ПРИСОЕДИНЕНИЯ :

SELECT * 
  FROM user_info as i 
    RIGHT JOIN user_account as a 
      ON a.userid=i.userid
union 
SELECT * 
  FROM user_info as i 
    LEFT JOIN user_account as a 
      ON a.userid=i.userid;

Он вернет следующие результаты:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   NULL | NULL |   1009 |    11 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

PS: на самом деле, из семантики ЛЕВОГО СОЕДИНЕНИЯ и ПРАВОГО СОЕДИНЕНИЯ мы видим, что разницы нет. Разница между двумя результатами зависит от порядка размещения левой и правой таблиц. Ниже приводится выдержка из официального документа MySQL:

ПРАВОЕ СОЕДИНЕНИЕ работает аналогично ЛЕВОМУ СОЕДИНЕНИЮ. Чтобы сохранить переносимость кода между базами данных, рекомендуется использовать ЛЕВОЕ СОЕДИНЕНИЕ вместо ПРАВОГО СОЕДИНЕНИЯ.

Поэтому, когда вы не знаете, использовать ли левое соединение или правое соединение, просто используйте левое соединение как можно чаще

5 разница между вкл и где

После того, как порядок выполнения соединения будет ясен, разница между “вкл.” и ” где ” будет хорошо понята. Например:

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    LEFT JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

Первая ситуация ЛЕВОЕ СОЕДИНЕНИЕ После выполнения второго шага по предложению отфильтруйте, что i.идентификатор пользователя.идентификатор пользователя и Третий шаг-добавить внешнюю строку в виртуальную таблицу для создания VT3, что является конечным результатом

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   NULL |  NULL |
|   1002 | y    |   NULL |  NULL |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+

Вторая ситуация ЛЕВОЕ СОЕДИНЕНИЕ После выполнения второго шага по предложению отфильтруйте, что i.идентификатор пользователя.идентификатор пользователя На третьем шаге выполняется предложение join для добавления внешней строки для создания таблицы VT3. На четвертом шаге выполняется предложение where для фильтрации таблицы VT3 для создания vt4. Конечный результат выглядит следующим образом:

vt2:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+
vt3:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1001 | x    |   1001 |    22 |
|   1002 | y    |   1002 |    30 |
|   1003 | z    |   1003 |     8 |
|   1004 | a    |   NULL |  NULL |
|   1005 | b    |   NULL |  NULL |
|   1006 | c    |   NULL |  NULL |
|   1007 | d    |   NULL |  NULL |
|   1008 | e    |   NULL |  NULL |
+--------+------+--------+-------+
vt4:
+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Если ЛЕВОЕ СОЕДИНЕНИЕ заменить на ВНУТРЕННЕЕ СОЕДИНЕНИЕ , независимо от условий фильтра ВКЛЮЧЕНО все еще ГДЕ Результаты те же, потому что Внутреннее соединение не выполняет шаг 3 для добавления внешней строки

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid and i.userid = 1003;

SELECT * 
  FROM user_info as i
    INNER JOIN user_account as a
      ON i.userid = a.userid where i.userid = 1003;

Возвращенные результаты являются:

+--------+------+--------+-------+
| userid | name | userid | money |
+--------+------+--------+-------+
|   1003 | z    |   1003 |     8 |
+--------+------+--------+-------+

Справочный материал

MySQL technology insider: Программирование SQL SQL – соединения – W3Schools sql-В чем разница между “ВНУТРЕННИМ СОЕДИНЕНИЕМ” и “ВНЕШНИМ СОЕДИНЕНИЕМ”? MySQL:: Справочное руководство по MySQL 8.0:: 13.2.10.2 Синтаксис СОЕДИНЕНИЯ Визуальное представление соединений SQL Соединение (SQL) – Википедия)