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

Forums:

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.

Источники

vedro-compota's picture

Просьба:

  1. Поставить тэг необходимости проверки
  2. Убрать массовый жирный шрифт в конце
  3. Источники оформить как список (их всегда лучше оформлять как список)

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

В примерах вообще не ясно о чем речь. Надо все отдельно писать запрос и отдельно
ответ после фразы вроде "в ответ получим"

_____________
матфак вгу и остальная классика =)

melisa's picture

добавлено разделение на запрос и ответ

vedro-compota's picture

RIHGT JOIN:

категория 1   - подкатегория 1
категория 1   - подкатегория 2
категория 1   - подкатегория 3
категория 1   - подкатегория 2
категория 2 

LEFT JOIN:

подкатегория 1 категория 1   - 
подкатегория 2 категория 1   - 
подкатегория 3 категория 1   - 
подкатегория 2 категория 1   
подкатегория 5  ---

_____________
матфак вгу и остальная классика =)