НейроАгент

INNER JOIN vs OUTER JOIN: Полное руководство по SQL

Узнайте ключевые различия между INNER JOIN и OUTER JOIN в SQL. Узнайте, как работают LEFT, RIGHT и FULL OUTER JOIN с практическими примерами и визуальными сравнениями.

#database-queries#inner-join#outer-join#sql-joins#sql-performance
Вопрос

В чем разница между INNER JOIN и OUTER JOIN в SQL? Как LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN отличаются друг от друга и от INNER JOIN?

НейроАгент

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

Содержание

Что такое INNER JOIN?

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

Ключевые характеристики INNER JOIN включают:

  • Строгое совпадение: Возвращает только строки, в которых условие соединения выполняется в обеих таблицах
  • Уменьшение данных: Часто приводит к меньшему количеству строк, чем исходные таблицы
  • Производительность: Обычно быстрее, чем OUTER JOIN, так как обрабатывает меньше данных
  • Синтаксис: Можно писать с или без ключевого слова INNER (и JOIN table2, и INNER JOIN table2 эквивалентны)
sql
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

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

Понимание типов OUTER JOIN

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

Три основных типа OUTER JOIN служат разным целям:

  1. LEFT OUTER JOIN: Сохраняет все записи из левой таблицы
  2. RIGHT OUTER JOIN: Сохраняет все записи из правой таблицы
  3. FULL OUTER JOIN: Сохраняет все записи из обеих таблиц

Ключевое замечание: Слово “OUTER” в синтаксисе SQL необязательно. Например, LEFT JOIN и LEFT OUTER JOIN означают абсолютно одно и то же в большинстве баз данных SQL.

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

Объяснение LEFT OUTER JOIN

LEFT OUTER JOIN (или просто LEFT JOIN) возвращает все записи из левой таблицы и совпавшие записи из правой таблицы. Когда совпадения в правой таблице нет, для этих столбцов возвращаются значения NULL. Как описано в руководстве DataCamp, “LEFT OUTER JOIN возвращает все записи из левой таблицы и совпавшие записи из правой таблицы.”

Как работает LEFT JOIN:

  • Сохранение левой таблицы: Все строки из левой таблицы включаются в результат
  • Совпадение правой таблицы: Включаются только строки с совпадающими условиями соединения из правой таблицы
  • Заполнение NULL: Неприведенные столбцы правой таблицы заполняются значениями NULL
  • Производительность: Обычно более затратен, чем INNER JOIN из-за необходимости обработки всех строк левой таблицы
sql
SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Пример сценария:
Если у вас 100 клиентов и только 30 из них разместили заказы, LEFT JOIN вернет всех 100 клиентов. 70 клиентов без заказов будут иметь значения NULL в столбце order_id.

Объяснение RIGHT OUTER JOIN

RIGHT OUTER JOIN (или RIGHT JOIN) — это зеркальное отражение LEFT JOIN. Он возвращает все записи из правой таблицы и совпавшие записи из левой таблицы. Согласно GeeksforGeeks, “Right Outer Join работает путем выбора всех записей из правой таблицы и совпавших записей из левой таблицы.”

Как работает RIGHT JOIN:

  • Сохранение правой таблицы: Все строки из правой таблицы включаются
  • Совпадение левой таблицы: Включаются только строки с совпадающими условиями соединения из левой таблицы
  • Заполнение NULL: Неприведенные столбцы левой таблицы заполняются значениями NULL
  • Использование: На практике используется реже, чем LEFT JOIN
sql
SELECT departments.department_name, employees.employee_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Пример сценария:
Если у вас 5 отделов и сотрудники назначены только в 4 из них, RIGHT JOIN вернет все 5 отделов. Отдел без сотрудников будет иметь значения NULL в столбце employee_name.

Объяснение FULL OUTER JOIN

FULL OUTER JOIN (или FULL JOIN) возвращает все записи как из левой, так и из правой таблицы. Он включает совпавшие строки, где условие соединения выполняется, а для неприведенных строк заполняются значения NULL. Как указано в статье Software Testing Help, “В полных внешних соединениях все данные объединяются, где это возможно.”

Как работает FULL JOIN:

  • Полное сохранение: Все строки из обеих таблиц включаются
  • Логика совпадения: Строки с совпадающими условиями соединения объединяются
  • Заполнение NULL: Неприведенные строки из любой таблицы получают значения NULL для столбцов другой таблицы
  • Результат: Наиболее полный результирующий набор среди всех типов JOIN
sql
SELECT customers.customer_name, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

Пример сценария:
Если у вас 100 клиентов и 200 заказов, FULL JOIN вернет всех 100 клиентов и все 200 заказов. Клиенты без заказов будут иметь NULL в order_id, а заказы без клиентов — NULL в customer_name.

Визуальное сравнение с диаграммами Венна

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

Визуализация INNER JOIN:

   Таблица А      Таблица Б
  ┌─────────┐   ┌─────────┐
  │    ●●●  │   │    ●●●  │
  │   ●●●●  │◄──►│   ●●●●  │
  │  ●●●●●  │   │  ●●●●●  │
  │   ●●●●  │   │   ●●●●  │
  │    ●●●  │   │    ●●●  │
  └─────────┘   └─────────┘
       ▲               ▲
       └───────┬───────┘
               │
        ┌───────────────┐
        │    ●●●●●     │
        │   ●●●●●●●    │
        │  ●●●●●●●●●   │
        │   ●●●●●●●    │
        │    ●●●●●     │
        └───────────────┘
          Результат INNER JOIN

Визуализация LEFT JOIN:

   Таблица А      Таблица Б
  ┌─────────┐   ┌─────────┐
  │    ●●●  │   │    ●●●  │
  │   ●●●●  │◄──►│   ●●●●  │
  │  ●●●●●  │   │  ●●●●●  │
  │   ●●●●  │   │   ●●●●  │
  │    ●●●  │   │    ●●●  │
  └─────────┘   └─────────┘
       ▲               ▲
       └───────┬───────┘
               │
        ┌───────────────────┐
        │    ●●●●●         │
        │   ●●●●●●●        │
        │  ●●●●●●●●●       │
        │   ●●●●●●●        │
        │    ●●●●●         │
        │   ●●●●●●●        │
        │  ●●●●●●●●●       │
        │   ●●●●●●●        │
        │    ●●●●●         │
        └───────────────────┘
            Результат LEFT JOIN

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

Практические примеры и варианты использования

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

Пример схемы базы данных:

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO employees VALUES 
(1, 'John Doe', 1),
(2, 'Jane Smith', 2),
(3, 'Mike Johnson', 1);

INSERT INTO departments VALUES 
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering');

Пример INNER JOIN:

sql
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Результат:

John Doe      | Sales
Jane Smith    | Marketing  
Mike Johnson  | Sales

Вариант использования: Когда нужно видеть только сотрудников, назначенных в существующие отделы.

Пример LEFT JOIN:

sql
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Результат:

John Doe      | Sales
Jane Smith    | Marketing  
Mike Johnson  | Sales

(В этом случае такой же, как INNER JOIN, потому что у всех сотрудников есть действительные отделы)

Вариант использования: Когда нужно видеть всех сотрудников, независимо от того, назначен ли им отдел.

Пример RIGHT JOIN:

sql
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Результат:

John Doe      | Sales
Jane Smith    | Marketing  
Mike Johnson  | Sales
NULL          | Engineering

Вариант использования: Когда нужно видеть все отделы, включая те, в которых нет сотрудников.

Пример FULL JOIN:

sql
SELECT e.name, d.name AS department
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

Результат:

John Doe      | Sales
Jane Smith    | Marketing  
Mike Johnson  | Sales
NULL          | Engineering

(В этом случае такой же, как RIGHT JOIN, потому что нет сотрудников без отделов)

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

Когда использовать каждый тип JOIN

Выбор правильного типа JOIN зависит от ваших конкретных требований к данным:

Используйте INNER JOIN, когда:

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

Используйте LEFT JOIN, когда:

  • Вам нужны все записи из основной таблицы (левой таблицы)
  • Вы хотите видеть, какие записи в основной таблице не имеют совпадений во вторичной таблице
  • Вы создаете отчеты, где отсутствие данных допустимо
  • Левая таблица представляет “основные” сущности, а правая — необязательные отношения

Используйте RIGHT JOIN, когда:

  • Вам нужны все записи из вторичной таблицы (правой таблицы)
  • Правая таблица представляет “основные” сущности в вашем запросе
  • Вы работаете со старыми системами, где RIGHT JOIN более естественен
  • Вы хотите определить, какие записи во вторичной таблице не имеют совпадений в основной таблице

Используйте FULL JOIN, когда:

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

Как упомянуто в статье SpringPeople, “Если вам нужны только совпадающие значения из связанных таблиц, вы можете выбрать inner join в SQL. Однако, если вам нужны как совпадающие, так и несовпадающие значения, вы должны использовать outer join в SQL.”

Заключение

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

Ключевые выводы:

  1. INNER JOIN лучше всего подходит для строгих требований совпадения и лучшей производительности
  2. LEFT JOIN является наиболее часто используемым OUTER JOIN для сохранения данных левой таблицы
  3. RIGHT JOIN служит противоположной цели LEFT JOIN, но используется реже
  4. FULL JOIN предоставляет наиболее полный обзор, но может быть ресурсоемким
  5. Слово “OUTER” в синтаксисе SQL необязательно для всех типов OUTER JOIN

При написании SQL-запросов внимательно учитывайте ваши требования к данным. Если вам нужно видеть всех клиентов, даже тех, которые не совершали покупок, используйте LEFT JOIN. Если вы сверяете два набора данных и нужно найти все различия, FULL JOIN может быть вашим лучшим выбором. Освоение этих операций JOIN значительно повысит вашу способность эффективно работать с реляционными базами данных.

Источники

  1. DataCamp - SQL INNER JOIN vs. OUTER JOIN: Key Differences
  2. GeeksforGeeks - Inner Join vs Outer Join
  3. GeeksforGeeks - Difference between Inner Join and Outer Join in SQL
  4. Software Testing Help - Inner Join Vs Outer Join: Exact Difference With Examples
  5. Temok - Inner Join vs Outer Join: Key SQL Differences With Examples
  6. SpringPeople - INNER vs OUTER Joins in SQL: What’s the Difference and When to Use Each?
  7. Steve Stedman - Inner Join versus Outer Join
  8. Medium - SQL JOINs (Inner, Left, Right, Full, Self and Cross)
  9. Interview Kickstart - Inner Join vs Outer Join: Exploring SQL Table Relationships
Чем INNER JOIN и LEFT JOIN отличаются по результирующим наборам и производительности?Какие практические случаи использования RIGHT JOIN против LEFT JOIN в реальных приложениях?Можете ли вы объяснить последствия производительности различных типов JOIN в больших базах данных?Как значения NULL влияют на операции JOIN и как их можно эффективно обрабатывать?Какие лучшие практики для выбора правильного типа JOIN в сложных SQL запросах?Как операции JOIN работают с несколькими таблицами в SQL запросах?
Спросить у NeuroAgent