MySQL.JOIN.примеры.

В данном посте будет рассмотренно несколько возможных применений LEFT JOIN, INNER JOIN и HAVING для запросов к MySQL.

По ходу текста будут внесены некоторые коментарии и результаты выполнения.
Небольшая база с основной информацией:

• authors {id, name}   Отобразить дамп Скрыть дамп

  --
  -- Структура таблицы `authors`
  --

  CREATE TABLE IF NOT EXISTS `authors` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(75) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=cp1251;
  --
  -- Дамп данных таблицы `authors`
  --

  INSERT INTO `authors` 
  (`id`, `name`) 
  VALUES
  (1, 'Николай Андреев'),
  (2, 'Сергей Лукьяненко'),
  (3, 'Роман Злотников');
  

• books {id, name}   Отобразить дамп Скрыть дамп

  --
  -- Структура таблицы `books`
  --  
  CREATE TABLE IF NOT EXISTS `books` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(75) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=cp1251;

  --
  -- Дамп данных таблицы `books`
  --

  INSERT INTO `books` 
  (`id`, `name`) 
  VALUES
  (1, 'Мир героев'),
  (2, 'Лучшие из мертвых'),
  (3, 'Яд для живых'),
  (4, 'Сектор мутантов'),
  (5, 'Геном'),
  (6, 'Танцы на снегу'),
  (7, 'Сос по прозвищу Верёвка');
  

• library {id, auth_id, book_id}   Отобразить дамп Скрыть дамп

  --
  -- Структура таблицы `library`
  --

  CREATE TABLE IF NOT EXISTS `library` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `auth_id` int(11) NOT NULL,
    `book_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=cp1251;

  --
  -- Дамп данных таблицы `library`
  --

  INSERT INTO `library` 
  (`auth_id`, `book_id`) 
  VALUES
  (1, 1),
  (1, 3),
  (1, 4),
  (2, 5),
  (2, 6),
  (1, 6),
  (1, 2);
  

Полный дамп базы можно почитать тут

И так,преступим составлению запросов.
Имена авторов с указанием кол-ва книг

SELECT a.name, COUNT(l.book_id) bcount
FROM authors a
LEFT JOIN library l ON l.auth_id = a.id 
GROUP BY a.id
ORDER BY bcount DESC

name	bcount
Николай Андреев	5
Сергей Лукьяненко	2
Роман Злотников	0

Авторы у которых более 3 книг.

SELECT a.name, count(l.id) bcount
FROM authors a
LEFT JOIN library l ON l.auth_id = a.id
GROUP BY a.id
HAVING bcount > 3
ORDER BY bcount DESC

name	bcount
Николай Андреев	5

Авторы без книг

SELECT a.name
FROM authors a
LEFT JOIN library l ON l.auth_id = a.id
WHERE l.auth_id IS NULL

name
Роман Злотников

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

Логическим завершением будет Список книг библиотеки

SELECT b.name, a.name
FROM library l
LEFT JOIN authors a ON l.auth_id = a.id
LEFT JOIN books b ON l.book_id = b.id

name	name
Мир героев	Николай Андреев
Яд для живых	Николай Андреев
Сектор мутантов	Николай Андреев
Геном	Сергей Лукьяненко
Танцы на снегу	Сергей Лукьяненко
Танцы на снегу	Николай Андреев
Лучшие из мертвых	Николай Андреев

И результат оказался несовсем красивым, хотя вполне ожидаемым.
Данный запрос читается так:
"Выбрать назнание книг из таблицы books и имена авторов из таблицы authors для всех записей из таблицы library при условии соответствия l.auth_id = a.id и l.book_id = b.id."
Первым делом изменим выборку для большей логичности

- SELECT b.name, a.name
+ SELECT b.name book, a.name author

Таким образом заголовок "name , name" изменится на необходимый нам "book, author"
Если мы изменим запрос к виду:

 SELECT b.name book , 
 (
  SELECT a.name FROM authors a
  LEFT JOIN library l ON l.auth_id = a.id
  WHERE l.book_id = b.id
 ) author
 FROM books b

Получим ошибку вида "Subquery returns more than 1 row", что лишний раз доказыват, необходимость решения этой проблемы на стороне php и в контекст даного поста не входит.
Перый конечный вид запроса:

SELECT b.name book, a.name author
FROM library l
LEFT JOIN authors a ON l.auth_id = a.id
LEFT JOIN books b ON l.book_id = b.id

Следует помнить, что в результате этого запроса возможны "NULL" значения в полях book и author.
Второй конечный вид запроса:

SELECT b.name book, a.name author
FROM library l
INNER JOIN authors a ON l.auth_id = a.id
INNER JOIN books b ON l.book_id = b.id

Следует помнить, что в лучае такого запроса проверется обязательное соответствие условий "ON". Например удаление автора приведет к неотображению всех книг, для которых автор является единственным.

book	author
Мир героев	Николай Андреев
Яд для живых	Николай Андреев
Сектор мутантов	Николай Андреев
Геном	Сергей Лукьяненко
Танцы на снегу	Сергей Лукьяненко
Танцы на снегу	Николай Андреев
Лучшие из мертвых	Николай Андреев

Дополнительно по теме:

• Справочное руководство по MySQL. 6.4.1 Синтаксис оператора SELECT.
  Отобразить описание Скрыть описание
  Особое внимание следует обратить на построение SELECT запроса и порядок применения условий. На применение HAVING достаточно безполезно придумывать примеры. Следует помнить, HAVING отрабатыват только по вычисляемым полям, по которым WHERE фильтровать не может.
• Справочное руководство по MySQL. 6.4.1.1 Синтаксис оператора JOIN.
  Отобразить описание Скрыть описание
  Основная информация - это ключевое отличие LEFT и INNER JOIN.
• A Visual Explanation of SQL Joins
  Отобразить описание Скрыть описание
  Очень неплохое и весьма наглядное описание применения JOIN. Информация на английском, но имеет очень наглядные примеры.

Если кого-то таки заинтересует более подробная информация дайте знать, дополню.