10 вопросов на собеседовании по SQL JOIN с ответами и примерами

Задумывались ли Вы когда-нибудь о том, какие вопросы по SQL JOIN Вам могут задать на собеседовании? Насколько Вы подготовлены к ответам на них? В этой статье рассматриваются наиболее распространенные вопросы на собеседовании по SQL JOIN и варианты ответа на них.

Если вы устраиваетесь на работу в качестве аналитика данных или разработчика программного обеспечения, вас, скорее всего, спросят о ваших знаниях в области команд SQL JOIN. Такие команды – излюбленная тема интервьюеров. Существует множество разновидностей операций JOIN, и каждая из них выполняет свою функцию.

Ищите работу Junior QA - тогда вам в наш телеграм канал QA Вакансии. 
Каждую неделю 7 лучших вакансий с телеграм контактом HR компании. 

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

Содержание:

1. Что такое команда SQL JOIN и когда она используется?

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

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

Существуют различные типы JOIN: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN и другие. Работа команды JOIN проиллюстрирована на рисунке ниже:

БОЛЬШЕ ВОПРОСОВ С СОБЕСЕДОВАНИЙ В НАШЕМ ТЕЛЕГРАМ КАНАЛЕ QASOBES

2. Как бы вы написали запрос для объединения этих двух таблиц?

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

У нас есть две таблицы:

  • employees – Эта таблица содержит идентификатор каждого сотрудника, его имя и идентификатор отдела.
idemployee_namedepartment_id
1Homer Simpson4
2Ned Flanders1
3Barney Gumble5
4Clancy Wiggum3
5Moe SyzslakNULL
  • departments – Эта таблица содержит идентификатор и название каждого отдела.
department_iddepartment_name
1Sales
2Engineering
3Human Resources
4Customer Service
5Research And Development

Если вас попросят объединить таблицы, постарайтесь найти столбец, который является общим для каждой из таблиц. В данном примере это столбец department_id.

SELECT *
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;

Выполнение этого кода приведет к следующему результату:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research And Development
4Clancy Wiggum33Human 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_idemployee_namedepartment_name
1Ned FlandersSales
3Clancy WiggumHuman Resources
4Homer SimpsonCustomer Service
5Barney GumbleResearch 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;

Выполнение этого кода приведет к такому результату:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research And Development
4Clancy Wiggum33Human Resources

При просмотре таблицы можно заметить, что сотрудник по имени Moe Szyslak отсутствует. В нашей таблице employees у этого сотрудника нет текущего идентификатора отдела (department_id). Поэтому при попытке выполнить JOIN таблицы departments по этому столбцу не было найдено ни одного совпадения. Таким образом, сотрудник исключается из результата. Мы решим эту проблему с помощью следующего типа JOINLEFT 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-запроса мы получим следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research And Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL

Обратите внимание, что сотрудник, Мо Шислак (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 будет получен следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
2Ned Flanders11Sales
NULLNULLNULL2Engineering
4Clancy Wiggum33Human Resources
1Homer Simpson44Customer Service
3Barney Gumble55Research 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 получается следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research And Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL
2Ned Flanders11Sales
NULLNULL2Engineering
4Clancy Wiggum33Human Resources
1Homer Simpson44Customer Service
3Barney Gumble55Research And Development

Если сравнить этот результат с результатами вышеописанных LEFT JOIN и RIGHT JOIN, можно увидеть, что эти данные представляют собой комбинацию таблиц, полученных в наших предыдущих примерах. Этот тип предложения JOIN позволяет получить весьма обширный набор данных. Хорошо подумайте, прежде чем его использовать.

CROSS JOIN

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

Приведенная ниже диаграмма хорошо иллюстрирует процесс объединения строк:

При использовании CROSS JOIN получается набор результатов, размер которого равен количеству строк в первой таблице, умноженному на количество строк во второй таблице. Такой результат называется декартовым продуктом двух таблиц (Таблица 1 x Таблица 2).

Рассмотрим две наши таблицы:

  • Таблица employees
idemployee_namedepartment_id
1Homer Simpson4
2Ned Flanders1
3Barney Gumble5
4Clancy Wiggum3
5Moe SyzslakNULL
  • Таблица departments
department_iddepartment_name
1Sales
2Engineering
3Human Resources
4Customer Service
5Research And Development

Чтобы выполнить CROSS JOIN с использованием этих таблиц, мы должны написать SQL-запрос следующим образом:

SELECT * FROM employees
CROSS JOIN departments;

Обратите внимание, что в CROSS JOIN не используется ON или USING. Этим он отличается от рассмотренных нами ранее вариаций JOIN.

После выполнения CROSS JOIN мы получим следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson41Sales
2Ned Flanders11Sales
3Barney Gumble51Sales
4Clancy Wiggum31Sales
5Moe SzyslakNULL1Sales
1Homer Simpson42Engineering
2Ned Flanders12Engineering
3Barney Gumble52Engineering
4Clancy Wiggum32Engineering
5Moe SzyslakNULL2Engineering
1Homer Simpson43Human Resources
2Ned Flanders13Human Resources
3Barney Gumble53Human Resources
4Clancy Wiggum33Human Resources
5Moe SzyslakNULL3Human Resources
1Homer Simpson44Customer Service
2Ned Flanders14Customer Service
3Barney Gumble54Customer Service
4Clancy Wiggum34Customer Service
5Moe SzyslakNULL4Customer Service
1Homer Simpson45Research And Development
2Ned Flanders15Research And Development
3Barney Gumble55Research And Development
4Clancy Wiggum35Research And Development
5Moe SzyslakNULL5Research 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_ididemployee_namedepartment_name
12Ned FlandersSales
34Clancy WiggumHuman Resources
41Homer SimpsonCustomer Service
53Barney GumbleResearch And Development

NATURAL JOIN выполняется по столбцу, который является общим для обеих таблиц. В данном случае это department_id, который отображается в нашем результате только один раз.

4. Что такое OUTER JOIN?

С помощью SQL OUTER JOIN можно вернуть несовпадающие строки в одной или обеих таблицах. Существует несколько разновидностей этого оператора, некоторые из которых мы уже рассматривали выше. Далее перечислены распространенные типы предложений OUTER JOIN:

  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL 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-запроса будет получен следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research And Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL

Обратите внимание, что сотрудник по имени 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-запроса будет получен следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
2Ned Flanders11Sales
NULLNULLNULL2Engineering
4Clancy Wiggum33Human Resources
1Homer Simpson44Customer Service
3Barney Gumble55Research 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 получается следующий результат:

demployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research And Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL
2Ned Flanders11Sales
NULLNULL2Engineering
4Clancy Wiggum33Human Resources
1Homer Simpson44Customer Service
3Barney Gumble55Research And Development

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

5. В чем разница между SQL INNER JOIN и SQL LEFT JOIN?

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

Рассмотрим эти различия на практическом примере, чтобы вы могли уверенно ответить на этот вопрос на собеседовании.

Допустим, у нас есть две таблицы:

  • employees – Эта таблица содержит идентификатор каждого сотрудника, его имя и идентификатор отдела.
idemployee_namedepartment_id
1Homer Simpson4
2Ned Flanders1
3Barney Gumble5
4Clancy Wiggum3
5Moe SyzslakNULL
  • departments – Эта таблица содержит идентификатор и название каждого отдела.
department_iddepartment_name
1Sales
2Engineering
3Human Resources
4Customer Service
5Research and Development

Следующий SQL-код ищет соответствия между таблицами employees и departments на основе столбца department_id:

SELECT * from employees emp
INNER JOIN departments dep
ON emp.department_id = dep.department_id;

Выполнение этого кода приведет к следующему результату:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research and Development
4Clancy Wiggum33Human Resources

При просмотре результата можно заметить, что сотрудник Moe Szyslak отсутствует. В таблице employees этот сотрудник не имеет текущего department_id. Поэтому при попытке присоединиться к таблице departments по этому столбцу не было найдено ни одного совпадения. Таким образом, сотрудник исключается из результата.

Теперь давайте воспользуемся LEFT JOIN и посмотрим, каким будет результат. В SQL LEFT JOIN возвращаются все значения из левой таблицы плюс совпадающие значения из правой таблицы. Если совпадения не найдено, LEFT JOIN возвращает значение NULL.

Синтаксис нашего предложения SQL LEFT JOIN выглядит следующим образом:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research and Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL

В данном случае 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-запроса следующий:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research and Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL

Рассмотрим, чем он отличается от SQL FULL JOIN. Синтаксис аналогичен, что демонстрирует данный код:

SELECT * FROM employees emp
FULL JOIN departments dep
ON emp.department_id = dep.department_id;

При выполнении этого SQL-запроса к таблицам employees и departments получается следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research and Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL
NULLNULLNULL2Engineering

Сравните этот набор результатов с результатами наших запросов 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 – Эта таблица содержит идентификатор, имя, фамилию и идентификатор отдела каждого сотрудника.
idemployee_namedepartment_id
1Homer Simpson4
2Ned Flanders1
3Barney Gumble5
4Clancy Wiggum3
5Moe SyzslakNULL
  • departments – Эта таблица содержит идентификатор и название каждого отдела.
department_iddepartment_name
1Sales
2Engineering
3Human Resources
4Customer Service
5Research and Development

Если мы хотим сохранить все строки из таблицы 1 (в данном случае – employees), мы должны указать ее в качестве левой таблицы.

Синтаксис этого предложения LEFT JOIN следующий:

SELECT * FROM employees emp
LEFT JOIN departments dep
ON emp.department_id = dep.department_id;

Выполнение этого запроса дает следующий результат:

idemployee_namedepartment_iddepartment_iddepartment_name
1Homer Simpson44Customer Service
2Ned Flanders11Sales
3Barney Gumble55Research and Development
4Clancy Wiggum33Human Resources
5Moe SzyslakNULLNULLNULL

Обратите внимание, что сотрудник Moe Szyslak был включен в этот набор данных, несмотря на то, что в таблице departments нет совпадающего идентификатора department_id. Именно в этом и заключается смысл предложения LEFT JOIN – включить все данные из левой таблицы, независимо от того, были ли найдены совпадения в правой таблице.

8. Как объединить более двух таблиц?

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

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

Для примера множественного JOIN представим, что у нас есть три таблицы:

departments – Эта таблица содержит идентификатор и название каждого отдела.

department_iddepartment_name
1Sales
2Engineering
3Human Resources
4Customer Service
5Research and Development

office – В этой таблице содержится адрес каждого офиса.

idaddress
15 Wisteria Lane, Springfield, USA
2124 Chestmount Street, Springfield, USA
36610 Bronzeway, Springfield, USA
4532 Executive Lane, Springfield, USA
510 Meadow View, Springfield, USA

department_office – Эта таблица связывает информацию об офисе с соответствующим отделом. Отделы могут включать в себя несколько офисов.

office_iddepartment_id
11
23
32
44
55
21
51
43

В нашем случае мы использовали таблицу связей 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_nameaddress
Sales5 Wisteria Lane, Springfield, USA
Engineering124 Chestmount Street, Springfield, USA
Human Resources6610 Bronzeway, Springfield, USA
Customer Service532 Executive Lane, Springfield, USA
Research and Development10 Meadow View, Springfield, USA
Sales124 Chestmount Street, Springfield, USA
Sales10 Meadow View, Springfield, USA
Human Resources532 Executive Lane, Springfield, USA

Вот и все! Мы получили желаемый результат – каждый отдел и соответствующий ему адрес. Обратите внимание, что самым крупным является отдел продаж, который охватывает три разных офиса. Второй по величине отдел – отдел кадров, который охватывает два разных офиса.

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

9. Как присоединить таблицу к самой себе?

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

Для нашего примера мы будем использовать следующую таблицу:

employee – В этой таблице хранятся имена всех сотрудников компании, идентификаторы их отделов и идентификаторы их руководителей.

idemployee_namedepartment_idmanager_id
1Montgomery Burns4NULL
2Waylon Smithers11
3Homer Simpson21
4Carl Carlson51
5Lenny Leonard31
6Frank Grimes23

Допустим, мы хотим получить набор результатов, в котором будут показаны только сотрудники с их руководителями. Это можно легко сделать с помощью псевдонимов таблиц в сочетании с самоприсоединением. Мы будем использовать 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-запрос с самоприсоединением и избежать ошибок.

Выполнение приведенного выше запроса дает следующий результат:

EmployeeManager
Montgomery BurnsNULL
Waylon SmithersMontgomery Burns
Homer SimpsonMontgomery Burns
Carl CarlsonMontgomery Burns
Lenny LeonardMontgomery Burns
Frank GrimesHomer 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
EmployeeManager
Waylon SmithersMontgomery Burns
Homer SimpsonMontgomery Burns
Carl CarlsonMontgomery Burns
Lenny LeonardMontgomery Burns
Frank GrimesHomer Simpson

Единственным существенным отличием является отсутствие Montgomery Burns в столбце Employee. Это объясняется тем, что значение manager_id для него было NULL; INNER JOIN возвращает только совпадающие столбцы, при этом NULL-значения исключаются.

10. Должно ли условие JOIN быть равенством?

Неравным соединением считается любое предложение JOIN, в котором в качестве условия JOIN не используется равенство ( = ). В сочетании с условиями соединения можно использовать обычные операторы сравнения (например, <, >, <=, >=, != и <>). Также можно использовать оператор BETWEEN.

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

Сначала посмотрим на данные, которые мы будем запрашивать. В данном примере мы будем использовать только одну таблицу, хорошо знакомую нам employee:

idemployee_namedepartment_idmanager_id
1Montgomery Burns4NULL
2Waylon Smithers11
3Homer Simpson21
4Carl Carlson51
5Lenny Leonard31
6Frank Grimes23
7Lenny Leonard31

Если бы мы хотели быстро идентифицировать любые повторяющиеся значения, мы бы написали следующий запрос:

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, мы увидим, что оно имеет два условия:

  1. Оно сопоставляет записи с одинаковыми именами.
  2. Оно извлекает записи, ID которых меньше ID временной самоприсоединенной таблицы.

Выполнение этого запроса дает следующий набор результатов:

idemployee_nameidemployee_name
5Lenny Leonard7Lenny Leonard

Мы видим, что в таблице есть повторяющаяся запись Lenny Leonard . Дубликаты могут привести к непредсказуемым ошибкам и испортить данные в отчетах.

Это лишь один из многих возможных примеров, демонстрирующих полезность неравных объединений.

Перевод статьи «The Top 10 SQL JOIN Interview Questions and How to Answer Them».

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *