Оператор Join в MySQL

Оператор Join в MySql используется для получения выборки из двух таблиц. Берётся запись одной таблицы и соответствующая ей запись второй таблицы. Из них составляется одна запись и добавляется в результат. Чаще всего оператор используют в трёх вариантах:

INNER JOIN - внутреннее объединение. Берутся записи из обоих таблиц.
LEFT JOIN - левостороннее объединение. Берутся записи из первой таблицы. А из второй таблицы используются только те записи, которые соответствуют какой-то записи первой таблицы.
RIGHT JOIN - правостороннее объединение. Берутся записи из второй таблицы. А из первой таблицы используются только те записи, которые соответствуют какой-то записи второй таблицы.

Сделаем выборку из таблиц users и comments:

1 2	<?php header('Content-type: text/html; charset=utf-8');

3
4
5
6
7
8
9
10
11
12
13

$db = mysqli_connect('localhost', 'root', '', 'mybase');
$query = "SELECT * FROM comments INNER JOIN users";
$result = mysqli_query($db, $query);
if ($result)
  {
  for ($i=0; $i<mysqli_num_rows($result); $i++)
    {
    print_r(mysqli_fetch_row($result));
    echo '<br>';
    }
  }

В этом примере каждая строка таблицы users соединена с каждой строкой таблицы comments. Результат выборки выведен в индексированный массив. Это сделано, потому что в таблицах есть поля с одинаковыми именами. В нашем примере это поля id. Если вывести ассоциативный массив, то элемент id будет только один и в него попадут данные только из одной таблицы.

Left join. Примеры использования

В таком виде, как в предыдущем примере оператор Join используется редко. Обычно соединяются строки, которые как-то связаны. Для примера выведем все комментарии с информацией об авторе каждого комментария. В таблице comments есть поле user_id, который содержит id пользователя, написавшего комментарий. Используем LEFT JOIN. Запрос получится такой:

4 5	$query = "SELECT * FROM comments LEFT JOIN users ON (comments.user_id = users.id)";

В запросе использован оператор ON, который указывает связь между таблицами. Для каждой записи таблицы comments берём поле user_id и в таблице users находим запись с таким id. Так как у комментария только один пользователь, то для него находиться только одна запись.

В нашем примере мы получаем все поля обеих таблиц. Укажем только те поля, которые нам нужны:

3
4
5
6
7
8
9
10
11
12
13
14
15

$db = mysqli_connect('localhost', 'root', '', 'mybase');
$query = "SELECT comments.id, text, login
  FROM comments LEFT JOIN users
  ON (comments.user_id = users.id)";
$result = mysqli_query($db, $query);
if ($result)
  {
  for ($i=0; $i<mysqli_num_rows($result); $i++)
    {
    print_r(mysqli_fetch_assoc($result));
    echo '<br>';
    }
  }

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

Теперь в выборке нет полей с одинаковыми именами. И мы вывели её в ассоциативный массив. С ним гораздо удобнее работать.

В примере мы получаем все комментарии. Но можно использовать оператор WHERE и указать условие выборки. Для примера выведем только те комментарии, у которых поле text не пустое:

4
5
6
7

$query = "SELECT comments.id, text, login
  FROM comments LEFT JOIN users
  ON (comments.user_id = users.id)
  WHERE text <> ''";

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

Для INNER JOIN также можно использовать оператор ON и указать связь между таблицами. Такой запрос будет работать по-другому. Запросы по разному обрабатывают комментарии, для которых указан несуществующий пользователь. Например, для какого-то комментария user_id равно 5. Но в таблице users нет записи с таким id. LEFT JOIN выведет такой комментарий, просто поля, относящиеся к пользователю, будут пустыми. А INNER JOIN вообще не выведет этот комментарий, потому что он находит только связанные записи из обеих таблиц.