Оператор OUTER JOIN

Причины появления OUTER JOIN

Кстати, помнишь, мы объединяли наши таблицы и у нас пропали задачи по уборке офиса, так как самой уборщицы еще не было?

Если выполнить такой запрос:

SELECT * FROM task

То мы получим такой результат:

Задача “Убрать офис” пропадает, если мы попробуем объединить таблицу task с таблицей employee по employee_id.

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

Напомню классический вид оператора JOIN:

       
   
       
   
       
    
    таблица1 JOIN таблица2 ON условие 
   

Мы можем сказать SQL-серверу, чтобы все данные из левой таблицы (таблица1) обязательно присутствовали в объединенной таблице. Даже если для них нет пары в правой таблице. Для этого всего лишь нужно написать:

таблица1 LEFT JOIN таблица2 ON условие

Если же ты хочешь, чтобы в объединенной таблице обязательно были все строки из правой таблицы, то нужно написать:

таблица1 RIGHT JOIN таблица2 ON
 условие 

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

SELECT * FROM employee e RIGHT JOIN task t ON e.id = t.employee_id

И результат такого запроса:

В нашу таблицу добавилась еще одна строка, и что интересно в ней очень много значений NULL. Все данные, которые брались из таблицы employee отображаются в виде NULL, так как для задачи “Убрать офис” не нашлось исполнителя из таблицы employee.

Типы JOIN-ов

Всего существует 4 типа JOIN-ов. Они представлены в таблице ниже:

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

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

Вопрос с собеседования

Иногда программистов-новичков на собеседовании валят очень простым вопросом. С учетом наших таблиц его можно сформулировать так:

Можно решить эту задачу многими способами, но я начну с самых простых: Во-первых, ты можешь объединить наши таблицы с помощью LEFT JOIN, а потом с помощью WHERE исключить все строки, для которых недостающие данные были дополнены NULL-ами.

SELECT * FROM employee e LEFT JOIN task t ON e.id = t.employee_id WHERE t.id IS NULL 

И результат такого запроса:

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

Для этого нужно или перечислить в SELECT требуемые колонки таблицы employee, или если нужно отобразить их все, то можно написать такую конструкцию:

SELECT e.* FROM employee e, task t 

Полный запрос будет выглядеть так:

SELECT e.* FROM employee e LEFT JOIN task t ON e.id = t.employee_id WHERE t.id IS NULL 

результат такого запроса:

Остальные способы остаются вам для домашнего задания. Не хочу лишать вас удовольствия найти их самостоятельно.

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0323

В этой задаче тебе потребуется: 1. Выбрать колонку last_name из таблицы customers, временно изменив название на surname (используй AS) и колонку order_id из таблицы orders. 2. Объединить таблицы customers и orders оператором LEFT JOIN. 3. Используя оператор ON, добавить условие, что колонка customer

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0324

В этой задаче тебе потребуется: 1. Выбрать колонку customer_id из таблицы customers и колонку order_id из таблицы orders. 2. Объединить таблицы customers и orders оператором LEFT JOIN, заменив их имена на c и o, соответственно, используя оператор AS. 3. Используя оператор ON, добавить условие, что к

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0325

В этой задаче тебе потребуется: 1. Выбрать колонку zip_code из таблицы customers и колонку order_id из таблицы orders. 2. Объединить таблицы customers и orders оператором LEFT JOIN, заменив их имена на c и o, соответственно, используя оператор AS. 3. Используя оператор ON, добавить условие, что коло

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0326

В этой задаче тебе потребуется: 1. Выбрать колонку email из таблицы customers и все колонки из таблицы orders. 2. Объединить таблицы customers и orders оператором RIGHT JOIN. 3. Используя оператор ON, добавить условие, что колонка customer_id таблицы customers равнa колонке customer_id таблицы order

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0327

В этой задаче тебе потребуется: 1. Выбрать колонку city из таблицы customers и колонку store_id из таблицы orders. 2. Объединить таблицы customers и orders оператором RIGHT JOIN. 3. Используя оператор ON, добавить условие, что колонка customer_id таблицы customers равнa колонке customer_id таблицы o

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0328

В этой задаче тебе потребуется: 1. Выбрать колонку phone из таблицы customers и переименовать её в cust_phone (используй оператор AS). Также выбрать колонки order_date, total_cost и store_id из таблицы orders. 2. Объединить таблицы customers и orders оператором RIGHT JOIN. 3. Используя оператор ON,

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0329

В этой задаче тебе потребуется: 1. Выбрать колонку customer_id из таблицы customers и колонку order_status из таблицы orders. 2. Объединить таблицы customers и orders оператором JOIN, при этом изменив их названия на c и o, соответственно (используя оператор AS). 3. Используя оператор ON, добавить ус

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0330

В этой задаче тебе потребуется: 1. Выбрать колонку zip_code из таблицы customers, переименовав её в cust_zip_code, и колонку order_status из таблицы orders, переименовав её в status. 2. Объединить таблицы customers и orders оператором LEFT JOIN. 3. Используя оператор ON, добавить условие, что колонк

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0331

В этой задаче тебе потребуется: 1. Выбрать колонку last_name из таблицы authors, переименовав её в author, и колонки title и copies_sold_millions из таблицы books, переименовав их в book_title и books_sold, соответственно. 2. Объединить таблицы authors и books оператором RIGHT JOIN, переименовав их

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0332

В этой задаче тебе потребуется: 1. Выбрать колонку last_name из таблицы authors, переименовав её в author, и колонки genre и date_released из таблицы books, переименовав genre в book_genre. 2. Объединить таблицы authors и books оператором RIGHT JOIN, переименовав их в аuth и book, соответственно. 3.

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0333

В этой задаче тебе потребуется: 1. Выбрать колонки sex и country из таблицы authors, переименовав их в author_sex и author_country, соответственно. Также, выбрать колонку copies_sold_millions из таблицы books, переименовав её в books_sold. 2. Объединить таблицы authors и books оператором JOIN, переи

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0334

1. Выбрать колонки last_name и country из таблицы authors, переименовав их в author и author_country, соответственно. Также, выбрать колонку genre из таблицы books, переименовав её в book_genre. 2. Объединить таблицы authors и books оператором LEFT JOIN, переименовав их в author и book, соответствен

Открыть

1

Задача

Модуль 4. Работа с БД, 3 уровень, 3 лекция

Недоступна

task0335

1. Выбрать колонку country из таблицы authors, переименовав её в author_country. 2. Передать в оператор COUNT колонку book_id таблицы books, переименовав её в book_count. 2. Объединить таблицы authors и books оператором JOIN, переименовав их в author и book, соответственно. 3. Используя оператор ON,

Открыть