Различия

Показаны различия между двумя версиями страницы.

Ссылка на это сравнение

Следующая версия		 Предыдущая версия
sql:mysql:join [2015/09/26 13:30] – создано - внешнее изменение 127.0.0.1sql:mysql:join [2024/10/30 20:31] (текущий) – mirocow
Строка 3: Строка 3:
 ====== Join / Sql / database ====== ====== Join / Sql / database ======
  
 +{{:sql:mysql:sql-join_dmi3vo.jpg?600|}}
  
-Имеем две таблицы+===== JOINS =====
  
-    +=== author ===
-  U) users               D) departments +
-  id name       d_id     id  name +
-  -- ----       ----     --  ---- +
-  1  Владимир    1       1  Сейлз +
-  2  Антон       2       2  Поддержка +
-  3  Александр   6       3  Финансы +
-  4  Борис       2       4  Логистика +
-  5  Юрий        4 +
- +
  
 +^ id  ^ name  ^
 +| 1   | Вася  |
 +| 2   | Коля  |
 +| 3   | Петя  |
  
-  +=== util ===
-<code> +
-SELECT u.id, u.name, d.name AS d_name +
-FROM users u +
-INNER JOIN departments d ON u.d_id = d.id +
-</code> +
- +
  
 +^ id  ^ author_id  | name                            |
 +| 1   | 1          | Вася Мега продукт               |
 +| 2   | 0          | Крутая, но неизвестная утилита  |
  
-Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN. В результате не присутствует: +===== SQL INNER / INNER JOIN - естественное или внутреннее соединение =====
-- пользователь Александр (отдел 6) +
-- отдел Финансы (нет пользователей) +
  
-   +Возвращает записи, у которых есть соответствующие записи в обеих таблицах.
-  id name       d_name +
-  -- --------   --------- +
-  1  Владимир    Сейлз +
-  2  Антон       Поддержка +
-  4  Борис       Поддержка +
-  3  Юрий        Логистика +
- +
  
 +<code sql>
 +-- Выводим все поля
 +select *
 +-- Указываем таблицу авторов
 +from author
 +-- Соединяем с таблицей программ
 +inner join util
 +-- Указываем условие
 +on author.id = util.author_id
 +</code>
  
 +^ id  ^ name  | id  | author_id  | name               |
 +| 1   | Вася  | 1   | 1          | Вася Мега продукт  |
  
-{{:sql:mysql:join-inner.gif?220|}}+===== LEFT JOIN =====
  
-рис. Inner join+Возвращает все записи из левой таблицы и соответствующие записи из правой таблицы. Если соответствия нет, возвращает NULL для правой таблицы.
  
-INNER JOIN это синоним для JOIN. Выбираются только совпадающие данные из объединяемых таблиц. Чтобы получить данные, которые не подходят по условию, необходимо использовать OUTER JOIN. Такое объединение вернет данные из обеих таблиц совпадающими по одному из условий.+<code sql> 
 +-- Выводим все поля 
 +select * 
 +-- Указываем таблицу авторов 
 +from author 
 +-- Соединяем с таблицей программ 
 +left join util 
 +-- Указываем условие 
 +on author.id = util.author_id 
 +</code>
  
 +^ id  ^ name  | id    | author_id  | name               |
 +| 1   | Вася  | 1     | 1          | Вася Мега продукт  |
 +| 2   | Коля  | NULL  | NULL       | NULL               |
 +| 3   | Петя  | NULL  | NULL       | NULL               |
  
-{{:sql:mysql:join-left.gif?220|}}+===== RIGHT JOIN =====
  
-рис. Left join+Возвращает все записи из правой таблицы и соответствующие записи из левой таблицы. Если соответствия нет, возвращает NULL для левой таблицы.
  
-Существует два типа внешнего объединения (OUTER JOIN) - LEFT OUTER JOIN и RIGHT OUTER JOIN. Работают они одинаково, разница заключается в том что LEFT - указывает что "внешней" таблицей будет находящаяся слева. В нашем примере это таблица users. +<code sql> 
- +-- Выводим все поля 
-  +select * 
-<code> +-- Указываем таблицу авторов 
-SELECT u.id, u.name, d.name AS d_name +from author 
-FROM users u +-- Соединяем с таблицей программ 
-LEFT OUTER JOIN departments d ON u.d_id = d.id+right join util 
 +-- Указываем условие 
 +on author.id = util.author_id
 </code> </code>
-  
  
 +^ id    ^ name  | id  | author_id  | name                            |
 +| 1     | Вася  | 1   | 1          | Вася Мега продукт               |
 +| NULL  | NULL  | 2   | 0          | Крутая, но неизвестная утилита  |
  
-Получаем полный список пользователей и сопоставленные департаменты.+===== LEFT OUTER JOIN - левое внешнее соединение =====
  
-   +Возвращает все записи из левой таблицы и соответствующие записи из правой таблицы. Если соответствия нет, возвращает NULL для правой таблицы.
-  id      name          d_name +
-  --      --------      --------- +
-  1       Владимир      Сейлз +
-  2       Антон         Поддержка +
-  3       Александр     NULL +
-  4       Борис         Поддержка +
-  5       Юрий          Логистика +
-  +
  
-{{:sql:mysql:join-left-outer.gif?220|}}+<code sql>
  
-рис. Left outer join с фильтрацией по полю 
- 
-RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей. 
- 
-  
-<code> 
-SELECT u.id, u.name, d.name AS d_name 
-FROM users u 
-RIGHT OUTER JOIN departments d ON u.d_id = d.id  
-WHERE u.id IS NULL 
 </code> </code>
-  
  
 +===== RIGHT OUTER JOIN - правое внешнее соединение =====
  
-   +Возвращает все записи из правой таблицы и соответствующие записи из левой таблицы. Если соответствия нет, возвращает NULL для левой таблицы.
-  id      name        d_name +
-  --      --------    --------- +
-  1       Владимир    Сейлз +
-  2       Антон       Поддержка +
-  4       Борис       Поддержка +
-  NULL    NULL        Финансы +
-  5       Юрий        Логистика +
-  +
  
 +<code sql>
  
-Дополнительно можно отфильтровать данные, проверяя их на NULL. +</code>
-В нашем примере указав WHERE u.id IS null, мы отбросим записи, в которых пользователи не числятся в отделах.+
  
-Информация: +===== FULL JOIN - полное внешнее соединение =====
-  * http://www.somecoders.com/2006/02/explanation-of-the-different-sql-joins/ +
-  * http://www.khankennels.com/blog/index.php/archives/2007/04/20/getting-joins/ +
-  * http://www.codinghorror.com/blog/archives/000976.html +
-  * http://ru.wikipedia.org/wiki/Join_(SQL) +
-  * http://en.wikipedia.org/wiki/Join_(SQL)+
  
 +Возвращает все записи, когда есть соответствие в одной из таблиц.
  
-====== Self joins ======+<code sql>
  
 +</code>
  
-Так называемая выборка с замыканием на себя (и совсем не closure). Нужна нам, если необходимо выбрать более одного +===== CROSS JOIN - перекрестное или декартово соединение =====
-значения из таблицы для нескольких условий. +
- +
-Имеем: набор фильтров для информации, значения которых сохраняются в табличке filts_data. +
-Необходимо: фильтровать продукты по дате, артикулу и имеющимся фильтрам +
- +
-  +
-<code php> +
-CREATE TABLE filts_data +
-( +
-  id serial NOT NULL, +
-  fid integer NOT NULL,  -- product_item.id  +
-  value integer NOT NULL, -- значение фильтра filts_items.id +
-  pid integer NOT NULL -- фильтр filts.id +
-) +
-</code>  +
-  +
- +
- +
-Есть таблица условных товаров product_item +
- +
-  +
-<code> +
-CREATE TABLE product_item +
-( +
-  id serial NOT NULL, +
-  section_id integer, +
-  date timestamp, +
-  art text, +
-  title text +
-) +
-</code>  +
- +
  
 +Выполняет декартово произведение двух таблиц, объединяя каждую строку первой таблицы с каждой строкой второй таблицы.
  
-Пример: выбрать записи, добавленные после 17/01/2009 и с установленными фильтрами 3=14 и 4=15 и 6=19. +<code sql>
-Логика подскажет нам такой запрос (нерабочий):+
  
-<code> 
-SELECT p1.title FROM products_item p1 INNER JOIN filts_data p2 ON p1.id = p2.fid  
-WHERE p1.date > '17.01.2009' AND (p2.pid = 3 AND p2.value = 14) AND (p2.pid = 4 AND p2.value = 15) AND (p2.pid = 6 AND p2.value = 19) 
 </code> </code>
  
 +===== UNIOUN =====
  
-Этот запрос не найдет элементов в таблице. +<code sql>
-Перепишем запрос, используя join на себя:+
  
-<code> 
-SELECT p1.* FROM product_item p1 INNER JOIN filts_data p2 ON p1.id = p2.fid  INNER JOIN filts_data p3 ON p1.id = p3.fid  INNER JOIN filts_data p4 ON p1.id = p4.fid WHERE p1.date > '17.01.2009' AND (p2.pid = 3 AND p2.value = 14) AND (p3.pid = 4 AND p3.value = 15) AND (p4.pid = 6 AND p4.value = 19) 
 </code> </code>
  
  
-В этом случае мы получим записи, для которых установлены все три фильтра и дата добавления позднее заданной.