Оператор JOIN в SQL

Просматривая вакансии на должность web-разработчика, я увидел, что некоторые компании требуют от своих потенциальных работников знание SQL запросов с применением оператора JOIN. Поэтому появилась идея написать эту статью и показать принцип его работы. Давайте приступим.

Слово «join» в переводе на русский язык означает глагол «объединять». Не трудно догадаться, что созвучный оператор предназначен для объедения нескольких таблиц в одну для последующей манипуляции с результирующими данными. Используется совместно с операторами SELECT, DELETE и UPDATE в предложении FROM. Создадим 3 таблицы, в одной из которых будут храниться страны, во второй, футбольные клубы, которые играют в этих странах, а в третьей информация о футболистах.

CREATE TABLE IF NOT EXISTS country(
`id` INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS club(
`id` INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`country_id` INT(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS player(
`id` INT(3) PRIMARY KEY NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(30) NOT NULL,
`last_name` VARCHAR(30) NOT NULL,
`club_id` INT(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Сразу же наполним их данными.

INSERT INTO country (`name`) VALUES
('England'),
('Italy');
INSERT INTO club (`name`, `country_id`) VALUES
('Liverpool', 1),
('Manchester City', 1),
('Napoli', 2),
('Juventus', 2);
INSERT INTO player (`first_name`, `last_name`, `club_id`) VALUES
('James', 'Milner', 1),
('Jordan', 'Henderson', 1),
('Alexander', 'Zinchenko', 2),
('Kyle', 'Walker', 2),
('Nikola', 'Maksimovic', 3),
('Marek', 'Hamsik', 3),
('Wojciech', 'Szczesny', 4),
('Mattia', 'Perin', 4),
('Sergio', 'Ramos', 5),
('Tony', 'Cross', 5),
('Jasper', 'Cillessen', 6),
('Thomas', 'Vermaelen', 6),
('Roman', 'Burki', 7),
('Marco', 'Reus', 7),
('Leon', 'Goretzka', 8),
('Tomas', 'Muller', 8);

Объединение таблиц может происходить по определённым правилам. Посмотрим по каким именно.

INNER JOIN

Внутреннее объединение, при котором в результат попадут данные нескольких таблиц имеющие пересечение по условию ON. Схематично это выглядит так:

На примере наших таблиц произведём выборку данных.

SELECT * FROM `country`
INNER JOIN `club` ON `country`.id = `club`.country_id
INNER JOIN `player` ON `club`.id = `player`.club_id

Получим:

Замечание: INNER является необязательным и при его отсутствии мы получим тот же самый результат выполнения. Однако для наглядного понимания по какому правилу объединяются таблицы я советую его указывать в вашем запросе.

Результат содержит в себе только те данные, по которым есть пересечение всех 3 таблиц, остальные же данные (игроки) в него не вошли.

OUTER JOIN

Помимо внутреннего объединения, как вы уже могли догадаться, существует внешнее. В свою очередь оно бывает левое внешнее, правое внешнее и полное внешнее. Рассмотрим принцип работы каждого.

LEFT OUTER JOIN

При таком виде объединения таблиц в результат попадают сначала те данные, которые имеют общее пересечение по условию ON, а затем данные из левой таблицы, которые не попали в это пересечение и имеющие соответствующие данные из правой таблицы записанные в виде значения NULL (пустое поле). На изображении ниже представлена вышеизложенная картина.

SELECT * FROM `player`
LEFT OUTER JOIN `club` ON `player`.club_id = `club`.id

RIGHT OUTER JOIN

Объединение происходит аналогично LEFT OUTER JOIN, только в этом случае, после данных, которые имеют общее пересечение попадают данные из правой таблицы, а соответствующим столбцам левой таблицы присвается значение NULL. Для этого случая представлено только изображение.

FULL OUTER JOIN

В результат такого объединения таблиц попадут сначала те данные, которые имеют общее пересечение по условию ON, затем данные из левой таблицы, которые не попали в это пересечение и имеющие соответствующие данные из правой таблицы записанные в виде значения NULL, а затем уже данные из правой таблицы, а соответствующим столбцам левой таблицы присвоется значение NULL.

Так выглядит полное внешнее объединение на изображении.

CROSS JOIN

Последний вид объединения, при котором в результат выборки попадут все возможные варианты, составленные из данных таблиц участвующих в этой операции.

SELECT * FROM `club`
CROSS JOIN `country`

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

Понравилась статья? Поделись: