Задумывались ли Вы когда-нибудь о том, какие вопросы по SQL JOIN Вам могут задать на собеседовании? Насколько Вы подготовлены к ответам на них? В этой статье рассматриваются наиболее распространенные вопросы на собеседовании по SQL JOIN и варианты ответа на них.
Если вы устраиваетесь на работу в качестве аналитика данных или разработчика программного обеспечения, вас, скорее всего, спросят о ваших знаниях в области команд SQL JOIN. Такие команды – излюбленная тема интервьюеров. Существует множество разновидностей операций JOIN, и каждая из них выполняет свою функцию.
Ищите работу Junior QA - тогда вам в наш телеграм канал QA Вакансии. Каждую неделю 7 лучших вакансий с телеграм контактом HR компании.
В этой статье мы подходим к теме с точки зрения собеседования и рассматриваем некоторые наиболее распространенные вопросы по SQL JOIN, с которыми вы можете столкнуться.
Содержание:
- Что такое команда SQL JOIN и когда она используется?
- Как бы вы написали запрос для объединения этих двух таблиц?
- Какие типы JOIN вы знаете ?
- Что такое OUTER JOIN?
- В чем разница между SQL INNER JOIN и SQL LEFT JOIN?
- В чем разница между LEFT JOIN и FULL JOIN?
- Напишите запрос, который объединит две таблицы таким образом, чтобы все строки из таблицы 1 попали в результат.
- Как объединить более двух таблиц?
- Как присоединить таблицу к самой себе?
- Должно ли условие JOIN быть равенством?
1. Что такое команда SQL JOIN и когда она используется?
Команду SQL JOIN применяют для объединения данных из двух таблиц в SQL. Она часто используется в ситуациях, когда таблицы имеют хотя бы один общий столбец данных.
Обычно условием JOIN является равенство столбцов из разных таблиц, но возможны и другие условия. Используя последовательные условия команды можно объединить более двух таблиц,
Существуют различные типы JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN и другие. Работа команды JOIN проиллюстрирована на рисунке ниже:
БОЛЬШЕ ВОПРОСОВ С СОБЕСЕДОВАНИЙ В НАШЕМ ТЕЛЕГРАМ КАНАЛЕ QASOBES
2. Как бы вы написали запрос для объединения этих двух таблиц?
В течение собеседования вам могут предложить применить свои знания на практике, написав команду JOIN. Давайте рассмотрим пример, чтобы вам было проще справиться с этой задачей.
У нас есть две таблицы:
- employees – Эта таблица содержит идентификатор каждого сотрудника, его имя и идентификатор отдела.
| id | employee_name | department_id |
|---|---|---|
| 1 | Homer Simpson | 4 |
| 2 | Ned Flanders | 1 |
| 3 | Barney Gumble | 5 |
| 4 | Clancy Wiggum | 3 |
| 5 | Moe Syzslak | NULL |
- departments – Эта таблица содержит идентификатор и название каждого отдела.
| department_id | department_name |
|---|---|
| 1 | Sales |
| 2 | Engineering |
| 3 | Human Resources |
| 4 | Customer Service |
| 5 | Research And Development |
Если вас попросят объединить таблицы, постарайтесь найти столбец, который является общим для каждой из таблиц. В данном примере это столбец department_id.
SELECT * FROM employees JOIN departments ON employees.department_id = departments.department_id;
Выполнение этого кода приведет к следующему результату:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
Условие ON указывает, как именно следует объединить две таблицы (одну после FROM и вторую после JOIN). В приведенном примере видно, что обе таблицы содержат столбец department_id. Наш SQL-запрос вернет строки, в которых employee.department_id равен department.department_id.
Иногда реляционные поля бывают не столь очевидны. Например, у вас может быть таблица employees с полем id, которое можно объединить с полем employee_id в любой другой таблице.
Вы также можете указать, какие именно столбцы вы хотите вернуть из каждой таблицы, включенной в вашу команду JOIN. Когда вы включаете имя столбца, существующего в обеих таблицах, вы должны указать точную таблицу, из которой хотите его получить.
Мы не можем написать department_id, поскольку это приведет к ошибке двусмысленности в SQL. Мы должны написать employees.department_id или departments.department_id. Рассмотрим пример ниже:
SELECT employees.department_id, employee_name, department_name FROM employees JOIN departments ON employees.department_id = departments.department_id;
Обратите внимание на оператор SELECT. Мы указали точное имя таблицы для столбца department_id, поскольку этот столбец существует в обеих таблицах, составляющих команду JOIN. Для столбцов employee_name и department_name этого делать не нужно, поскольку они уникальны. Выполнение этого SQL-запроса дает следующий результат:
| department_id | employee_name | department_name |
|---|---|---|
| 1 | Ned Flanders | Sales |
| 3 | Clancy Wiggum | Human Resources |
| 4 | Homer Simpson | Customer Service |
| 5 | Barney Gumble | Research And Development |
При написании команд SQL JOIN мы также можем использовать псевдонимы SQL. Имена столбцов могут быть весьма техническими и не очень понятными. Это порой затрудняет понимание вывода запроса. Ниже приведены некоторые правила, которых следует придерживаться при реализации SQL-псевдонимов:
- Чтобы дать столбцу описательное имя, можно использовать псевдоним столбца.
- Чтобы присвоить псевдоним столбцу, используйте ключевое слово AS, за которым указывается псевдоним.
- Если псевдоним содержит пробелы, его необходимо заключить в кавычки.
Псевдоним SQL может применяться как к именам таблиц, так и к именам столбцов. Если мы перепишем наш предыдущий запрос, включив в него псевдоним для каждого имени столбца, он может выглядеть примерно так:
SELECT employees.department_id AS ID, employee_name AS ‘Employee Name’, department_name AS Department FROM employees JOIN departments ON employees.department_id = departments.department_id;
Обратите внимание, что нам пришлось использовать кавычки для столбца ‘Employee Name’, поскольку новое имя содержит пробелы.
Если мы перепишем приведенный выше код, на этот раз используя псевдоним для каждого имени таблицы, то получим следующий результат:
SELECT * FROM employees AS emp JOIN departments AS dep ON emp.department_id = dep.department_id;
Оператор AS, используемый здесь, также является совершенно необязательным. Его можно убрать из запроса. Реализация этого небольшого изменения приведет к тому, что наш код будет выглядеть так:
SELECT * FROM employees emp JOIN departments dep ON emp.department_id = dep.department_id;
Мы рассмотрели всю необходимую информацию по объединению двух таблиц и дали ответы на все вопросы, которые могут возникнуть в связи с основным синтаксисом JOIN.
3. Какие типы JOIN вы знаете ?
Как уже говорилось в начале статьи, существует множество разновидностей оператора SQL JOIN. Демонстрация того, что вы владеете каждой командой, — это один из лучших способов показать ваши знания по данной теме. Вот некоторые из наиболее часто встречающихся типов предложений JOIN:
SQL INNER JOIN
Команда INNER JOIN является стандартной командой JOIN в SQL. Если вы посмотрите на наш предыдущий пример (SELECT * FROM employees JOIN departments), то на самом деле это и был INNER JOIN.
INNER JOIN используется для возврата строк из обеих таблиц, удовлетворяющих заданному условию. Он сопоставляет строки из первой и второй таблиц, удовлетворяющие условию ON.
На этом рисунке показана связь между двумя таблицами, включенными в наше предложение INNER JOIN:
Давайте подробнее рассмотрим синтаксис и функциональность INNER JOIN на практическом примере с использованием двух таблиц – employees и departments, описанных выше.
Следующий SQL-код ищет совпадения между таблицами employees и departments на основе столбца department_id.
SELECT * from employees emp INNER JOIN departments dep ON emp.department_id = dep.department_id;
Выполнение этого кода приведет к такому результату:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
При просмотре таблицы можно заметить, что сотрудник по имени Moe Szyslak отсутствует. В нашей таблице employees у этого сотрудника нет текущего идентификатора отдела (department_id). Поэтому при попытке выполнить JOIN таблицы departments по этому столбцу не было найдено ни одного совпадения. Таким образом, сотрудник исключается из результата. Мы решим эту проблему с помощью следующего типа JOIN – LEFT JOIN.
SQL LEFT JOIN
Подобно оператору INNER JOIN, LEFT JOIN позволяет запрашивать данные из двух таблиц. Но в чем ключевое различие между этими двумя операторами? LEFT JOIN возвращает все строки, которые находятся в первой (левой) таблице. Также возвращаются соответствующие строки из правой таблицы.
При использовании предложения LEFT JOIN выводится общее представление левой и правой таблиц.
На приведенной схеме таблица 1 является левой таблицей, а таблица 2 – правой.
LEFT JOIN выбирает данные, начиная с левой таблицы. При этом каждая строка из левой таблицы сопоставляется со строками из правой таблицы на основании условия, заданного оператором JOIN.
Оператор SQL LEFT JOIN возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений. Это означает, что если в предложении ON нет ни одной записи в правой таблице, то JOIN все равно вернет в результат строку, но со значением NULL в каждом столбце из правой таблицы.
SQL LEFT JOIN возвращает все значения из левой таблицы плюс совпавшие значения из правой таблицы. Если совпадения не найдено, LEFT JOIN возвращает значение NULL.
Синтаксис предложения SQL LEFT JOIN выглядит следующим образом:
SELECT * FROM employees emp LEFT JOIN departments dep ON emp.department_id = dep.department_id;
Мы указываем, что хотим получить LEFT JOIN, что прописывается одинаково для всех типов JOIN. Перед ключевым словом JOIN укажите, какой именно вариант вы хотите использовать.
Ключевое слово ON работает так же, как и в нашем примере с INNER JOIN. Мы ищем совпадающие значения между столбцом department_id нашей таблицы employees и столбцом department_id нашей таблицы departments.
Здесь таблица employees будет выступать в качестве левой таблицы, поскольку это первая таблица, которую мы указываем.
В результате выполнения этого SQL-запроса мы получим следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
Обратите внимание, что сотрудник, Мо Шислак (Moe Szyslak) был включен в итоговую таблицу, несмотря на то, что в таблице departments нет совпадения с department_id. Именно в этом и заключается смысл предложения LEFT JOIN – включить все данные из левой таблицы, независимо от наличия совпадений.
SQL RIGHT JOIN
RIGHT JOIN аналогичен LEFT JOIN, за исключением того, что действия, выполняемые над объединенными таблицами, меняются на противоположные. Это означает, что RIGHT JOIN возвращает все значения из правой таблицы, плюс совпадающие значения из левой таблицы или NULL в случае отсутствия совпадающего предиката JOIN.
На приведенной ниже схеме таблица 2 – это правая таблица, а таблица 1 – левая:
Мы применяем следующий запрос к таблицам employee и departments:
SELECT * FROM employees emp RIGHT JOIN departments dep ON emp.department_id = dep.department_id;
Синтаксис аналогичен синтаксису LEFT JOIN. Мы указываем, что хотим выполнить RIGHT JOIN, для поиска совпадений между таблицей departments и таблицей employees.
Здесь таблица employee будет выступать в качестве левой таблицы, поскольку это первая таблица, которую мы указываем. Таблица departments будет правой таблицей. В результате выполнения этого запроса SQL JOIN будет получен следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 2 | Ned Flanders | 1 | 1 | Sales |
| NULL | NULL | NULL | 2 | Engineering |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
Оператор RIGHT JOIN начинает выборку данных из правой таблицы (departments). При этом каждая строка из правой таблицы сопоставляется с каждой строкой из левой таблицы. Если в обеих строках условие JOIN оценивается как истинное, то столбцы объединяются в новую строку и эта новая строка включается в набор результатов.
SQL FULL JOIN
SQL FULL JOIN объединяет результаты левых и правых внешних объединений. Объединенная таблица будет содержать все записи из обеих таблиц и заполнится значениями NULL для отсутствующих совпадений с обеих сторон.
Следует иметь в виду, что в результате FULL JOIN может получиться очень большой набор данных. При полном объединении возвращаются все строки из объединенных таблиц, независимо от того, совпадают они или нет.
SQL FULL JOIN является разновидностью OUTER JOIN (мы рассмотрим его позже), поэтому его также можно называть FULL OUTER JOIN.
Ниже представлена наглядная иллюстрация концепции SQL FULL JOIN:
Обратите внимание, что в нашей диаграмме возвращается каждый ряд из обеих таблиц.
Рассмотрим синтаксис оператора SQL FULL JOIN на примере кода.
SELECT * FROM employees emp FULL JOIN departments dep ON emp.department_id = dep.department_id;
При выполнении этого SQL-запроса к таблицам employees и departments получается следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
| 2 | Ned Flanders | 1 | 1 | Sales |
| NULL | NULL | 2 | Engineering | |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
Если сравнить этот результат с результатами вышеописанных LEFT JOIN и RIGHT JOIN, можно увидеть, что эти данные представляют собой комбинацию таблиц, полученных в наших предыдущих примерах. Этот тип предложения JOIN позволяет получить весьма обширный набор данных. Хорошо подумайте, прежде чем его использовать.
CROSS JOIN
Оператор SQL CROSS JOIN используется, когда нужно выяснить все возможности объединения двух таблиц, где набор результатов включает в себя каждую строку из каждой участвующей таблицы. CROSS JOIN возвращает декартово произведение строк из объединенных таблиц.
Приведенная ниже диаграмма хорошо иллюстрирует процесс объединения строк:
При использовании CROSS JOIN получается набор результатов, размер которого равен количеству строк в первой таблице, умноженному на количество строк во второй таблице. Такой результат называется декартовым продуктом двух таблиц (Таблица 1 x Таблица 2).
Рассмотрим две наши таблицы:
- Таблица employees
| id | employee_name | department_id |
|---|---|---|
| 1 | Homer Simpson | 4 |
| 2 | Ned Flanders | 1 |
| 3 | Barney Gumble | 5 |
| 4 | Clancy Wiggum | 3 |
| 5 | Moe Syzslak | NULL |
- Таблица departments
| department_id | department_name |
|---|---|
| 1 | Sales |
| 2 | Engineering |
| 3 | Human Resources |
| 4 | Customer Service |
| 5 | Research And Development |
Чтобы выполнить CROSS JOIN с использованием этих таблиц, мы должны написать SQL-запрос следующим образом:
SELECT * FROM employees CROSS JOIN departments;
Обратите внимание, что в CROSS JOIN не используется ON или USING. Этим он отличается от рассмотренных нами ранее вариаций JOIN.
После выполнения CROSS JOIN мы получим следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 1 | Sales |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 1 | Sales |
| 4 | Clancy Wiggum | 3 | 1 | Sales |
| 5 | Moe Szyslak | NULL | 1 | Sales |
| 1 | Homer Simpson | 4 | 2 | Engineering |
| 2 | Ned Flanders | 1 | 2 | Engineering |
| 3 | Barney Gumble | 5 | 2 | Engineering |
| 4 | Clancy Wiggum | 3 | 2 | Engineering |
| 5 | Moe Szyslak | NULL | 2 | Engineering |
| 1 | Homer Simpson | 4 | 3 | Human Resources |
| 2 | Ned Flanders | 1 | 3 | Human Resources |
| 3 | Barney Gumble | 5 | 3 | Human Resources |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | 3 | Human Resources |
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 4 | Customer Service |
| 3 | Barney Gumble | 5 | 4 | Customer Service |
| 4 | Clancy Wiggum | 3 | 4 | Customer Service |
| 5 | Moe Szyslak | NULL | 4 | Customer Service |
| 1 | Homer Simpson | 4 | 5 | Research And Development |
| 2 | Ned Flanders | 1 | 5 | Research And Development |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 5 | Research And Development |
| 5 | Moe Szyslak | NULL | 5 | Research And Development |
Наш результат содержит все возможные комбинации между двумя таблицами. Даже если используемые таблицы содержат мало данных, как, например, наши таблицы employees и departments, они могут дать огромный набор результатов, если их использовать в сочетании с предложением SQL CROSS JOIN.
SQL NATURAL JOIN
NATURAL JOIN – это тип JOIN, который объединяет таблицы на основе столбцов с одинаковым именем и типом данных. При использовании NATURAL JOIN создается неявное предложение JOIN, основанное на общих столбцах двух объединяемых таблиц.
Общие столбцы – это столбцы, которые имеют одинаковое имя в обеих таблицах. Не обязательно указывать имена столбцов для объединения, поскольку итоговая таблица и так не будет содержать повторяющихся столбцов.
Синтаксис NATURAL JOIN достаточно прост:
SELECT * FROM employees NATURAL JOIN departments;
При выполнении этого запроса будет получен следующий результат:
| department_id | id | employee_name | department_name |
|---|---|---|---|
| 1 | 2 | Ned Flanders | Sales |
| 3 | 4 | Clancy Wiggum | Human Resources |
| 4 | 1 | Homer Simpson | Customer Service |
| 5 | 3 | Barney Gumble | Research And Development |
NATURAL JOIN выполняется по столбцу, который является общим для обеих таблиц. В данном случае это department_id, который отображается в нашем результате только один раз.
4. Что такое OUTER JOIN?
С помощью SQL OUTER JOIN можно вернуть несовпадающие строки в одной или обеих таблицах. Существует несколько разновидностей этого оператора, некоторые из которых мы уже рассматривали выше. Далее перечислены распространенные типы предложений OUTER JOIN:
LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOIN
LEFT JOIN является синонимом LEFT OUTER JOIN. Функциональность обоих типов одинакова. Кстати, это может быть одним из вопросов по SQL JOIN на собеседовании! То же самое можно сказать о RIGHT JOIN и RIGHT OUTER JOIN, а также о FULL JOIN и FULL OUTER JOIN. Рассмотрим пример каждого из них.
SQL LEFT OUTER JOIN
Используйте LEFT OUTER JOIN, если вам нужны все результаты, находящиеся в первой таблице. LEFT OUTER JOIN вернет из второй таблицы только совпадающие строки .
Синтаксис предложения LEFT OUTER JOIN следующий:
SELECT * FROM employees emp LEFT OUTER JOIN departments dep ON emp.department_id = dep.department_id;
В результате выполнения этого SQL-запроса будет получен следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
Обратите внимание, что сотрудник по имени Moe Syzslak был включен в итоговую таблицу, несмотря на то, что в таблице departments нет совпадения с department_id. Именно в этом и заключается смысл предложения LEFT OUTER JOIN – включить все данные из левой таблицы, независимо от наличия совпадений.
SQL RIGHT OUTER JOIN
RIGHT OUTER JOIN аналогичен LEFT OUTER JOIN, за исключением того, что действие, выполняемое над объединенными таблицами, является обратным. Это означает, что RIGHT OUTER JOIN возвращает все значения из правой таблицы, плюс совпавшие значения из левой таблицы или NULL в случае отсутствия совпадений.
Если мы применим RIGHT OUTER JOIN к таблицам employees и departments, то код будет выглядеть следующим образом:
SELECT * FROM employees emp RIGHT OUTER JOIN departments dep ON emp.department_id = dep.department_id;
Здесь таблица employees будет выступать в качестве левой таблицы, поскольку это первая таблица, которую мы указываем.
В результате выполнения этого SQL-запроса будет получен следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 2 | Ned Flanders | 1 | 1 | Sales |
| NULL | NULL | NULL | 2 | Engineering |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
RIGHT OUTER JOIN начинает выборку данных из правой таблицы, в нашем случае из таблицы departments. При этом каждая строка из правой таблицы сопоставляется с каждой строкой из левой таблицы. Если в обеих строках условие JOIN оценивается как истинное, то столбцы объединяются в новую строку и эта строка включается в итоговую таблицу.
SQL FULL OUTER JOIN
SQL FULL OUTER JOIN объединяет результаты левого и правого внешних объединений. В итоге таблица будет содержать все записи из обеих таблиц и заполнять отсутствующие совпадения с обеих сторон значением NULL. В результате FULL OUTER JOIN возвращает все строки из объединенных таблиц, независимо от того, совпадают они или нет.
Рассмотрим синтаксис предложения SQL FULL OUTER JOIN:
SELECT * FROM employees emp FULL OUTER JOIN departments dep ON emp.department_id = dep.department_id;
При выполнении этого SQL-запроса к таблицам employees и departments получается следующий результат:
| d | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
| 2 | Ned Flanders | 1 | 1 | Sales |
| NULL | NULL | 2 | Engineering | |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 3 | Barney Gumble | 5 | 5 | Research And Development |
Обратите внимание, что этот набор данных представляет собой комбинацию наших предыдущих запросов LEFT OUTER JOIN и RIGHT OUTER JOIN.
5. В чем разница между SQL INNER JOIN и SQL LEFT JOIN?
Следует помнить о некоторых ключевых различиях между этими вариантами JOIN. INNER JOIN возвращает строки, если в обеих таблицах есть совпадения. LEFT JOIN возвращает все строки из левой таблицы и все совпадающие строки из правой таблицы.
Рассмотрим эти различия на практическом примере, чтобы вы могли уверенно ответить на этот вопрос на собеседовании.
Допустим, у нас есть две таблицы:
- employees – Эта таблица содержит идентификатор каждого сотрудника, его имя и идентификатор отдела.
| id | employee_name | department_id |
|---|---|---|
| 1 | Homer Simpson | 4 |
| 2 | Ned Flanders | 1 |
| 3 | Barney Gumble | 5 |
| 4 | Clancy Wiggum | 3 |
| 5 | Moe Syzslak | NULL |
- departments – Эта таблица содержит идентификатор и название каждого отдела.
| department_id | department_name |
|---|---|
| 1 | Sales |
| 2 | Engineering |
| 3 | Human Resources |
| 4 | Customer Service |
| 5 | Research and Development |
Следующий SQL-код ищет соответствия между таблицами employees и departments на основе столбца department_id:
SELECT * from employees emp INNER JOIN departments dep ON emp.department_id = dep.department_id;
Выполнение этого кода приведет к следующему результату:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research and Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
При просмотре результата можно заметить, что сотрудник Moe Szyslak отсутствует. В таблице employees этот сотрудник не имеет текущего department_id. Поэтому при попытке присоединиться к таблице departments по этому столбцу не было найдено ни одного совпадения. Таким образом, сотрудник исключается из результата.
Теперь давайте воспользуемся LEFT JOIN и посмотрим, каким будет результат. В SQL LEFT JOIN возвращаются все значения из левой таблицы плюс совпадающие значения из правой таблицы. Если совпадения не найдено, LEFT JOIN возвращает значение NULL.
Синтаксис нашего предложения SQL LEFT JOIN выглядит следующим образом:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research and Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
В данном случае Moe Szyslak был включен в этот набор результатов, несмотря на то, что в таблице departments нет совпадения с department_id. Именно в этом и заключается смысл LEFT JOIN – включить все данные из левой таблицы, независимо от того, были ли найдены совпадения.
6. В чем разница между LEFT JOIN и FULL JOIN?
Это один из популярных вопросов по SQL JOIN, с которым вы можете столкнуться в процессе собеседования.
Как мы уже говорили, SQL LEFT JOIN возвращает все значения из левой таблицы плюс совпадающие значения из правой таблицы. Если совпадения не найдено, LEFT JOIN возвращает значение NULL. SQL FULL JOIN возвращает все строки из объединенных таблиц, независимо от того, совпали они или нет. По сути, он объединяет в себе функциональность LEFT JOIN и RIGHT JOIN.
Давайте сравним набор результатов, полученных с помощью предложения LEFT JOIN, с набором результатов, полученных с помощью FULL JOIN.
Ниже приведен запрос, в котором используется LEFT JOIN:
SELECT * FROM employees emp LEFT JOIN departments dep ON emp.department_id = dep.department_id;
Здесь в качестве левой таблицы будет выступать таблица employees, поскольку это первая таблица, которую мы указываем.
Результат выполнения этого SQL-запроса следующий:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research and Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
Рассмотрим, чем он отличается от SQL FULL JOIN. Синтаксис аналогичен, что демонстрирует данный код:
SELECT * FROM employees emp FULL JOIN departments dep ON emp.department_id = dep.department_id;
При выполнении этого SQL-запроса к таблицам employees и departments получается следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research and Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
| NULL | NULL | NULL | 2 | Engineering |
Сравните этот набор результатов с результатами наших запросов LEFT JOIN и RIGHT JOIN. Легко заметить, что для отдела Engineering не было найдено ни одного совпадения, но данные все равно были возвращены. Этот специфический тип предложения JOIN позволяет получить обширный набор данных.
7. Напишите запрос, который объединит две таблицы таким образом, чтобы в результат попали все строки из таблицы 1.
На собеседовании при приеме на должность аналитика данных или разработчика программного обеспечения вас могут попросить решить техническую задачу, связанную с SQL. Самым распространенным заданием является написание запроса, который соединяет две таблицы определенным образом. Представим, что вас просят написать запрос, который объединит две таблицы таким образом, чтобы в результате были получены все строки из таблицы 1.
Прежде всего, необходимо понять концепцию правых и левых таблиц.
На приведенной схеме таблица 1 – это левая таблица, а таблица 2 – правая. Другими словами, левая таблица стоит на первом месте в запросе; она получила свое название из-за того, что находится слева от условия объединения. Правая таблица идет после ключевого слова JOIN.
Оператор LEFT JOIN выбирает данные, начиная с левой таблицы. Она сопоставляет каждую строку из левой таблицы со строками из правой таблицы, исходя из условия JOIN. Возвращаются все значения из левой таблицы плюс совпавшие значения из правой таблицы. Если совпадения не найдено, LEFT JOIN возвращает значение NULL. Это означает, что если в предложении ON не найдено ни одной записи в правой таблице, то JOIN все равно вернет эту строку, но со значением NULL в каждом столбце правой таблицы.
В нашем практическом примере мы будем использовать уже известные нам таблицы employees и departments:
- employees – Эта таблица содержит идентификатор, имя, фамилию и идентификатор отдела каждого сотрудника.
| id | employee_name | department_id |
|---|---|---|
| 1 | Homer Simpson | 4 |
| 2 | Ned Flanders | 1 |
| 3 | Barney Gumble | 5 |
| 4 | Clancy Wiggum | 3 |
| 5 | Moe Syzslak | NULL |
- departments – Эта таблица содержит идентификатор и название каждого отдела.
| department_id | department_name |
|---|---|
| 1 | Sales |
| 2 | Engineering |
| 3 | Human Resources |
| 4 | Customer Service |
| 5 | Research and Development |
Если мы хотим сохранить все строки из таблицы 1 (в данном случае – employees), мы должны указать ее в качестве левой таблицы.
Синтаксис этого предложения LEFT JOIN следующий:
SELECT * FROM employees emp LEFT JOIN departments dep ON emp.department_id = dep.department_id;
Выполнение этого запроса дает следующий результат:
| id | employee_name | department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | Homer Simpson | 4 | 4 | Customer Service |
| 2 | Ned Flanders | 1 | 1 | Sales |
| 3 | Barney Gumble | 5 | 5 | Research and Development |
| 4 | Clancy Wiggum | 3 | 3 | Human Resources |
| 5 | Moe Szyslak | NULL | NULL | NULL |
Обратите внимание, что сотрудник Moe Szyslak был включен в этот набор данных, несмотря на то, что в таблице departments нет совпадающего идентификатора department_id. Именно в этом и заключается смысл предложения LEFT JOIN – включить все данные из левой таблицы, независимо от того, были ли найдены совпадения в правой таблице.
8. Как объединить более двух таблиц?
Объединение более двух таблиц в одном SQL-запросе может быть довольно сложной задачей для новичков в этой сфере. Следующий пример поможет прояснить ситуацию.
JOIN выполняется для более чем двух таблиц, когда данные, которые вы хотите включить в результат, существуют в трех или более таблицах. Многотабличное соединение требует последовательных операций JOIN: сначала соединяются первая и вторая таблицы и получается виртуальный набор результатов, а затем к этой виртуальной таблице присоединяется другая таблица. Рассмотрим пример.
Для примера множественного JOIN представим, что у нас есть три таблицы:
departments – Эта таблица содержит идентификатор и название каждого отдела.
| department_id | department_name |
|---|---|
| 1 | Sales |
| 2 | Engineering |
| 3 | Human Resources |
| 4 | Customer Service |
| 5 | Research and Development |
office – В этой таблице содержится адрес каждого офиса.
| id | address |
|---|---|
| 1 | 5 Wisteria Lane, Springfield, USA |
| 2 | 124 Chestmount Street, Springfield, USA |
| 3 | 6610 Bronzeway, Springfield, USA |
| 4 | 532 Executive Lane, Springfield, USA |
| 5 | 10 Meadow View, Springfield, USA |
department_office – Эта таблица связывает информацию об офисе с соответствующим отделом. Отделы могут включать в себя несколько офисов.
| office_id | department_id |
|---|---|
| 1 | 1 |
| 2 | 3 |
| 3 | 2 |
| 4 | 4 |
| 5 | 5 |
| 2 | 1 |
| 5 | 1 |
| 4 | 3 |
В нашем случае мы использовали таблицу связей department_office, которая связывает или соотносит отделы с офисами.
Чтобы написать SQL-запрос, который выводит атрибуты department_name и address рядом друг с другом, нам необходимо объединить три таблицы:
- Первый оператор JOIN соединит отделы и department_office и создаст временную таблицу, которая будет содержать столбец office_id.
- Второй оператор JOIN соединит эту временную таблицу с таблицей office по столбцу office_id, чтобы получить желаемый результат.
Рассмотрим приведенный ниже SQL-запрос:
SELECT department_name, address FROM departments d JOIN department_office do ON d.department_id=do.department_id JOIN office o ON do.office_id=o.id;
Нам нужно получить только два столбца – название отдела и связанный с ним адрес. Мы присоединяемся к таблице department_office, которая имеет связь с таблицами department и office. Это позволяет нам затем присоединиться к таблице office, которая содержит столбец адреса в нашем операторе SELECT.
Выполнение этого кода дает следующий набор результатов:
| department_name | address |
|---|---|
| Sales | 5 Wisteria Lane, Springfield, USA |
| Engineering | 124 Chestmount Street, Springfield, USA |
| Human Resources | 6610 Bronzeway, Springfield, USA |
| Customer Service | 532 Executive Lane, Springfield, USA |
| Research and Development | 10 Meadow View, Springfield, USA |
| Sales | 124 Chestmount Street, Springfield, USA |
| Sales | 10 Meadow View, Springfield, USA |
| Human Resources | 532 Executive Lane, Springfield, USA |
Вот и все! Мы получили желаемый результат – каждый отдел и соответствующий ему адрес. Обратите внимание, что самым крупным является отдел продаж, который охватывает три разных офиса. Второй по величине отдел – отдел кадров, который охватывает два разных офиса.
Вы видите, как можно использовать предложение JOIN для нескольких таблиц, чтобы создать связи между таблицами, имеющими общие столбцы. Существует множество различных ситуаций, когда объединение нескольких таблиц может быть полезным.
9. Как присоединить таблицу к самой себе?
Многие начинающие пользователи даже не знают, что таблицу можно присоединить к самой себе. Такую операцию обычно называют самоприсоединением. Она полезна при запросах к иерархическим данным или при сравнении строк в одной таблице. При использовании самоприсоединения важно использовать SQL-псевдоним для каждой таблицы.
Для нашего примера мы будем использовать следующую таблицу:
employee – В этой таблице хранятся имена всех сотрудников компании, идентификаторы их отделов и идентификаторы их руководителей.
| id | employee_name | department_id | manager_id |
|---|---|---|---|
| 1 | Montgomery Burns | 4 | NULL |
| 2 | Waylon Smithers | 1 | 1 |
| 3 | Homer Simpson | 2 | 1 |
| 4 | Carl Carlson | 5 | 1 |
| 5 | Lenny Leonard | 3 | 1 |
| 6 | Frank Grimes | 2 | 3 |
Допустим, мы хотим получить набор результатов, в котором будут показаны только сотрудники с их руководителями. Это можно легко сделать с помощью псевдонимов таблиц в сочетании с самоприсоединением. Мы будем использовать SQL LEFT JOIN. Посмотрите на приведенный ниже код:
SELECT e.employee_name AS 'Employee', m.employee_name AS 'Manager' FROM employee e LEFT JOIN employee m ON m.id = e.manager_id
Остерегайтесь ошибки двусмысленного столбца, которая может легко возникнуть, если вы не будете внимательны при написании такого запроса. Чтобы ее избежать, необходимо правильно использовать псевдонимы SQL, т.е. присваивать псевдоним каждому вхождению таблицы в SQL-запрос. Это легко демонстрируется следующим фрагментом приведенного выше запроса:
FROM employee e LEFT JOIN employee m
Имена столбцов также должны быть снабжены псевдонимом таблицы, чтобы было понятно, на какую таблицу ссылается каждый столбец. Мы явно указали e.employee_name и m.employee_name.
Эти правила помогут успешно выполнить SQL-запрос с самоприсоединением и избежать ошибок.
Выполнение приведенного выше запроса дает следующий результат:
| Employee | Manager |
|---|---|
| Montgomery Burns | NULL |
| Waylon Smithers | Montgomery Burns |
| Homer Simpson | Montgomery Burns |
| Carl Carlson | Montgomery Burns |
| Lenny Leonard | Montgomery Burns |
| Frank Grimes | Homer Simpson |
Все получилось! Вы можете четко видеть каждого сотрудника и соответствующего ему менеджера. Большинство сотрудников подчиняются мистеру Бернсу, хотя менеджером Фрэнка Граймса является Гомер Симпсон. Обратите внимание на значение NULL в столбце Manager для Монтгомери Бернса. Дело в том, что у Монтгомери Бернса нет менеджера – он сам себе начальник.
Давайте немного изменим запрос и на этот раз используем INNER JOIN:
SELECT e.employee_name AS 'Employee', m.employee_name AS 'Manager' FROM employee e INNER JOIN tbl_employee m ON m.id = e.manager_id
| Employee | Manager |
|---|---|
| Waylon Smithers | Montgomery Burns |
| Homer Simpson | Montgomery Burns |
| Carl Carlson | Montgomery Burns |
| Lenny Leonard | Montgomery Burns |
| Frank Grimes | Homer Simpson |
Единственным существенным отличием является отсутствие Montgomery Burns в столбце Employee. Это объясняется тем, что значение manager_id для него было NULL; INNER JOIN возвращает только совпадающие столбцы, при этом NULL-значения исключаются.
10. Должно ли условие JOIN быть равенством?
Неравным соединением считается любое предложение JOIN, в котором в качестве условия JOIN не используется равенство ( = ). В сочетании с условиями соединения можно использовать обычные операторы сравнения (например, <, >, <=, >=, != и <>). Также можно использовать оператор BETWEEN.
Существует множество ситуаций, когда неравные соединения могут оказаться полезными, в том числе для перечисления уникальных пар, записей в диапазоне и выявления дубликатов. Рассмотрим наш последний пример и узнаем, как выявить дубликаты.
Сначала посмотрим на данные, которые мы будем запрашивать. В данном примере мы будем использовать только одну таблицу, хорошо знакомую нам employee:
| id | employee_name | department_id | manager_id |
|---|---|---|---|
| 1 | Montgomery Burns | 4 | NULL |
| 2 | Waylon Smithers | 1 | 1 |
| 3 | Homer Simpson | 2 | 1 |
| 4 | Carl Carlson | 5 | 1 |
| 5 | Lenny Leonard | 3 | 1 |
| 6 | Frank Grimes | 2 | 3 |
| 7 | Lenny Leonard | 3 | 1 |
Если бы мы хотели быстро идентифицировать любые повторяющиеся значения, мы бы написали следующий запрос:
SELECT e1.id, e1.employee_name, e2.id, e2.employee_name FROM employee e1 JOIN employee e2 ON e1.employee_name = e2.employee_name AND e1.id < e2.id
Присмотревшись к предложению JOIN, мы увидим, что оно имеет два условия:
- Оно сопоставляет записи с одинаковыми именами.
- Оно извлекает записи, ID которых меньше ID временной самоприсоединенной таблицы.
Выполнение этого запроса дает следующий набор результатов:
| id | employee_name | id | employee_name |
|---|---|---|---|
| 5 | Lenny Leonard | 7 | Lenny Leonard |
Мы видим, что в таблице есть повторяющаяся запись Lenny Leonard . Дубликаты могут привести к непредсказуемым ошибкам и испортить данные в отчетах.
Это лишь один из многих возможных примеров, демонстрирующих полезность неравных объединений.
Перевод статьи «The Top 10 SQL JOIN Interview Questions and How to Answer Them».