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

Добавлено 11 февраля 2016 в 18:00

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

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

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

Таблица 'user'
idnamecourse
1Alice1
2Bob1
3Caroline2
4David5
5Emma(NULL)

Код 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;

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

Таблица 'course'
idname
1HTML5
2CSS3
3JavaScript
4PHP
5MySQL

Код 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

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

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

Результат:

user.namecourse.name
AliceHTML5
BobHTML5
CarolineCSS3
DavidMySQL

LEFT JOIN

left join

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

SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;
user.namecourse.name
AliceHTML5
BobHTML5
CarlineCSS3
DavidMySQL
Emma(NULL)

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

WHERE course.id IS NULL

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

left join excluding

RIGHT JOIN

right join

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

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

Результат:

user.namecourse.name
AliceHTML5
BobHTML5
CarlineCSS3
(NULL)JavaScript
(NULL)PHP
DavidMySQL

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;

Результат:

course.namecount()
HTML52
CSS31
JavaScript0
PHP0
MySQL1

OUTER JOIN (или FULL OUTER JOIN)

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;

Результат:

user.namecourse.name
AliceHTML5
BobHTML5
CarlineCSS3
DavidMySQL
Emma(NULL)
(NULL)JavaScript
(NULL)PHP

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

Теги

JOINSQLБаза данных

На сайте работает сервис комментирования DISQUS, который позволяет вам оставлять комментарии на множестве сайтов, имея лишь один аккаунт на Disqus.com.

В случае комментирования в качестве гостя (без регистрации на disqus.com) для публикации комментария требуется время на премодерацию.