Визуальное представление соединений JOIN в SQL

'JOIN' – ключевое слово в SQL, используемое для запроса данных из двух и более связанных таблиц. Данная статья – это попытка кратко и лаконично объяснить работу с JOIN себе и всем, кто заинтересован в этом.

Связанные таблицы

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

Код MySQL для создания таблицы:

CREATE TABLE `user` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(30) NOT NULL,
	`course` smallint(5) unsigned DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Номера курсов связаны с объектами, находящимися в таблице курсов...

Код MySQL для создания таблицы:

CREATE TABLE `course` (
	`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Поскольку мы используем таблицы InnoDB и знаем, что user.course и course.id связаны, то можем задать связь внешним ключом:

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

В сущности, MySQL будет автоматически:

• перенумеровывать связанные записи в столбце user.course при изменениях в course.id;
• отклонять любые попытки удалить курс, на который зачислены пользователи.

INNER JOIN (или просто JOIN)

INNER JOIN является наиболее часто используемым. Он дает набор записей, которые совпадают в обоих таблицах, и в user, и в course, то есть все пользователи, зачисленные на курсы:

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

Результат:

LEFT JOIN

А что делать, если нам необходим список всех студентов и их курсов, даже если они не зачислены ни на один курс? LEFT JOIN даст набор записей, которые соответствуют каждой записи в левой таблице (user) независимо от соответствия записи в правой таблице (course):

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;

Добавив условие

WHERE course.id IS NULL

мы получим список студентов, не зачисленных ни на один курс. И диаграмма для такого запроса будет следующей:

RIGHT JOIN

Возможно, нам потребуется список всех курсов и студентов, даже если никто из них не был зачислен? RIGHT JOIN дает набор записей, которые соответствуют каждой записи в правой таблице (course) независимо от соответствия записи в левой таблице (user):

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Результат:

RIGHT JOIN используется редко, так как вы можете получить тот же результат, используя LEFT JOIN. Следующий код может быть более эффективным и быстрым для парсинга СУБД:

SELECT user.name, course.name
FROM `course`
LEFT JOIN `user` on user.course = course.id;

Мы могли бы, например, подсчитать количество студентов, обучающихся на каждом курсе:

SELECT course.name, COUNT(user.name)
FROM `course`
LEFT JOIN `user` ON user.course = course.id
GROUP BY course.id;

Результат:

OUTER JOIN (или FULL OUTER JOIN)

OUTER JOIN возвращает все записи из обеих таблиц независимо от совпадений. Там где совпадения нет, на недостающей стороне будет содержаться NULL.

OUTER JOIN менее полезен по сравнению с INNER, LEFT или RIGHT, и он не реализован в MySQL. Тем не менее, вы можете обойти это ограничение, используя UNION для LEFT JOIN и RIGHT JOIN, например:

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id

UNION

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

Результат:

Вот и всё! Надеюсь этот материал будет кому-то полезен.

Теги