Этот хитрый LEFT JOIN

Введение (можно и пропустить).

Рано или поздно, так или иначе, любому программисту связанному с системами хранения данных (как MySQL, к примеру) приходится сталкиваться с проблемой соединения таблиц. (Здесь и далее, условимся, речь идёт о связке PHP + MySQL). Вообще сразу скажу, чего я имею ввиду. Вот, предположим, есть у нас пара таблиц — users и user_ip_address. В программке Navicat наваял некую схемку, чтобы было проще представить себе структурку:

Mysql Join
Mysql Join

Кроме того, я добавил так называемый Foreign Key к таблице user_ip_address, связав её с users. Запрос выглядит так:

ALTER TABLE `user_ ip_address`
ADD CONSTRAINT `ip_to_user`
FOREIGN KEY (`user_id`)
REFERENCES `users` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

Теперь если, скажем мы удаляем запись о пользователе в таблице users, БД сама удалит все связанные данные в таблице user_ip_address. ON UPDATE можно не ставить. Foreign Keys — очень мощная и кошерная штука. Она здорово облегчает работу, перекладывая заботу о целостности данных на плечи БД.

Так вот, в общем и целом, эти таблицы описывают структуру с юзером, и его айпишниками. В таблице users есть три пользователя — alex, john & verner, у Алекса и Джона есть записи в таблице user_ip_addresses, а у Вернера — нет. Собственно, нас интересуют только и исключительно поля user_id в таблице user_ip_address, и поле id в таблице users. Как вы уже догадались, они связаны по этим полям. И вот, представим себе ситуацию, что вам надо сделать выборку всех пользователей с соответствующими им ip… Работёнка не такая уж и тривиальная поначалу. Обычно новички (да и я в своё время) делают как… сначала делают выборку всех пользователей, запросом типа:

 SELECT * FROM `users`; 

А потом благополучно перебирают получившийся массив в цикле на PHP, и на каждой итерации совершают ещё по запросу, вида

 SELECT * FROM `user_ip_address` WHERE `user_id`='{$uid}'; 

Нет, я ничего не говорю, метод рабочий, но если у вас, допустим, форум с 1000 пользователей — сервер загнётся, потому как это 1001 запрос. Если надо просто получить список ip, то можно сделать и так:

SELECT DISTINCT * FROM `user_ip_address`; 

Недостаток методы в том, что вы получите только список ip, без привязки к пользователям… Что же делать? В общем, есть такая замечательная инструкция в SQL как JOIN (соединить). Она позволяет получить в одной выборке сразу данные из нескольких таблиц. Но тут есть засада. При попытке найти нормальную документацию по вопросу — вообще по инструкции — вас сначала окунут в дебри теории, а потом покажут пару куцых примерчиков. Это безмерно раздражает. В общем, в этой статье я постараюсь популярно рассказать, как соединять таблицы и в чем отличия между JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN и так далее. (Это, к слову, один из самых любимых вопросов на собеседованиях при приёме на работу веб-программиста, да и просто понимать о чем идёт речь крайне полезно, я например очень здорово облегчил себе жизнь, когда познакомился с соединениями таблиц поближе).

Техническая часть.

Итак, пролив воды, сразу берём быка за рога. Синтаксис команды:

SELECT * FROM `users`/* Выбрать из первой таблицы записи.... */
LEFT|INNER|RIGHT JOIN `user_ip_address` /* ... также присоединив вторую ... */
ON (`user_ip_address`.`user_id`=`users`.`id`) /* ... по-условию совпадения полей в обоих таблицах ... */
WHERE `name` LIKE '%alex%'; /* ... а также по второму условию - только для первой таблицы, если надо */

Например:

SELECT * FROM users t_u
INNER JOIN `user_ip_address` t_ip
ON t_ip.user_id = t_u.id

Результатом будет 6 строк.

Inner Join Result
Inner Join Result

Как можно видеть, были выданы записи из таблицы users, для которых имеются IP-адреса в другой таблице. Вернер в результат запроса не попал, потому что у него нет IP-адресов. Если поменять местами таблицы в этом запросе — ничего не изменится, кроме порядка вывода полей, если здесь вначале выводятся поля таблицы users, а затем поля user_ip_address, то в другом случае будет наоборот — сначала id, user_id и ip, а затем id1 (user), name & password. Набор данных при этом остается прежним. К абсолютно аналогичным результатам приведет запрос вида:

SELECT * FROM user_ip_address t_ip, users t_u
WHERE t_u.id = t_ip.user_id

 

 

Дело в том, что здесь образуется неявный INNER JOIN, поэтому result set будет полностью идентичным запросу с явным.

Собственно, соединять таблицы можно в любом количестве (несколько join один за другим), но надо помнить, что любой join это довольно тяжёлый запрос (но несомненно он в разы легче чем 1001 отдельный). Поэтому разумно на поля, по которым происходит выборка/сортировка, установить индексы. Ну да это только пример, для затравки. Как вы понимаете вместо INNER можно указать и LEFT JOIN — тоже будет работать, но немного иначе. Вообще, в чем разница между всеми этими JOIN’s? Это любимый вопрос на собеседованиях — хитрые менеджеры по подбору персонала любят его задавать, особенно часто он звучит как: «в чем разница между left join и inner join» ? При этом хитрый менеджер как правило сам не понимает, что он спрашивает. Если этот вопрос задает программист, то он может прикрываться самыми благими целями — дескать, он хочет выявить уровень опыта и всякое такое. На самом деле — ложь, пиздеж и провокация, ибо такие вещи выявляет только практика совместной работы. В таком случае на них хорошо действуют заумные заклинания, типа декартового произведения. Если вы поняли, что это такое, а то вдруг собеседующий не просто выёбывается, а и правда знает механизм :)

Так вот, разница между join-ами разных типов в том, как они формируют результат запроса. В PHP это массив, который вам вернёт mysql_fetch_array($result, MYSQL_ASSOC);

LEFT JOIN — вернёт строки из левой таблицы, даже если их нет в правой. Т.е. она всегда возвращает строки из таблицы слева, в данном случае из users. Как мы помним, у Вернера нет IP-адресов, однако запрос вида:

SELECT * FROM users t_u
LEFT JOIN user_ip_address t_ip ON t_ip.user_id = t_u.id

 

 

Всё равно возвращает Вернера в результатах:

LEFL JOIN ip_addresses
LEFL JOIN ip_addresses

Если мы поменяем таблицы в порядке выборок, то выйдет вот что:

LEFT JOIN users
LEFT JOIN users

Как мы видим, в выборку не попадает записей, для которых нет IP адресов, по сути данная выборка аналогична INNER JOIN.

RIGHT JOIN — полностью аналогичен LEFT JOIN, только он вернёт уже данные из правой таблицы вне зависимости, есть ли у них связь с левой. Просто взгляните на скриншот и всё поймете :)

RIGHT JOIN users
RIGHT JOIN users

Наконец, FULL JOIN вернет записи при хотя бы одном совпадении в любой из таблиц. Собственно, это комбинация из LEFT JOIN и RIGHT JOIN. В принципе это почти ничем не отличается от одновременной выборки из двух таблиц. За исключением того, что некоторые поля правильным образом связаны по какому-либо полю. Только в MySQL FULL JOIN не поддерживается :) Равно как и всякие Full outer join и прочие. Они поддерживаются в PostgreSQL, которую я лично очень люблю использовать в своих проектах. Как там это устроено, можно почитать здесь.

Вообще, я бы порекомендовал создать аналогичные таблички в тестовой базе и побаловаться различными запросами. Для лучшего понимания. Помните, что для ограничений выборки по какому-то пользователю можно использовать условия WHERE. (В данном случае по пользователю, а если взглянуть шире — то по какой-то записи).

1, alex, 123, 127.0.0.1

Автор

Алекс Разгибалов

Сумасшедший мужчина, неопределённого возраста, наслаждающийся манией преследования. Паталогически недоверчив, эгоистичен, авторитарен. Вторичные диагнозы - программист и поц. Владеет английским языком на уровне около хренового разговорного. Также знаком с некоторыми другими языками. Интересуется всем и вся, за счёт чего в любой области знания являются поверхностными, неглубокими. Характер невыдержанный. Крепость - 55 градусов.

Этот хитрый LEFT JOIN: 12 комментариев

  1. Left join и right join немого коварные конструкции. Как вы правильно описали возвращаются значения NULL в полях результирующего курсора если данные отсутствуют. И, например, конструкция group by может нагло не включить в суммирование строки в которых есть поля равные NULL. Поэтому, дабы не было лишнего геморроя желательно выполнять преобразование NULL в 0 для чисел либо в пустую строку для текстовых значений.
    Я уже не раз сталкивался с этим в СУБД Orcle, MSSql, InterBase, Firebird. Насчёт MySQL не курсе. Но возможно эта СУБД ведёт себя также.

    1. Спасибо, я вот этого насчёт GROUP BY не знал (хотя косвенно использовал, каюсь, чтобы результаты с NULL исключить как раз)). А вообще в последнее время пришёл к выводу, что намного нагляднее и удобнее во многих (но не во всех) случаях использовать соединения через условия во WHERE. Получается по выдаче тот же INNER JOIN, а выглядит понятнее…
      Самое интересное, чем дальше в лес, тем толще партизаны… БД вещь многогранная)) Я вот с ними сравнительно немного сталкиваюсь, на уровне вставок-выборок относительно несложных.

  2. Ну да. Если не выполнять SUM() над полями то group by будет исключать строки со значениями NULL.

    То что БД вещь многогранная согласен целиком и полностью :)))) Иногда приходится так извратиться. Бывали случаи когда приходилось вязать таблицу к самой себе несколько раз с разными условиями. Тут без join-ов увы никак :)))

  3. Особенно понравилось ето «в чем разница между left join и inner join» ? При этом хитрый менеджер как правило сам не понимает, что он спрашивает, он заучил ответ на один вопрос, который он выклянчил у одного из программистов.
    Поржал. спасибо.

  4. Хорошая статейка, как раз искал в чём разница между просто Join и Inner Join. Спасибо автору!

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Собирать идеально - не обязательно, просто приблизительно соберите картинку (должен быть включен JavaScript).WordPress CAPTCHA