Вопросы на собеседовании Java программиста. Разница между LEFT JOIN, RIGHT JOIN и INNER JOIN

Программирование на java не отделимо от работы с базой данных. Работая с базой данных и извлекая данные из таблиц часто приходиться соединять таблицы между собой для того, чтобы получить нужную информацию. Cоединять таблицы в SQL можно не просто с помощью оператора JOIN. Оказывается существуют еще и INNER JOIN, LEFT JOIN и RIGHT JOIN. Поэтому очень вероятно, что на собеседовании вас спросят, в чем разница между опрераторами INNER JOIN, LEFT JOIN и RIGHT JOIN. Дак вот, о том, в чем же разница между этими операторами и какие тонкости существуют в их применении, я и хочу рассказать в этой статье. 

Чтобы выяснить это и наглядно проилюстрировать разницу, давайте создадим две таблицы: таблицу Сотрудники (EMPLOYERS) и таблицу Отделы (DEPARTMENTS). Все таблицы созданы в СУБД PostgreSQL 9.1
В таблицу сотрудники добавим три отдела.

CREATE TABLE departments
(
  department_id SERIAL,
  departmentName VARCHAR(255) NOT NULL,
  chief VARCHAR(255) NOT NULL,
  speciality VARCHAR(255) NOT NULL,
  primary key (department_id)
)

INSERT INTO departments  (departmentName, chief, speciality) 
VALUES ('Бухгалтерия', 'Иванов Иван Петрович', 'Финансовый учет');
 
INSERT INTO departments  (departmentName, chief, speciality) 
VALUES ('Отдел кадров', 'Степанова Лидия Ивановна', 'Управление персоналом');
 
INSERT INTO departments  (departmentName, chief, speciality) 
VALUES ('Отдел информационных технологий', 'Смирнов Игорь Васильевич', 'Информационные технологии');

Добавим сотрудников. В первом отделе (department_id = 1) будет 3 сотрудника, во втором отеделе  (department_id = 2) будет 4 сотрудника в третьем отделе не будет ни одного сотрудника и три сотрудника не будут принадлежать ни одному отделу.

CREATE TABLE employers
(
  employee_id SERIAL,
  firstName VARCHAR(255),
  surName VARCHAR(255),
  patronymic VARCHAR(255),
  dateOfBirth DATE NOT NULL,
  sex CHAR(1),
  department_id int references departments(department_id),
  primary key (employee_id)
)

INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Иван', 'Дмитриев', 'Владимирович','1982-03-12', 'M', 1);
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex) 
VALUES ('Степан', 'Сколов', 'Иванович','1987-03-12', 'M');
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex) 
VALUES ('Петр', 'Кремлев', 'Сергеевич','1978-05-18', 'M');
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex) 
VALUES ('Татьяна', 'Васильева', 'Алексеевна','1988-06-14', 'Ж');
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Григорий', 'Камаров', 'Владимирович','1987-06-22', 'M', 1);
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Дмитрий', 'Голицин', 'Анатольевич','1986-08-15', 'M', 1);
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Алексей', 'Кузнецов', 'Иванович','1981-03-11', 'M', 2);
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Дмитрий', 'Карнаков', 'Владимирович','1982-04-12', 'M', 2);
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Антон', 'Казаков', 'Сергеевич','1983-05-13', 'M', 2);
 
INSERT INTO employers (firstname, surname, patronymic, dateofbirth, sex, department_id) 
VALUES ('Кирилл', 'Ульянов', 'Константинович','1984-06-14', 'M', 2);

Получили две таблицы Employers и Departments. Таблицы связаны между собой по ключу department_id - который является первичным ключом в таблице Departments и внешним ключом в таблице Employers, то есть связывает сотрудников и отделы.

INNER JOIN и JOIN

Ну вот теперь, когда обе таблицы созданы, можно начинать проводить эксперименты по их соединению. Соединять таблицы будем по полю department_id - оно является первичным ключом в таблице departments и внешним ключом в таблице employers. Сначала соединим таблицы использую INNER JOIN, а потом просто JOIN.

INNER JOIN

SELECT 
  employers.employee_id,
  employers.firstname,
  employers.surname,
  departments.department_id,
  departments.departmentname 
FROM employers
inner join departments ON employers.department_id = departments.department_id

JOIN

SELECT 
  employers.employee_id,
  employers.firstname,
  employers.surname,
  departments.department_id,
  departments.departmentname 
FROM employers
join departments ON employers.department_id = departments.department_id

Видим что разницы между результатами работы INNER JOIN  и простым JOIN нет никакой. 

При соединении таблиц будут отобраны только те строки, которые имеют значения в столбце department_id в обоих таблицах.  То есть будут отобраны только отделы, которые имеют сотрудников и сотрудники, которые состоят в отделах. То есть только пересечения.

LEFT JOIN

Соединим таблицы используя оператор LEFT JOIN. Присоединим к таблице employers таблицу departments. 

SELECT 
  employers.employee_id,
  employers.firstname,
  employers.surname,
  departments.department_id 
FROM employers
left join departments ON employers.department_id = departments.department_id

Из результатов выполнения SQL запроса видно, что будут извлечены все значения из таблицы которая находится слева (то есть из таблицы employers), в том числе и те которые не имеют соответствующего значения department_id связыващего их с таблицей  departments, то есть будут извлечены все сотрудники, даже те, которые не состоят ни водном отделе. 

Поменяем таблицы местами теперь к таблице departments присоеденим таблицу employers. 

SELECT 
  employers.employee_id,
  employers.firstname,
  employers.surname,
  departments.department_id 
FROM departments
left join employers ON employers.department_id = departments.department_id 

Произошла аналогичная ситуация. Из таблицы которая находится слева (то есть из таблицы departments) будут извлечены все значения, в том числе и те, которые не связаны ни одной строкой из таблицы employers, то есть будут извлечены все отделы, даже те которые не содержат ни одного сотрудника (что наглядно видно рисунке - в отделе с department_id  = 3 нет ни одного сотрудника)

RIGHT JOIN

Соединим таблицы используя оператор RIGHT JOIN. 

SELECT 
  employers.employee_id,
  employers.firstname,
  employers.surname,
  departments.department_id 
FROM employers
right join departments ON employers.department_id = departments.department_id 

По результатам видим, что для таблицы, которая присоединяется, это таблица departments (то есть как бы находится справа), будут извлечены все значения, даже те значения которые отсутствуют в таблице employers, то есть все отделы, даже те которые не содержат ни одного сотрудника.

Поменяем таблицы местами, теперь к таблице departments присоеденим таблицу employers. 

SELECT 
  employers.employee_id,
  employers.firstname,
  employers.surname,
  departments.department_id 
FROM departments 
right join employers ON employers.department_id = departments.department_id 

Если же к таблице departments присоединить справа таблицу employers, то из таблицы employers будут извлечены все значения в том числе и те, которые не связаны с таблицей departments, то есть даже те сотрудники которые не состоят ни в одном отделе.