Mysql JOIN, LEFT JOIN, RIGHT JOIN - отличия - какая разница. Виды джойнов
Primary tabs
Forums:
Примеры будем рассматривать на основе таблиц:
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.
Источники
- Log in to post comments
- 71156 reads
vedro-compota
Fri, 12/15/2017 - 12:14
Permalink
Просьба:
Просьба:
_____________
матфак вгу и остальная классика =)
vedro-compota
Fri, 12/15/2017 - 12:16
Permalink
объяснить примеры
В примерах вообще не ясно о чем речь. Надо все отдельно писать запрос и отдельно
ответ после фразы вроде "в ответ получим"
_____________
матфак вгу и остальная классика =)
melisa
Fri, 12/15/2017 - 12:31
Permalink
добавлено разделение на запрос и ответ
добавлено разделение на запрос и ответ
vedro-compota
Mon, 08/14/2023 - 19:59
Permalink
RIHGT JOIN:
RIHGT JOIN:
LEFT JOIN:
_____________
матфак вгу и остальная классика =)