Узнайте больше о MySQL с нашей заставкой по поиску и обновлению записей MySQL .
Эта статья была написана в 2011 году и остается одной из наших самых популярных публикаций. Если вы хотите больше узнать о MySQL, вы можете найти эту недавнюю статью об администрировании MySQL очень интересной.
«JOIN» — это ключевое слово SQL, используемое для запроса данных из двух или более связанных таблиц. К сожалению, понятие регулярно объясняется с использованием абстрактных терминов или отличается в разных системах баз данных. Это часто смущает меня. Разработчики справляются с достаточно путаницы, так что это моя попытка кратко и кратко объяснить JOIN для себя и всех, кому это интересно.
Связанные таблицы
MySQL, PostgreSQL, Firebird, SQLite, SQL Server и Oracle являются системами реляционных баз данных. Хорошо спроектированная база данных будет содержать несколько таблиц, содержащих связанные данные. Очень простой пример — пользователи (студенты) и зачисления на курсы:
таблица ‘user’:
| Я бы | имя | курс |
|---|---|---|
| 1 | Алиса | 1 |
| 2 | боб | 1 |
| 3 | Каролина | 2 |
| 4 | Дэвид | 5 |
| 5 | Эмма | (ЗНАЧЕНИЕ 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;
Номер курса относится к предмету, взятому в таблице курса …
таблица «курс»:
| Я бы | имя |
|---|---|
| 1 | HTML5 |
| 2 | CSS3 |
| 3 | JavaScript |
| 4 | PHP |
| 5 | MySQL |
Код создания таблицы 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. Это создает набор записей, которые совпадают как в таблицах пользователей, так и в таблицах курса, то есть всех пользователей, которые зарегистрированы в курсе:
SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;
Результат:
| user.name | название курса |
|---|---|
| Алиса | HTML5 |
| боб | HTML5 |
| Carline | CSS3 |
| Дэвид | MySQL |
ЛЕВЫЙ РЕЙТИНГ
Что если нам потребуется список всех студентов и их курсов, даже если они не зачислены на одного? LEFT JOIN создает набор записей, который соответствует каждой записи в левой таблице (пользователь), независимо от какой-либо соответствующей записи в правой таблице (курс):
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id;
Результат:
| user.name | название курса |
|---|---|
| Алиса | HTML5 |
| боб | HTML5 |
| Carline | CSS3 |
| Дэвид | MySQL |
| Эмма | (ЗНАЧЕНИЕ NULL) |
ПРАВИЛЬНОЕ СОЕДИНЕНИЕ
Возможно, нам требуется список всех курсов и студентов, даже если никто не был зачислен? Правое соединение создает набор записей, который соответствует каждой записи в правой таблице (курсе) независимо от какой-либо соответствующей записи в левой таблице (пользователь):
SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;
Результат:
| user.name | название курса |
|---|---|
| Алиса | HTML5 |
| боб | HTML5 |
| Carline | CSS3 |
| (ЗНАЧЕНИЕ NULL) | JavaScript |
| (ЗНАЧЕНИЕ NULL) | PHP |
| Дэвид | MySQL |
ПРАВАЯ СОЕДИНЕНИЕ используется редко, так как вы можете выразить тот же результат, используя ЛЕВОЕ СОЕДИНЕНИЕ. Это может быть более эффективным и быстрым для разбора базы данных:
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;
Результат:
| название курса | кол-() |
|---|---|
| HTML5 | 2 |
| CSS3 | 1 |
| JavaScript | 0 |
| PHP | 0 |
| MySQL | 1 |
ВНЕШНЕЕ СОЕДИНЕНИЕ (или ПОЛНОЕ НАРУЖНОЕ СОЕДИНЕНИЕ)
Наш последний вариант — OUTER JOIN, который возвращает все записи в обеих таблицах независимо от совпадения. Если совпадений не существует, отсутствующая сторона будет содержать NULL.
OUTER JOIN менее полезен, чем INNER, LEFT или RIGHT, и он не реализован в MySQL. Однако вы можете обойти это ограничение, используя СОЮЗ ЛЕВОГО И ПРАВОГО СОЕДИНЕНИЯ, например
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.name | название курса |
|---|---|
| Алиса | HTML5 |
| боб | HTML5 |
| Carline | CSS3 |
| Дэвид | MySQL |
| Эмма | (ЗНАЧЕНИЕ NULL) |
| (ЗНАЧЕНИЕ NULL) | JavaScript |
| (ЗНАЧЕНИЕ NULL) | PHP |
Я надеюсь, что это даст вам лучшее понимание JOIN и поможет вам писать более эффективные SQL-запросы.
Узнайте больше о MySQL с нашей заставкой по поиску и обновлению записей MySQL .
Комментарии к этой статье закрыты. Есть вопрос о MySQL? Почему бы не спросить об этом на наших форумах ?