Большинство начинающих веб программистов начинает свое изучение MySQL с простейших операторов SELECT, UPDATE и DELETE. Данными операторами вполне можно описать весь необходимый функционал простого сайта, но, как можно догадаться, на этом возможности языка SQL далеко не заканчиваются. В процессе разработки обязательно потребуется объединение данных из нескольких таблиц. И для этих целей существует оператор JOIN. Данный оператор является основным оператором стандарта SQL92 и поддерживается большинством СУБД.
Общий синтаксис JOIN выглядит следующим образом:
Однако, сразу следует отметить, что СУБД MySQL поддерживает только два наиболее популярных выражения: INNER JOIN и LEFT JOIN.
Рассмотрим как работает каждый из операторов, для этого создадим две таблицы: TableA и TableB.
id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja
Попробуем объединить данные из этих таблиц используя различные варианты конструкции оператора JOIN.
1. INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обоих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор.
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja
Выбор по первичному ключу и индексу положительно сказывается на скорости выборки.
2. OUTER JOIN – внешнее объединение.
Присоединение таблицы с необязательным присутствием записи в таблице. Также как и в случае с inner join, условие по индексированным полям и первичному ключу ускоряет все виды outer join’ов.
2.1 LEFT OUTER JOIN или LEFT JOIN-левое внешнее объединения. Левосторонние объединения позволяют извлекать данные из левой таблицы, дополняя их по возможности данными из правой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null
Если дополнить предыдущий запрос условием на проверку несуществования, то можно получить список записей, которые не имеют пары в таблице TableB:
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null
2.2 RIGHT OUTER JOIN или RIGHT JOIN — Правостороннее внешнее объединение
Этот вид объединений практически ничем не отличается от левостороннего объединения, за тем исключением, что данные берутся из второй таблицы, которая находится справа от конструкции JOIN, и сравниваются с данными, которые находятся в таблице, указанной перед конструкцией.
2.3 FULL OUTER JOIN – комбинация правого и левого объединений. К левой таблице присоединяются все записи из правой, соответствующие условию (по правилам inner join), плюс все не вошедшие записи из правой таблицы, поля левой таблицы заполняются значениями NULL и плюс все не вошедшие записи из левой таблицы, поля правой таблицы заполняются значениями NULL.
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null
id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader
3. CROSS JOIN – перекрестное объединение (декартово произведение), выводятся все возможные сочетания из обеих таблиц. Для этого типа оператора JOIN условия не указывается.
SELECT * FROM TableA CROSS JOIN TableB
id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Rutabaga 3 Ninja 3 Rutabaga 4 Spaghetti 4 Rutabaga 1 Pirate 1 Pirate 2 Monkey 2 Pirate 3 Ninja 3 Pirate 4 Spaghetti 4 Pirate 1 Pirate 1 Darth Vader 2 Monkey 2 Darth Vader 3 Ninja 3 Darth Vader 4 Spaghetti 4 Darth Vader 1 Pirate 1 Ninja 2 Monkey 2 Ninja 3 Ninja 3 Ninja 4 Spaghetti 4 Ninja
Данная конструкция, по причине своей не нужности, не поддерживается почти ни в одной БД
blog.kogtev.com
Example Tables
Suppose we have the following tables:
-- t1 id name 1 Tim 2 Marta -- t2 id name 1 Tim 3 Katarina Inner Joins
An inner join, like this:
SELECT * FROM `t1` INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`; Would get us only records that appear in both tables, like this:
1 Tim 1 Tim Inner joins don’t have a direction (like left or right) because they are explicitly bidirectional — we require a match on both sides.
Outer Joins
Outer joins, on the other hand, are for finding records that may not have a match in the other table. As such, you have to specify which side of the join is allowed to have a missing record.
LEFT JOIN
and
RIGHT JOIN are shorthand for LEFT OUTER JOIN and RIGHT OUTER JOIN; I will use their full names below to reinforce the concept of outer joins vs inner joins.
Left Outer Join
A left outer join, like this:
SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`; …would get us all the records from the left table regardless of whether or not they have a match in the right table, like this:
1 Tim 1 Tim 2 Marta NULL NULL Right Outer Join
A right outer join, like this:
SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`; …would get us all the records from the right table regardless of whether or not they have a match in the left table, like this:
1 Tim 1 Tim NULL NULL 3 Katarina Full Outer Join
A full outer join would give us all records from both tables, whether or not they have a match in the other table, with NULLs on both sides where there is no match. The result would look like this:
1 Tim 1 Tim 2 Marta NULL NULL NULL NULL 3 Katarina
However, as Pablo Santa Cruz pointed out, MySQL doesn’t support this. We can emulate it by doing a UNION of a left join and a right join, like this:
SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` UNION SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`; You can think of a UNION as meaning «run both of these queries, then stack the results on top of each other»; some of the rows will come from the first query and some from the second.
It should be noted that a UNION in MySQL will eliminate exact duplicates: Tim would appear in both of the queries here, but the result of the UNION only lists him once. My database guru colleague feels that this behavior should not be relied upon. So to be more explicit about it, we could add a WHERE clause to the second query:
SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` UNION SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` WHERE `t1`.`id` IS NULL; On the other hand, if you wanted to see duplicates for some reason, you could use UNION ALL.
stackoverflow.com
Введение в LEFT JOIN MySQL
Оператор MySQL LEFT JOIN позволяет запрашивать данные из двух или более таблиц базы данных. Он является необязательной частью оператора SELECT, которая указывается после FROM.
Предположим, что вы собираетесь запрашивать данные из таблиц t1 и t2. На примере приведенного ниже запроса мы проиллюстрируем синтаксис LEFT JOIN, объединяя две таблицы:
Когда вы подключаетесь к таблицам t1 и t2, используя оператор LEFT JOIN, если строка из левой таблицы t1 соответствует строке из правой таблицы t2 на основе условия соединения (t1.c1 = t2.c1), эта строка будет включена в результирующий набор.
Если значение в левой таблице не соответствует значению в правой таблице, строка в левой таблице также выбирается и объединяется с «условной» строкой из правой таблицы. «Условная» строка содержит NULL для всех соответствующих столбцов в операторе SELECT.
Другими словами, условие MySQL LEFT JOIN позволяет выбирать строки обеих таблиц, которые совпадают, плюс все строки из левой таблицы (t1) даже без совпадения со строками правой таблицы (t2).
Следующая диаграмма поможет представить, как работает условие LEFT JOIN. Пересечение двух кругов — это строки, которые соответствуют в обеих таблицах, а оставшаяся часть левого круга — это строки в таблице t1, которые не имеют соответствующей строки в таблице t2. Следовательно, все строки в левой таблице включены в результирующий набор.
Обратите внимание, что возвращаемые значения также должны соответствовать условиям в операторах WHERE и HAVING, если эти операторы доступны в запросе.
Примеры использования LEFT JOIN
Использование оператора MySQL LEFT JOIN для объединения двух таблиц
Возьмем две таблицы клиентов и заказов из демонстрационной базы данных:
В базе данных, описанной на диаграмме:
- Каждый заказ в таблице заказов должен принадлежать клиенту в таблице клиентов;
- Каждый клиент в таблице клиентов может иметь ноль или более заказов в таблице заказов.
Чтобы найти заказы, принадлежащие каждому клиенту, можно использовать MySQL LEFT JOIN пример:
Посмотреть пример
Левая таблица — это клиенты, поэтому все клиенты включены в результирующий набор. Но в нем есть строки, которые имеют данные клиента, но не имеют данных заказа, например. 168, 169 и т. д. Данные заказов в этих строках равны NULL. Это означает, что у этих клиентов нет заказов в соответствующей таблице.
Поскольку мы использовали одно и то же имя столбца (orderNumber) для объединения двух таблиц, можно сделать запрос короче, используя приведенный ниже синтаксис:
Если вы замените оператор MySQL SELECT LEFT JOIN оператором INNER JOIN, вы получите только клиентов, которые оформили хотя бы один заказ.
Использование оператора MySQL LEFT JOIN для поиска несовпадающих строк
Оператор LEFT JOIN может оказаться полезен, если вы хотите найти строки в левой таблице, которые не соответствуют строкам в правой. Чтобы найти несовпадающие строки двух таблиц, нужно добавить оператор WHERE в выборку SELECT для запроса только строк, значения столбцов которых в правой таблице содержат значение NULL.
Например, чтобы найти всех клиентов, которые не оформили ни одного заказа, используется следующий запрос:
Посмотреть пример
Условие в операторе WHERE или условие в операторе ON
Рассмотрим следующий пример использования MySQL LEFT JOIN WHERE:
В этом примере мы использовали оператор LEFT JOIN для запроса данных из таблиц orders и orderDetails. Запрос возвращает заказ и его данные, если они есть, для заказа 10123.
Но если вы переместите условие из оператора WHERE в ON:
то получите другое значение.
В этом случае запрос возвращает все заказы. Но только заказ 10123 будет иметь связанные с ним данные, как показано ниже:
Обратите внимание, что для оператора INNER JOIN условие в ON эквивалентно условию в операторе WHERE.
В этой статье мы рассказали об операторе MySQL LEFT JOIN и показали, как применять его для запроса данных из нескольких таблиц базы данных.
Перевод статьи «MySQL LEFT JOIN» дружной командой проекта Сайтостроение от А до Я.
www.internet-technologies.ru
6.4.1.1 Синтаксис оператора JOIN
MySQL поддерживает следующий синтаксис оператора JOIN при использовании в командах SELECT:
table_reference, table_reference table_reference [CROSS] JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference join_condition table_reference LEFT [OUTER] JOIN table_reference table_reference NATURAL [LEFT [OUTER]] JOIN table_reference { oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr } table_reference RIGHT [OUTER] JOIN table_reference join_condition table_reference RIGHT [OUTER] JOIN table_reference table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
где table_reference определено, как:
table_name [[AS] alias] [USE INDEX (key_list)] [IGNORE INDEX (key_list)]
и join_condition определено, как:
ON conditional_expr | USING (column_list)
Никогда не следует указывать в части ON какие бы то ни было условия, накладывающие ограничения на строки в наборе результатов. Если необходимо указать, какие строки должны присутствовать в результате, следует сделать это в выражении WHERE.
Необходимо учитывать, что в версиях до 3.23.17 оператор INNER JOIN не принимает параметр join_condition!
Наличие последней из приведенных выше конструкций выражения LEFT OUTER JOIN обусловлено только требованиями совместимости с ODBC:
- Вместо ссылки на таблицу может использоваться псевдоним, который присваивается при помощи выражений
tbl_name AS alias_nameилиtbl_name alias_name:mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
- Условный оператор
ONпредставляет собой условие в любой форме из числа тех, которые можно использовать в выраженииWHERE. - Если запись для правой таблицы в частях
ONилиUSINGвLEFT JOINне найдена, то для данной таблицы используется строка, в которой все столбцы установлены вNULL. Эту возможность можно применять для нахождения результатов в таблице, не имеющей эквивалента в другой таблице:mysql> SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
Этот пример находит все строки в таблице
table1с величинойid, которая не присутствует в таблицеtable2(т.е. все строки вtable1, для которых нет соответствующих строк вtable2). Конечно, это предполагает, чтоtable2.idобъявлен какNOT NULL. See section 5.2.6 Как MySQL оптимизируетLEFT JOINиRIGHT JOIN. -
USING (column_list)служит для указания списка столбцов, которые должны существовать в обеих таблицах. Такое выражениеUSING, как:A LEFT JOIN B USING (C1,C2,C3,...)
семантически идентично выражению
ON, например:A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
- Выражение
NATURAL [LEFT] JOINдля двух таблиц определяется так, чтобы оно являлось семантическим эквивалентомINNER JOINилиLEFT JOINс выражениемUSING, в котором указаны все столбцы, имеющиеся в обеих таблицах. -
INNER JOINи,(запятая) являются семантическими эквивалентами. Оба осуществляют полное объединение используемых таблиц. Способ связывания таблиц обычно задается в условииWHERE. -
RIGHT JOINработает аналогичноLEFT JOIN. Для сохранения переносимости кода между различными базами данных рекомендуется вместоRIGHT JOINиспользоватьLEFT JOIN. -
STRAIGHT_JOINидентичноJOIN, за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор объединения располагает таблицы в неправильном порядке. - Начиная с версии MySQL 3.23.12, можно давать MySQL указания о том, какой индекс должен использоваться при извлечении информации из таблицы. Эта возможность полезна, если оператор
EXPLAIN(выводящий информацию о структуре и порядке выполнения запросаSELECT), показывает, что MySQL использует ошибочный индекс. Задавая значение индекса вUSE INDEX (key_list), можно заставить MySQL применять для поиска записи только один из указанных индексов. Альтернативное выражениеIGNORE INDEX (key_list)запрещает использование в MySQL данного конкретного индекса. ВыраженияUSE/IGNORE KEYявляются синонимами дляUSE/IGNORE INDEX.
Несколько примеров:
mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
See section 5.2.6 Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN.
www.mysql.ru
INNER JOIN
возвращает пересечение двух множеств
SELECT t1.name, t2.city FROM Table1 t1 INNER JOIN Table2 t2 ON t1.key2 = t2.key2;
Отражением INNER JOIN является OUTER JOIN. Нам предоставлено три типа OUTER JOIN – FULL, LEFT и RIGHT. Слово OUTER писать не обязательно.
FULL JOIN
Объединяет два множества
FULL JOIN вернет ВСЕ записи из таблиц table и table2, без повторяющихся данных. Где данных нет, будет подставленно NULL.
LEFT JOIN
Возвращает данные из левой таблицы, а также данные из правой, которые пересекаются с левой.
Если данных из правой таблицы будет не хватать, то подставится NULL значение.
RIGHT JOIN – как вы наверное поняли, вернет все значения из правой таблицы и пересекающиеся данные из левой.
Исключения
Если нам понадобятся данные из первой таблицы для которых нет данных в правой, то нам просто надо добавить условие WHERE
SELECT t1.name, t2.city FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 WHERE t2.key2 IS NULL;
Множественные JOIN
С помощью JOIN можно соединить не только 2 таблицы, а сколько надо. Если вам надо соединить 2 таблицы, то потребуется 2 команды JOIN. Но не стоит впадать в крайность и объединять кучу таблиц, все это ощутимо скажется на производительности, поэтому иногда лучше выполнить несколько подзапросов. Пример соединения трех таблиц:
SELECT t1.Name, t2.City, t3.Profession FROM Table1 t1 INNER JOIN Table2 t2 ON t1.key2 = t2.key2 INNER JOIN Table3 t3 ON t1.key3 = t3.key3;
Что же, надеюсь перечисленные простые примеры JOIN помогли вам разобраться с этим оператором.
labdes.ru
Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми 🙂
В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.
Что такое JOINы в MySQL
В MySQL термин JOIN используется гораздо шире, чем можно было бы предположить. Здесь JOINом может называться не только запрос объединяющий результаты из нескольких таблиц, но и запрос к одной таблице, например, SELECT по одной таблице — это тоже джоин.
Все потому, что алгоритм выполнения джоинов в MySQL реализован с использованием вложенных циклов. Т.е. каждый последующий JOIN это дополнительный вложенный цикл. Чтобы выполнить запрос и вернуть все записи удовлетворяющие условию MySQL выполняет цикл и пробегает по записям первой таблицы параллельно проверяя соответствия условиям описанных в теле запроса, когда находятся записи, удовлетворяющие условиям — во вложенном цикле по второй таблице ищутся записи соответствующие первым и удовлетворяющие условиям проверки и т.д.
Прмер обычного запроса с INNER JOIN
SELECT
*
FROM
Table1
INNER JOIN
Table2 ON P1(Table1,Table2)
INNER JOIN
Table3 ON P2(Table2,Table3)
WHERE
P(Table1,Table2,Table3).
* This source code was highlighted with Source Code Highlighter.
где Р — условия склейки таблиц и фильтры в WHERE условии.
Можно представить такой псевдокод выполнения такого запроса.
FOR each row t1 in Table1 {
IF(P(t1)) {
FOR each row t2 in Table2 {
IF(P(t2)) {
FOR each row t3 in Table3 {
IF P(t3) {
t:=t1||t2||t3; OUTPUT t;
}
}
}
}
}
}
* This source code was highlighted with Source Code Highlighter.
где конструкция t1||t2||t3 означает конкатенацию столбцов из разных таблиц.
Если в запросе встречаются OUTER JOINs, например, LEFT OUTER JOIN
SELECT
*
FROM
Table1
LEFT JOIN
(
Table2 LEFT JOIN Table3 ON P2(Table2,Table3)
)
ON P1(Table1,Table2)
WHERE
P(Table1,Table2,Tabke3)
* This source code was highlighted with Source Code Highlighter.
то алгоритм выполнения этого запроса MySQL будет выглядеть как-то так
FOR each row t1 in T1 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}
* This source code was highlighted with Source Code Highlighter.
Более подробно почитать об этом можно здесь — dev.mysql.com/doc/refman/5.1/en/nested-joins.html
Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?
MySQL оптимизатор старается приводить запросы к тому виду к которому ему удобней обрабатывать и выполнять запросы по стандартной схеме.
С SELECT все понятно — просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.
Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.
Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.
Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
Пример самого запроса можно посмотреть по ссылке на вики.
План выполнения JOIN запросов
В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
Это дерево имеет следующий вид и имеет название «left-deep tree»
В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)
habr.com
В результате обсуждения JOIN’ов здесь я попытался в своем блоге собрать описание всех видов Join в MySQL. Здесь я привожу полученный результат на ваш строгий, но справедливый суд Ниже приведен синтаксис всех возможных форматов записи оператора JOIN при использовании в командах SELECT (см. http://dev.mysql.com/doc/refman/5.0/en/join.html). Все примеры и описания рассмотрены на примере MySQL и вероятнее всего будут отличаться для других типов СУБД!
1. table_reference [INNER] JOIN table_reference
2. table_reference [CROSS] JOIN table_reference
3. table_reference STRAIGHT_JOIN table_reference
4. table_reference [INNER] JOIN table_reference join_condition
5. table_reference [CROSS] JOIN table_reference join_condition
6. table_reference STRAIGHT_JOIN table_reference ON conditional_expr
7. table_reference LEFT [OUTER] JOIN table_reference join_condition
8. table_reference RIGHT [OUTER] JOIN table_reference join_condition
9. table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
10. table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
При описании различных вариантов использования JOIN будут использоваться две тестовые таблицы – sport и music. Первая содержит список людей, посещающих спортивные секции, вторая для людей, посещающих занятия по музыке.
1. table_reference [INNER] JOIN table_reference
Тип соединения "внутреннее". Внутренний тип соединения используется по умолчанию, когда тип явно не задан (то есть, опущено ключевое слово INNER). Если не приведено ни одного условия совпадения, то будет возвращено декартово произведение, в котором каждая строка одной таблицы будет сопоставлена с каждой строкой другой таблицы. То есть будут получены всевозможные сочетания из обеих таблиц. Такое соединение в разделе FROM может быть заменено списком таблиц через запятую.
эквивалентно:
результат:
Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом.
В данном случае, например, спортсмен Джек (Jack) ходил бы на занятия музыкой вместе с Майклом (Mike), Сэмом (Sam) и с Гарри (Garry). И аналогично для остальных ребят.
2. table_reference [CROSS] JOIN table_reference
Абсолютно то же самое, что table_reference [INNER] JOIN table_reference.
результат:
3. table_reference STRAIGHT_JOIN table_reference
То же самое, что table_reference [INNER] JOIN table_reference за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор соединения таблиц в MySQL располагает таблицы в неправильном порядке. То есть специальный оператор, по сути, заставляет соединять таблицы в том порядке, в котором это указано в SQL-запросе. В некоторых случаях это может позволить оптимизировать скорость выполнения запроса.
результат:
Покажите мне всевозможные сочетания пар, как если бы все ребята занимались И музыкой И спортом И соедините для меня данные именно в том порядке, как я прошу — сначала реестр спортсменов (таблица sport), а рядом реестр музыкантов!
И снова, как и для table_reference [INNER] JOIN table_reference спортсмен Джек (Jack) ходит на занятия музыкой вместе с Майклом (Mike), Сэмом (Sam) и с Гарри (Garry). И аналогично для всех остальных.
4. table_reference [INNER] JOIN table_reference join_condition
INNER JOIN – внутреннее соединение. Объединяет две таблицы, где каждая строка обеих таблиц в точности соответствует условию. Если для строки одной таблицы не найдено соответствия в другой таблице, строка не включается в набор. Тип соединения "внутреннее". Внутренний тип соединения используется по умолчанию, когда тип явно не задан (т.е. если вы напишите просто JOIN).
эквивалентно:
результат:
Покажите мне всех ребят, которые ходят И на занятия музыкой И на занятия спортом.
Из таких ребят оказался только один – Майкл (Mike). Только он присутствует и в таблице music и в таблице sport.
5. table_reference [CROSS] JOIN table_reference join_condition
Абсолютно то же самое, что table_reference [INNER] JOIN table_reference join_condition.
результат:
6. table_reference STRAIGHT_JOIN table_reference ON conditional_expr
То же самое, что table_reference table_reference [INNER] JOIN table_reference join_condition за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор соединения таблиц в MySQL располагает таблицы в неправильном порядке. То есть специальный оператор STRAIGHT_JOIN, по сути, заставляет соединять таблицы в том порядке, в котором это указано в SQL-запросе. В некоторых случаях это может позволить оптимизировать скорость выполнения запроса.
результат:
Покажите мне всех ребят, которые ходят И на занятия музыкой И на занятия спортом и соедините для меня данные именно в том порядке, как я прошу — сначала список спортсменов (таблица sport), а рядом список музыкантов!
Снова, как и в примере для INNER JOIN, из таких ребят оказался только один – Майкл (Mike).
7. table_reference LEFT [OUTER] JOIN table_reference join_condition
LEFT (OUTER) — Тип соединения "левое (внешнее)". Левое соединение таблиц А и В включает в себя все строки из левой таблицы А и те строки из правой таблицы В, для которых обнаружено совпадение. Для строк из таблицы А, для которых не найдено соответствия в таблице В, в столбцы, извлекаемые из таблицы В, заносятся значения NULL.
результат:
Покажите мне всех спортсменов, которые ходят И на занятия спортом И на занятия музыкой, а тех, что занимаются только спортом и не занимаются музыкой пометьте в разделе music как NULL.
8. table_reference RIGHT [OUTER] JOIN table_reference join_condition
Тип соединения "правое (внешнее)". Правое соединение таблиц А и В включает в себя все строки из правой таблицы В и те строки из левой таблицы А, для которых обнаружено совпадение. Для строк из таблицы В, для которых не найдено соответствия в таблице А, в столбцы, извлекаемые из таблицы А заносятся значения NULL.
результат:
Покажите мне всех музыкантов, которые ходят И на занятия музыкой И на спортом, а тех, что занимаются только музыкой и не занимаются спортом пометьте в разделе sport как NULL.
9. table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
Естественное соединение производится по всем столбцам таблиц А и В, имеющим одинаковые имена. В результирующую таблицу одинаковые столбцы вставляются только один раз. Выражение NATURAL [LEFT] JOIN для двух таблиц является эквивалентом INNER JOIN или LEFT JOIN с выражением USING, в котором указаны все столбцы, имеющиеся в обеих таблицах.
эквивалентно:
результат:
Дайте мне данные всех колонок из реестра спортсменов (именно спортсменов!), для которых в реестре музыкантов есть колонки с такими же названиями.
10. table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference
Естественное соединение производится по всем столбцам таблиц А и В, имеющим одинаковые имена. В результатирующую таблицу одинаковые столбцы вставляются только один раз.
результат:
Дайте мне данные всех колонок из реестра музыкантов (именно музыкантов!), для которых в реестре спортсменов есть колонки с такими же названиями.
Полезные ссылки
1. Описание синтаксиса JOIN на dev.mysql.com (английский)
2. Описание синтаксиса JOIN на mysql.ru (русский)
3. Синтаксис соединенных таблиц в руководстве "Введение в системы управления базами данных" на citforum.ru
Отредактированно FiMko (24.03.2012 22:32:43)
sqlinfo.ru
Понятие слова Join
Языки разработки баз данных, неважно, что именно это за язык, за основу берут стандартные слова из англоязычных словарей (именно поэтому при условии, что вы знаете английский язык, вам буде намного проще работать с таблицами). Для реализации подключения таблиц в выборку взято такое же слово — Join. В языке программирования баз данных Используется My SQL. Перевод этого служебного слова в точности такой же, как и в самом языке — «объединение».
Интерпретация конструкции MySQL – Join, причем любой из них, будет точно такой же. Если расшифровать назначение конструкции, а именно схему ее работы, то получим следующие значение: конструкции позволят собрать нужные поля из разных таблиц или вложенных запросов в одну выборку.
Виды конструкций для объединения
Если программисту необходимо собрать выборку из нескольких таблиц и он знает, какие ключевые поля в них и какие именно данные необходимы для отчета, то можно воспользоваться одной из основных конструкций объединения, для достижения желаемого результата. Основных конструкций (для объединения таблиц) четыре:
- Inner join.
- Cross Join.
- Left join.
- Right Join.
В зависимости от поставленной задачи, каждая из стандартных конструкций будет давать разные результаты, что позволит получать отчеты по разным параметрам в короткий сроки.
Создание и заполнение таблиц для дальнейшего использования
Перед тем как начать, например, рассматривать механизмы работы с конструкциями объединения данных, стоит подготовить несколько таблиц, с которыми мы будем в дальнейшем работать. Это поможет наглядно показать все принципы работы операторов, кроме того, таким образом новички легче усвоят все азы программирования таблиц.
В первой таблице будут описаны некоторые предметы, с которыми человек постоянно встречается на протяжении своей жизни.
Во второй таблице опишем некоторые свойства объектов из первой таблицы, чтобы можно было в дальнейшем с ними работать.
В целом двух таблиц будет достаточно, чтобы на примере показать их работу. Вот теперь можно приступать к практическому рассмотрению наших конструкций.
Использование Inner Join
При использовании конструкции MySQL – Join Ineer стоит учитывать некоторые ее особенности. Данная конструкция позволит выбрать из обеих таблиц только те записи, которые есть и в первой и во второй таблице. Как это работает? В первой таблице у нас есть главный ключ – ID, который указывает на порядковый номер записей в таблице.
При создании второй таблицы, этот же ключ используется как порядковый номер, пример можно посмотреть на рисунках. При выборе данных оператор Select определит в результате только те записи, порядковые номера которых совпадают — значит, они есть и в первой и во второй таблице.
При использовании конструкции нужно понимать, какие именно данные необходимо получить. Самая распространенная ошибка, особенно у начинающего программиста баз данных, это нерациональное и неправильное использование конструкции Inner Join. Как пример, MySQL Inner Join, можно рассмотреть скрипт, который вернет нам из ранее описанных и заполненных таблиц информацию о предметах и их свойствах. Но и здесь может быть несколько способов использования конструкции. В этом плане My SQL очень гибкий язык. Итак, можно рассмотреть примеры использования MySQL Inner Join.
Объединение таблиц без указания, каких-либо параметров. В этом случае мы получим результат такого плана:
Если укажем через служебное слово Using, что обязательно нужно учитывать главные ключи записей в таблицах, то результат выборки кардинально изменится. В этом случае мы получим выборку, которая вернет только те строки, которые имеют одинаковые главные ключи.
Возможен еще и третий вариант использования конструкции, когда в запросе через слово «on» указываются поля, по которым должно происходить объединение таблиц. В таком случае выборка вернет следующие данные:
Особенности использования Left Join
Если рассматривать еще один способ объединения таблиц с помощью конструкции MySQL – Join, можно заметить разницу в данных, которые выводятся. Таким механизмом является конструкция Left.
Использование конструкции Left Join MySQL имеет некоторые особенности и, как и Inner, требует четкого понимания результата, который необходимо получить.
В данном случае сначала будут выбраны все записи из первой таблицы, а в дальнейшем к ним будут присоединены записи из второй таблицы свойств. При этом, если в первой таблице есть запись, например, «табурет», а во второй таблице нет ни одного свойства для нее, то оператор Left выведет напротив этой записи значение null, что говорит программисту о том, что признаков по этому виду предмета нет.
Использование этой конструкции позволят определять, по каким полям или, например, товарам в магазине не выставлена цена, гарантийный срок и так далее.
Пример использования Left
Для рассмотрения на практике оператора конструкции Left Join MySQL используем ранее описанные таблицы. Скажем, необходимо выбрать весь список товаров, которые есть в магазине, и проверить по каким из них не проставлены признаки или свойства. В таком случае выборка выведет на экран все товары, а по тем, у которых нет свойства, будут проставлены пустые значения.
Использование Where в конструкции Join
Как параметр, объединение может включать не только указания полей, по которым необходимо произвести подключение таблиц, но может и включать оператор условия Where.
Для примера рассмотрим скрипт, который должен вернуть нам только те записи, по которым не проставлен признак. В таком случае в конструкцию Join необходимо добавить оператор условия и указать, что именно нужно вернуть как результат.
При использовании в MySQL Join – Where нужно четко понимать, что будут показаны только те записи, к которым относится указанное условие, и выборка тогда будет выглядеть следующим образом:
Такие запросы позволяют делать выборки по конкретным данным, которые относятся к выбранному программистом условию. Таких условий можно указывать несколько, максимально при этом уточняя параметры выбора данных с объединённых таблиц.
Использование Join для изменения данных в таблицах
Конструкция Join, по сути, универсальна. Она позволяет не только делать разнообразные выборки, но и подключать в запросы от одной до нескольких таблиц, вводить дополнительные условия в выборку. Конструкцию можно также использовать и для других операций с данными. Так, Join можно использовать для изменения данных в таблице. Вернее сказать, для уточнения условий в таблице или же в тех случаях, если нужно обновить данные в нескольких таблицах по одним и тем же условиям.
Для примера рассмотрим такую задачу. Дано три таблицы, в которых имеются некоторые данные. Нужно провести изменение данных в обеих таблицах, используя один запрос. Как раз для решения такого рода заданий можно применять в команде Update конструкцию Join. Сам тип конструкции Join зависит, так же, как и в случае с выборкой данных, от результата, который хочет получить программист.
Рассмотрим самый простой пример. Нужно обновить одним запросом данные по одним и тем же условиям. Такого рода запросы строятся для оптимизации работы с базой данных. Зачем писать разные запросы для каждой из таблиц, если можно провести все манипуляции с данными одним запросом? Пример MySQL Update Join в нашем случае будет таким:
Построение сложных запросов
Довольно часто при работе с базой данных необходимо строить запросы не только с объединением нескольких таблиц, но и с использованием подзапросов. Такие задачи довольно сложны для понимания начинающего программиста баз данных. Сложность состоит в том, что приходится продумывать каждый шаг, определять, какие данные из какой таблицы или запроса необходимо получить и как в дальнейшем с ними нужно будет работать.
Для более конкретного понимания можно рассмотреть (в MySQL Join) примеры сложных запросов. Если вы новичок и только начинаете работать с базами данных, то такой тренинг пойдет лишь на пользу. Идеальным вариантом, будут MySQL Left Join примеры.
Данный запрос вернет нам 58 записей о договорах продажи, по которым заполнен или существует баланс денежных средств на выбранную дату. В данном случае это текущая дата. Также в выборку добавлено условие, что в названии договора должны быть символы — «123». Выводимая на экран информация (данные), будет иметь сортировку – упорядоченность по номеру договора.
Следующий пример выведет на экран данные обо всех платежах, в которых будет указан номер договора.
Использование подзапросов
Как уже говорилось ранее, при работе с базами данных можно объединить не только таблицы, но и таблицу с запросом. Такая конструкция используется в основном для ускорения работы запроса и его оптимизации.
К примеру, если необходимо из таблицы, которая имеет несколько сот полей и, скажем, тысячу записей, выбрать всего два поля, то стоит использовать запрос, который вернет только необходимые поля, и объединить его с основной выборкой данных. Как пример MySQL Join Select можно рассмотреть запрос такого типа:
Это не все способы использования стандартных конструкций MySQL, а только стандартные. Как использовать конструкцию Join и в каких ее видах, решает сам программист, но стоит помнить и учитывать то, какой результат надо получить при выполнении запроса.
fb.ru