Mysql JOIN, LEFT JOIN, RIGHT JOIN - отличия - какая разница. Виды джойнов

Примеры будем рассматривать на основе таблиц:

TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

• INNER JOIN (Внутреннее объединение)

  Это самое простое, самое понятное и самое распространённое объединение. Этот запрос вернёт все записи из левой таблицы (Table_A), которые имеют соответствующие записи в правой таблице. Это объединение (JOIN) в общем виде описывается следующим образом, используя
  предложение WHERE:

  SELECT A.*, B.* FROM Table_A A, Table_B B WHERE A.Key = B.Key

  или используя предложение ON:

  SELECT * FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key

  Пример:

  -- INNER JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
         B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  INNER JOIN Table_B B
  ON A.PK = B.PK

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
     1 FOX        TROT          1
     2 COP        CAR           2
     3 TAXI       CAB           3
     6 WASHINGTON MONUMENT      6
     7 DELL       PC            7
  
  (5 row(s) affected)

• LEFT JOIN (Левостороннее внешнее объединение)

  Данный запрос вернёт все записи из левой таблицы (Table A), независимо от того, имеются ли соответствующие им записи в правой таблице (Table B). Если в Table B существуют ключи, соответствующие ключам Table A, то поля результирующей таблицы заполняются значениями Table B. Часть строк, для которых не обнаружилось совпадающих ключей, будут заполнены только данными из левой таблицы, а поля, хранящие данные Table B, будут иметь значение null. Описывается следующим образом:

  SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key

  Пример:

  -- LEFT JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
  B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  LEFT JOIN Table_B B
  ON A.PK = B.PK

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
     1 FOX        TROT          1
     2 COP        CAR           2
     3 TAXI       CAB           3
     4 LINCOLN    NULL       NULL
     5 ARIZONA    NULL       NULL
     6 WASHINGTON MONUMENT      6
     7 DELL       PC            7
    10 LUCENT     NULL       NULL
  
  (8 row(s) affected)

• RIGHT JOIN (Правостороннее внешнее объединение)

  Ситуация абсолютно симметричная LEFT JOIN.
  В результирующей таблице полностью заполнены будут строки, для которых был найден ключ левой таблицы (Table A), соответствующий ключу правой таблицы (Table B). Также в результат будут добавлены все остальные строки правой таблицы. Для них поля, хранящие данные Table A, будут иметь значение null.
  Обратите внимание на порядок записи таблиц в запросе! В RIGHT JOIN правая таблица попадёт в результат полностью!
  Описывается следующим образом:

  SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key

  Отличие LEFT и RIGHT JOIN только в порядке указания таблиц. По сути два этих объединения работают одинаково, но в разных направлениях)

  Пример:

  -- RIGHT JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
  B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  RIGHT JOIN Table_B B
  ON A.PK = B.PK

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
     1 FOX        TROT          1
     2 COP        CAR           2
     3 TAXI       CAB           3
     6 WASHINGTON MONUMENT      6
     7 DELL       PC            7
  NULL NULL       MICROSOFT     8
  NULL NULL       APPLE         9
  NULL NULL       SCOTCH       11
  
  (8 row(s) affected)

• OUTER JOIN (Полное внешнее объединение)

  Это объединение также упоминается как FULL OUTER JOIN или как FULL JOIN. Такой запрос возвращает все запрашивемые записи из обеих таблиц. Записи, не имеющие соответствующих себе данных из противоположной таблицы, заполняются частично. Пустые поля заполнятся значениями null.
  Описывается следующим образом:

  SELECT * FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key

  Пример:

  -- OUTER JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
  B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  FULL OUTER JOIN Table_B B
  ON A.PK = B.PK

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
     1 FOX        TROT          1
     2 COP        CAR           2
     3 TAXI       CAB           3
     6 WASHINGTON MONUMENT      6
     7 DELL       PC            7
  NULL NULL       MICROSOFT     8
  NULL NULL       APPLE         9
  NULL NULL       SCOTCH       11
     5 ARIZONA    NULL       NULL
     4 LINCOLN    NULL       NULL
    10 LUCENT     NULL       NULL
  
  (11 row(s) affected)

• LEFT EXCLUDING JOIN (Левостороннее исключающее объединение)

  Такой запрос возвращает только записи левой таблицы (Table A), не имеющие совпадений по ключу с правой таблицей (Table B). Чтобы этого достичь, добавляем условие "WHERE B.Key IS NULL"
  Описывается следующим образом:

  SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL

  Пример:

  -- LEFT EXCLUDING JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
  B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  LEFT JOIN Table_B B
  ON A.PK = B.PK
  WHERE B.PK IS NULL

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
     4 LINCOLN    NULL       NULL
     5 ARIZONA    NULL       NULL
    10 LUCENT     NULL       NULL
  (3 row(s) affected)

• RIGHT EXCLUDING JOIN (Правосторонее исключающее объединение)

  Ситуация абсолютно симметричная LEFT EXCLUDING JOIN.
  Такой запрос возвращает только записи правой таблицы (Table B), не имеющие совпадений по ключу с левой таблицей (Table A).
  Описывается следующим образом:

  SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL

  Пример:

  -- RIGHT EXCLUDING JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
  B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  RIGHT JOIN Table_B B
  ON A.PK = B.PK
  WHERE A.PK IS NULL

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
  NULL NULL       MICROSOFT     8
  NULL NULL       APPLE         9
  NULL NULL       SCOTCH       11
  
  (3 row(s) affected)

• OUTER EXCLUDING JOIN (Левостороннее исключающее объединение)

  Данный запрос возвращает все записи из обеих таблиц, НЕ имеющие совпадений по ключам. Также это можно описать как "FULL OUTER JOIN минус INNER JOIN"
  Выглядит следующим образом:

  SELECT * FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL

  Пример:

  -- OUTER EXCLUDING JOIN
  SELECT A.PK AS A_PK, A.Value AS A_Value,
  B.Value AS B_Value, B.PK AS B_PK
  FROM Table_A A
  FULL OUTER JOIN Table_B B
  ON A.PK = B.PK
  WHERE A.PK IS NULL
  OR B.PK IS NULL

  В ответ получим:

  A_PK A_Value    B_Value    B_PK
  ---- ---------- ---------- ----
  NULL NULL       MICROSOFT     8
  NULL NULL       APPLE         9
  NULL NULL       SCOTCH       11
     5 ARIZONA    NULL       NULL
     4 LINCOLN    NULL       NULL
    10 LUCENT     NULL       NULL
  
  (6 row(s) affected)

Обратите внимание, что при использовании ВНЕШНИХ объединений первыми возвращаются данные подходящие обеим таблицам -- INNER JOIN (если это не исключающее объединение, конечно), затем записи правой таблицы, а затем - левой. Такое поведение характерно для mysql по умолчанию, если вы не используете ORDER BY.

Источники

• CodeProject
• Википедия