Розробка будь-якої бази даних має на увазі не лише створення та наповнення таблиць різноманітною інформацією, але і подальшу роботу з даними. Для коректного виконання різноманітних завдань з вибору даних з таблиць і формування звітів, використовується стандартна конструкція Select.
Вибірки даних з таблиць
Якщо розглядати задачу вибору даних або побудови деякого звіту, можна визначити рівень складності даної операції. Як правило, при роботі з серйозними (за обсягом інформації) базами даних, які формуються, наприклад, в інтернет-магазинах або великих компаніях, вибірка даних не буде обмежуватися лише однією таблицею. Як правило, вибірки можуть бути з досить великої кількості не тільки пов'язаних між собою таблиць, але і вкладених запитів / підзапитів, які складає сам програміст, в залежності від поставленої перед ним завдання. Для вибірки з однієї таблиці можна використовувати найпростішу конструкцію:
| Select * from Person |
де Person - ім'я таблиці, з якої необхідно зробити вибірку даних.
Якщо ж буде необхідність вибрати дані з декількох таблиць, можна використовувати одну з стандартних конструкцій для об'єднання декількох таблиць.
Способи підключення додаткових таблиць
Якщо розглядати використання такого роду конструкцій на початковому рівні, то можна виділити наступні механізми підключення необхідної кількості таблиць для вибірки, а саме:
- Оператор Inner Join.
- Left Join або, це другий спосіб запису, Left Outer Join.
- Cross Join.
- Full Join.
Використання операторів об'єднання таблиць на практиці можна засвоїти, розглянувши застосування оператора SQL - Inner Join. Приклад його використання буде виглядати наступним чином:
Select * from Person |
Мова SQL і оператор Join Inner Join можна використовувати не тільки для об'єднання двох і більше таблиць, але і для підключення інших підзапитів, що значно полегшує роботу адміністраторів бази даних і, як правило, може значно прискорити виконання певних, складних за структурою запитів.
Об'єднання даних в таблицях через підрядник
Якщо розглядати підключення великої кількості підзапитів і складання даних в єдину таблицю рядок за рядком, то можна використовувати також оператори Union, і Union All.
Застосування цих конструкцій буде залежати від поставленої перед розробником завдання і результату, якого він хоче досягти в результаті.
Опис оператора Inner Join
У більшості випадків для об'єднання декількох таблиць в мові SQL використовується оператор Inner Join. Опис Inner Join в SQL досить просте для розуміння середньостатистичного програміста, який тільки починає розбиратися в базах даних. Якщо розглянути опис механізму роботи цієї конструкції, то отримаємо таку картину. Логіка оператора в цілому побудована на можливості перетину і вибірки тільки тих даних, які є в кожної з вхідних в запит таблиць.
Якщо розглянути таку роботу з точки зору графічної інтерпретації, то отримаємо структуру оператора SQL Inner Join, приклад якої можна показати за допомогою такої схеми:
Наприклад, ми маємо дві таблиці, схема яких показана на малюнку. Вони в свою чергу, мають різну кількість записів. У кожній з таблиць є поля, які пов'язані між собою. Якщо спробувати пояснити роботу оператора виходячи з малюнка, то повертається результат буде у вигляді набору записів з двох таблиць, де номери пов'язаних між собою полів збігаються. Простіше кажучи, запит поверне тільки ті записи (з таблиці номер два), дані про яких є в таблиці номер один.
Синтаксис оператора Inner Join
Як вже говорилося раніше, оператор Inner Join, а саме його синтаксис, надзвичайно простий. Для організації зв'язків між таблицями в межах однієї вибірки досить буде запам'ятати і використовувати наступну принципову схему побудови оператора, яка прописується в одну строчку програмного SQL-коду, а саме:
- Inner Join [Ім'я таблиці] on [ключове поле з таблиці, до якої підключаємо] \u003d [Ключовому полю підключається таблиці].
Для зв'язку в даному операторі використовуються головні ключі таблиць. Як правило, в групі таблиць, які зберігають інформацію про співробітників, раніше описані Person і Subdivision мають хоча б по одній схожою записи. Отже, розглянемо докладніше Inner Join, приклад якого був показаний дещо раніше.
Приклад та опис підключення до вибірці однієї таблиці
У нас є таблиця Person, де зберігається інформація про всіх співробітників, що працюють в компанії. Відразу відзначимо, що головним ключем даної таблиці є поле - Pe_ID. Якраз по ньому і буде йти зв'язка.
Друга таблиця Subdivision буде зберігати інформацію про підрозділи, в яких працюють співробітники. Вона, в свою чергу, пов'язана з допомогою поля Su_Person з таблицею Person. Про що це говорить? Виходячи зі схеми даних можна сказати, що в таблиці підрозділів для кожного запису з таблиці «Співробітники» буде інформація про відділ, в якому вони працюють. Саме по цьому зв'язку і буде працювати оператор Inner Join.
Для більш зрозумілого використання розглянемо оператор SQL Inner Join (приклади його використання для однієї і двох таблиць). Якщо розглядати приклад для однієї таблиці, то тут все досить просто:
Select * from Person Inner join Subdivision on Su_Person \u003d Pe_ID |
Приклад підключення двох таблиць і підзапиту
Оператор SQL Inner Join, приклади використання якого для вибірки даних з декількох таблиць можна організувати як вказано вище, працює по чуть ускладненому принципом. Для двох таблиць ускладнити завдання. Скажімо, у нас є таблиця Depart, в якій зберігається інформація про всі відділах в кожному з підрозділів. У в цю таблицю записаний номер підрозділу і номер співробітника і потрібно доповнити вибірку даних назвою кожного відділу. Забігаючи вперед, варто сказати, що для вирішення цього завдання можна скористатися двома методами.
Перший спосіб полягає в підключенні таблиці відділів до вибірці. Організувати запит в цьому випадку можна таким чином:
Варто зазначити, що така конструкція не завжди може прискорити роботу запиту. Іноді бувають випадки, коли доводиться використовувати додатково вибірку даних в тимчасову таблицю (якщо їх обсяг занадто великий), а потім її об'єднувати з основною вибіркою.
Приклад використання оператора Inner Join для вибірок з великої кількості таблиць
Побудова складних запитів на увазі використання для вибірки даних значної кількості таблиць і підзапитів, пов'язаних між собою. Цим вимогам може задовольнити SQL Inner Join синтаксис. Приклади використання оператора в даному випадком можуть ускладнюватися не тільки вибірками з багатьох місць зберігання даних, але і з великої кількості вкладених підзапитів. Для конкретного прикладу можна взяти вибірку даних з системних таблиць (оператор Inner Join SQL). Приклад - 3 таблиці - в цьому випадку буде мати досить складну структуру.
В даному випадку підключено (до основної таблиці) ще три додатково і введено кілька умов вибору даних.
При використанні оператора Inner Join варто пам'ятати про те, що чим складніше запит, тим довше він буде реалізовуватися, тому варто шукати шляхи більш швидкого виконання і вирішення поставленого завдання.
висновок
У підсумку хотілося б сказати одне: робота з базами даних - це не найскладніше, що є в програмуванні, тому при бажанні абсолютно кожна людина зможе оволодіти знаннями з побудови баз даних, а з часом, набравшись досвіду, вийде працювати з ними на професійному рівні .
Продовжуємо вивчати основи SQL, І прийшов час поговорити про простих об'єднаннях JOIN. І сьогодні ми розглянемо, як об'єднуються дані за коштами операторів LEFT JOIN, RIGHT JOIN, CROSS JOIN і INNER JOIN, Іншими словами, навчимося писати запити, які об'єднують дані, і як зазвичай вивчати все це будемо на прикладах.
Об'єднання JOIN дуже важливі в SQL, так як без уміння писати запити з об'єднанням даних різних об'єктів, просто не обійтися програмісту SQL, та й просто адміну який час від часу вивантажує якісь дані з бази даних, тому це відноситься до основ SQL і кожен людина, яка має справу з SQL, повинен мати уявлення, що це таке.
Примітка! Всі приклади будемо писати в Management Studio SQL Server 2008.
Ми з Вами вже давно вивчаємо основи SQL, і якщо згадати починали ми з оператора select, і взагалі було вже багато матеріалу на цьому сайті з SQL, наприклад:
І багато іншого, навіть вже розглядали об'єднання union і union all, але, так чи інакше, більш докладно саме про об'єднання join ми з Вами не розмовляли, тому сьогодні ми заповнимо цю прогалину в наших знаннях.
І почнемо ми як завжди з невеликою теорії.
об'єднання JOIN - це об'єднання двох або більше об'єктів бази даних по засобам певного ключа або ключів або в разі cross join і зовсім без ключа. Під об'єктами тут мається на увазі різні таблиці, уявлення (views), табличні функції або просто підзапити sql, тобто все, що повертає табличні дані.
Об'єднання SQL LEFT і RIGHT JOIN
LEFT JOIN - це об'єднання даних по лівому ключу, тобто допустимо, ми об'єднуємо дві таблиці по left join, і це значить що всі дані з другої таблиці підтягнуться до першої, а в разі відсутності ключа виведеться NULL значення, іншими словами виведуться всі дані з лівої таблиці і всі дані по ключу з правої таблиці.
RIGHT JOIN - це таке ж об'єднання як і Left join тільки будуть виводитися всі дані з правої таблиці і тільки ті дані з лівої таблиці в яких є ключ об'єднання.
Тепер давайте розглядати приклади, і для початку створимо дві таблиці:
CREATE TABLE ((18, 0) NULL, (50) NULL) ON GO CREATE TABLE ((18, 0) NULL, (50) NULL) ON GO
Ось такі простенькі таблиці, І я для прикладу заповнив їх ось такими даними:
Тепер давайте напишемо запит з об'єднанням цих таблиць по ключу number, для початку по LEFT:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 LEFT JOIN test_table_2 t2 ON t1.number \u003d t2.number
Як бачите, тут дані з таблиці t1 вивелися все, а дані з таблиці t2 не всі, так як рядки з number \u003d 4 там немає, тому і вивелися NULL значення.
А що буде, якщо б ми об'єднували за коштами right join, а було б ось це:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 RIGHT JOIN test_table_2 t2 ON t1.number \u003d t2.number
Іншими словами, вивелися всі рядки з таблиці t2 і відповідні записи з таблиці t1, так як всі ті ключі, які є в таблиці t2, є і в таблиці t1, і тому у нас немає NULL значень.
Об'єднання SQL INNER JOIN
Inner join - це об'єднання коли виводяться всі записи з однієї таблиці і всі відповідні записи з іншої таблиці, а ті записи яких немає в одній або в іншій таблиці виводитися не будуть, тобто тільки ті записи які відповідають ключу. До речі відразу скажу, що inner join це те ж саме, що і просто join без Inner. приклад:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 INNER JOIN test_table_2 t2 on t1.number \u003d t2.number
А тепер давайте спробуємо об'єднати наші таблиці за двома ключам, для цього трохи згадаємо, як додавати колонку в таблицю і як оновити дані через update, так як в наших таблицях всього дві колонки, і об'єднувати по текстовому полю якось не добре. Для цього додамо колонки:
ALTER TABLE test_table ADD number2 INT ALTER TABLE test_table_2 ADD number2 INT
Оновимо наші дані, просто проставимо в колонку number2 значення 1:
UPDATE test_table SET number2 \u003d 1 UPDATE test_table_2 SET number2 \u003d 1
І давайте напишемо запит з об'єднанням по двом ключам:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 INNER JOIN test_table_2 t2 ON t1.number \u003d t2.number AND t1.number2 \u003d t2.number2
І результат буде таким же, як і в попередньому прикладі:
Але якщо ми, припустимо в другій таблиці в одному рядку змінимо, поле number2 на значення скажімо 2, то результат буде вже зовсім інший.
UPDATE test_table_2 set number2 \u003d 2 WHERE number \u003d 1
Запит той же самий, а от результат:
Як бачите, по другому ключу у нас одна рядок не вивелася.
Об'єднання SQL CROSS JOIN
CROSS JOIN - це об'єднання SQL за якими кожен рядок однієї таблиці об'єднується з кожним рядком іншої таблиці. Особисто у мене це об'єднання рідко потрібно, але все одно іноді потрібно, тому Ви також повинні вміти його використовувати. Наприклад, в нашому випадку вийде, звичайно, не зрозуміло що, але все одно давайте спробуємо, тим більше синтаксис трохи відрізняється:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text FROM test_table t1 CROSS JOIN test_table_2 t2
Тут у нас кожному рядку таблиці test_table відповідає кожен рядок з таблиці test_table_2, тобто в таблиці test_table у нас 4 рядки, а в таблиці test_table_2 3 рядка 4 помножити 3 і буде 12, як і у нас вивелося 12 рядків.
І наостанок, давайте покажу, як можна об'єднувати кілька таблиць, для цього я, просто для прикладу, кілька разів об'єднаю нашу першу таблицю з другої, сенсу в об'єднання в даному випадку, звичайно, немає але, Ви побачите, як можна це робити і так приступимо:
SELECT t1.number as t1_number, t1.text as t1_text, t2.number as t2_number, t2.text as t2_text, t3.number as t3_number, t3.text as t3_text, t4.number as t4_number, t4.text as t4_text FROM test_table t1 LEFT JOIN test_table_2 t2 on t1.number \u003d t2.number RIGHT JOIN test_table_2 t3 on t1.number \u003d t3.number INNER JOIN test_table_2 t4 on t1.number \u003d t4.number
Як бачите, я тут поєдную і по left і по right і по inner просто, для того щоб це було наочно.
З об'єднаннями я думаю досить, тим більше нічого складного в них немає. Але на цьому вивчення SQL не закінчене в наступних статтях ми продовжимо, а поки тренуйтеся і пишіть свої запити. Успіхів!
У Oracle підтримується кілька типів з'єднань, що відрізняються способом, яким проводиться об'єднання рядків з двох або більше таблиць або уявлень. У цій замітці мого блогу будуть описані типи з'єднань, що застосовуються в Oracle найбільш часто.
еквісоедіненія
При еквісоедіненія (equi-join) дві або більше таблиць з'єднуються на підставі умови рівності між стовпцями. Іншими словами, один і той же стовпець має однакове значення в усіх з'єднуються таблицях. Нижче наведено приклад застосування еквісоедіненія:
SQL\u003e SELECT e.last_name, d.dept FROM emp e, dept d WHERE e.emp_id \u003d d.emp_id;
Для показаного вище оператора з'єднання також можна використовувати і наступний новий синтаксис:
SQL\u003e SELECT e.last_name, d.dept FROM emp e JOIN dept d USING (emp_id);
При бажанні з'єднати декілька стовпців, можна перерахувати їх імена у вигляді розділеного комами списку, наприклад: USING (dept_id, emp_name).
природне з'єднання
Природним з'єднанням (natural join) називається еквісоедіненія, при якому стовпці, які повинні зіставлятися для виконання з'єднання, спеціально не вказуються. Oracle автоматично визначає підлягають з'єднанню стовпці на підставі співпадаючих стовпців в двох таблицях. Нижче наведено приклад застосування природного з'єднання:
SQL\u003e SELECT e.last_name, d.dept FROM emp e NATURAL JOIN dept d;
У цьому прикладі умовою для виконання з'єднання служить наявність ідентичних значень в стовпці last_name в таблицях emp і dept.
рефлексивне з'єднання
Під рефлексією з'єднанням (self join) мається на увазі з'єднання таблиці з самою собою за рахунок використання псевдонімів. У наступному прикладі здійснюється з'єднання таблиці employees з самою собою за допомогою псевдоніма з видаленням всіх дубльованих рядків.
SQL\u003e DELETE FROM employees X WHERE ROWID\u003e 2 (select MIN (rowid) FROM employees Y 3 where X.key_values \u200b\u200b\u003d Y.key_values);
внутрішнє з'єднання
Внутрішнє з'єднання (inner join), також зване простим з'єднанням (simple join), передбачає повернення всіх рядків, які задовольняють зазначеному умові з'єднання. Раніше в синтаксисі внутрішнього сполучення для вказівки того, яким чином повинні з'єднуватися таблиці, потрібно було використовувати конструкцію WHERE, наприклад, так:
SQL\u003e SELECT e.flast_name, d.dept FROM emp e, dept d WHERE e.emp_id \u003d d.emp_id;
Тепер Oracle дозволяє задавати критерії з'єднання в синтаксисі внутрішнього (або простого) з'єднання за рахунок застосування нової конструкції ON або USING, наприклад:
SQL\u003e SELECT DISTINCT NVL (dname, "No Dept"), COUNT (empno) nbr_emps FROM emp JOIN DEPT ON emp.deptno \u003d dept.deptno WHERE emp.job IN ( "MANAGER", "SALESMAN", "ANALYST") GROUP BY dname;
Зовнішнє з'єднання
Зовнішнє з'єднання (outer join) застосовується для повернення всіх рядків, які задовольняють зазначеному умові з'єднання, плюс деяких або всіх рядків з таблиці, в якій немає відповідних рядків, які відповідають вказаним умові з'єднання. Існують три види зовнішнього з'єднання: ліве зовнішнє з'єднання (left outer join), праве зовнішнє з'єднання (right outer join) і повне зовнішнє з'єднання (full outer join). В операторі повного зовнішнього з'єднання слово OUTER зазвичай опускається.
Операція з'єднання, як і інші бінарні операції, призначена для забезпечення вибірки даних з двох таблиць і включення цих даних в один результуючий набір. Відмінною особливістю операції з'єднання є наступне:
- в схему таблиці-результату входять стовпці обох вихідних таблиць (таблиць-операндів), тобто схема результату є «зчепленням» схем операндів;
- кожен рядок таблиці-результату є «зчепленням» рядки з однієї таблиці-операнда з рядком другої таблиці-операнда.
Визначення того, які саме вихідні рядки увійдуть в результат і в яких поєднаннях, залежить від типу операції з'єднання і від явно заданого умови з'єднання. Умова з'єднання, тобто умова зіставлення рядків вихідних таблиць один з одним, є логічне вираження (предикат).
При необхідності з'єднання не двох, а кількох таблиць, операція з'єднання застосовується кілька разів (послідовно).
опис оператора
SELECT FIELD [... n] FROM Table1 (INNER | (LEFT | RIGHT | FULL) OUTER | CROSS) JOIN Table2 ON< condition>
У більшості СУБД при вказівці слів LEFT, RIGHT, FULL слово OUTER можна опустити. Слово INNER також в більшості СУБД можна опустити.
У загальному випадку СУБД при виконанні з'єднання перевіряє умова (предикат) condition. Для CROSS JOIN умова не вказується.
Для перехресного з'єднання (декартова твори) CROSS JOIN в деяких реалізаціях SQL використовується оператор «кома» ( , ):
SELECT FIELD [... n] FROM Table1, Table2
Види оператора JOIN
Для подальших пояснень будуть використовуватися такі таблиці:
Люди, які проживають у міських поселеннях (таблиця Person)
INNER JOIN
оператор у внутрішньому сполученні INNER JOIN з'єднує дві таблиці. Порядок таблиць для оператора неважливий, оскільки оператор є симетричним.
Заголовок конкатенацией
тіло результату логічно формується таким чином. Кожен рядок однієї таблиці зіставляється з кожним рядком другої таблиці, після чого для отриманої «з'єднаної» рядки перевіряється умова з'єднання (обчислюється предикат з'єднання). Якщо умова істинно, в таблицю-результат додається відповідна «поєднана» рядок.
Описаний алгоритм дій є строго логічним, тобто він лише пояснює результат, який повинен вийти при виконанні операції, але не наказує, щоб конкретна СУБД виконувала з'єднання саме зазначеним чином. Існує безліч способів реалізації операції з'єднання, наприклад з'єднання вкладеними циклами (англ. inner loops join), З'єднання хешем (англ. hash join), З'єднання злиттям (англ. merge join). Єдина вимога полягає в тому, щоб будь-яка реалізація логічно давала такий же результат, як при застосуванні описаного алгоритму.
SELECT * FROM Person INNER JOIN City ON Person. CityId \u003d City. Id
результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| Сергій | 1 | 1 | Москва |
OUTER JOIN
З'єднання двох таблиць, в результат якого в обов'язковому порядку входять рядки або однієї, або обох таблиць.
LEFT OUTER JOIN
оператор лівого зовнішнього з'єднання LEFT OUTER JOIN з'єднує дві таблиці. Порядок таблиць для оператора важливий, оскільки оператор не є симетричним.
Заголовок таблиці-результату є об'єднанням (конкатенацией) заголовків з'єднуються таблиць.
тіло p.
- p.
- Потім в результат додаються ті записи лівої таблиці, які не ввійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, відповідні правої таблиці, заповнюються значеннями NULL.
SELECT * FROM Person LEFT OUTER JOIN City ON Person. CityId \u003d City. Id
результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| Сергій | 1 | 1 | Москва |
| Григорій | 4 | NULL | NULL |
RIGHT OUTER JOIN
оператор правого зовнішнього з'єднання RIGHT OUTER JOIN з'єднує дві таблиці. Порядок таблиць для оператора важливий, оскільки оператор не є симетричним.
Заголовок таблиці-результату є об'єднанням (конкатенацией) заголовків з'єднуються таблиць.
тіло результату логічно формується таким чином. Нехай виконується з'єднання лівої і правої таблиць по предикату (умовою) p.
- В результат включається внутрішнє з'єднання (INNER JOIN) лівої і правої таблиць по предикату p.
- Потім в результат додаються ті записи правої таблиці, які не ввійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, відповідні лівої таблиці, заповнюються значеннями NULL.
SELECT * FROM Person RIGHT OUTER JOIN City ON Person. CityId \u003d City. Id
результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Сергій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
FULL OUTER JOIN
оператор повного зовнішнього з'єднання FULL OUTER JOIN з'єднує дві таблиці. Порядок таблиць для оператора неважливий, оскільки оператор є симетричним.
Заголовок таблиці-результату є об'єднанням (конкатенацией) заголовків з'єднуються таблиць.
тіло результату логічно формується таким чином. Нехай виконується з'єднання першої і другої таблиць по предикату (умовою) p. Слова «першої» і «другий» тут не позначають порядок в запису (який не важлива), а використовуються лише для розрізнення таблиць.
- В результат включається внутрішнє з'єднання (INNER JOIN) першої та другої таблиць по предикату p.
- В результат додаються ті записи першої таблиці, які не ввійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, відповідні другій таблиці, заповнюються значеннями NULL.
- В результат додаються ті записи другий таблиці, які не ввійшли у внутрішнє з'єднання на кроці 1. Для таких записів поля, відповідні першій таблиці, заповнюються значеннями NULL.
SELECT * FROM Person FULL OUTER JOIN City ON Person. CityId \u003d City. Id
результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Сергій | 1 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| NULL | NULL | 3 | Казань |
| Григорій | 4 | NULL | NULL |
CROSS JOIN
оператор перехресного з'єднання, або декартова твори CROSS JOIN з'єднує дві таблиці. Порядок таблиць для оператора неважливий, оскільки оператор є симетричним.
Заголовок таблиці-результату є об'єднанням (конкатенацией) заголовків з'єднуються таблиць.
тіло результату логічно формується таким чином. Кожен рядок однієї таблиці з'єднується з кожним рядком другої таблиці, даючи тим самим в результаті всі можливі поєднання рядків двох таблиць.
SELECT * FROM Person CROSS JOIN City
SELECT * FROM Person, City
результат:
| Person.Name | Person.CityId | City.Id | City.Name |
|---|---|---|---|
| Андрій | 1 | 1 | Москва |
| Андрій | 1 | 2 | Санкт-Петербург |
| Андрій | 1 | 3 | Казань |
| Леонід | 2 | 1 | Москва |
| Леонід | 2 | 2 | Санкт-Петербург |
| Леонід | 2 | 3 | Казань |
| Сергій | 1 | 1 | Москва |
| Сергій | 1 | 2 | Санкт-Петербург |
| Сергій | 1 | 3 | Казань |
| Григорій | 4 | 1 | Москва |
| Григорій | 4 | 2 | Санкт-Петербург |
| Григорій | 4 | 3 | Казань |
Якщо в реченні WHERE додати умова з'єднання, тобто обмеження на поєднання кортежів, то результат еквівалентний операції INNER JOIN з таким же умовою:
Таким чином, CROSS JOIN + WHERE ( предикат) І INNER JOIN ( предикат) Синтаксично є альтернативними формами запису однієї і тієї ж логічної операції у внутрішньому сполученні. Синтаксис CROSS JOIN + WHERE для операції з'єднання називають застарілим, оскільки його не рекомендує стандарт SQL ANSI.
Примітки
посилання
- Опис видів Join в Oracle (рус.)
- Jeff Atwood, A Visual Explanation of SQL Joins (англ.)
| SQL | |
|---|---|
| версії | SQL-86 SQL-89 SQL-92 SQL: одна тисяча дев'ятсот дев'яносто дев'ять SQL: 2003 SQL: 2008 |
| Ключові слова | Create Delete From Having Insert Join Merge Null Order by Prepare Select Top Truncate Union Update |
| Related | Едгар Кодд Реляційна база даних |
Оператор мови SQL JOIN призначений для з'єднання двох або більше таблиць бази даних по збігається умові. Цей оператор існує тільки в реляційних базах даних. Саме завдяки JOIN реляційні бази даних мають таку потужну функціональність, яка дозволяє вести не тільки зберігання даних, але і їх, хоча б найпростіший, аналіз за допомогою запитів. Розберемо основні нюанси написання SQL-запитів з оператором JOIN, які є загальними для всіх СУБД (систем управління базами даних). Для з'єднання двох таблиць оператор SQL JOIN має наступний синтаксис:
SELECT ІМЕНА_СТОЛБЦОВ (1..N) FROM ІМЯ_ТАБЛІЦИ_1 JOIN ІМЯ_ТАБЛІЦИ_2 ON УМОВА
Після одного або декількох ланок з оператором JOIN може слідувати необов'язкова секція WHERE або HAVING, в якій, також, як в простому SELECT-запиті, задається умова вибірки. Загальним для всіх СУБД є те, що в цій конструкції замість JOIN може бути зазначено INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (або, як варіант, кома).
INNER JOIN (внутрішнє з'єднання)
Запит з оператором INNER JOIN призначений для з'єднання таблиць і виведення результуючої таблиці, в якій дані повністю перетинаються за умовою, вказаною після ON.
Те ж саме робить і просто JOIN. Таким чином, слово INNER - не обов'язкова.
Приклад 1. Є база даних порталу оголошень. У ній є таблиця Categories (категорії оголошень) і Parts (частини, або інакше - рубрики, які і відносяться до категорій). Наприклад, частини Квартири, Дачі відносяться до категорії Нерухомість, а частини Автомобілі, Мотоцикли - до категорії Транспорт. Ці таблиці з заповненими даними мають такий вигляд.
Таблиця Parts:
Зауважимо, що в таблиці Parts Книги мають Cat - посилання на категорію, якій немає в таблиці Categories, а в таблиці Categories Техніка має Cat_ID - первинний ключ, посилання на який немає в таблиці Parts. Потрібно з'єднати дані цих двох таблиць так, щоб в результуючій таблиці були поля Part (Частина), Cat (Категорія) і Price (Ціна подачі оголошення) і щоб дані повністю перетиналися за умовою. Умова - збіг ідентифікатора категорії в таблиці Categories і посилання на категорію в таблиці Parts. Для цього пишемо наступний запит:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS INNER JOIN CATEGORIES ON PARTS.Cat \u003d CATEGORIES.Cat_ID
| Part | Cat | Price |
| квартири | 505 | 210,00 |
| автомашини | 205 | 160,00 |
| дошки | 10 | 105,00 |
| Шафи | 30 | 77,00 |
У результуючій таблиці немає Книг, так як цей запис посилається на категорію, якій немає в таблиці Categories, і Техніки, так як цей запис має зовнішній ключ в таблиці Categories, на який немає посилання в таблиці Parts.
У ряді випадків при з'єднаннях таблиць скласти менш громіздкі запити можна за допомогою предиката EXISTS і без використання JOIN.
Є база даних "Театр". Таблиця Play містить дані про постановках. Таблиця Team - про ролі акторів. Таблиця Actor - про акторів. Таблиця Director - про режисерів. Поля таблиць, первинні та зовнішні ключі можна побачити на малюнку нижче (для збільшення натиснути лівою кнопкою миші).
Приклад 3. Вивести список акторів, які в одній виставі грають більше однієї ролі, і кількість їх ролей.
Оператор JOIN використовувати 1 раз. використовувати HAVING, GROUP BY .
Підказка. Оператор HAVING застосовується до числа ролей, підрахованих агрегатної функцією COUNT.
LEFT OUTER JOIN (ліве зовнішнє з'єднання)
Запит з оператором LEFT OUTER JOIN призначений для з'єднання таблиць і виведення результуючої таблиці, в якій дані повністю перетинаються за умовою, вказаною після ON, і доповнюються записами з першої по порядку (лівої) таблиці, навіть якщо вони не відповідають умові. У записів лівої таблиці, які не відповідають умові, значення стовпця з правої таблиці буде NULL (невизначеним).
Приклад 4. База даних і таблиці - ті ж, що і в прикладі 1.
Для отримання результуючої таблиці, в якій дані з двох таблиць повністю перетинаються за умовою і доповнюються усіма даними з таблиці Parts, які не відповідають умові, пишемо наступний запит:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS LEFT OUTER JOIN CATEGORIES ON PARTS.Cat \u003d CATEGORIES.Cat_ID
Результатом виконання запиту буде наступна таблиця:
| Part | Cat | Price |
| квартири | 505 | 210,00 |
| автомашини | 205 | 160,00 |
| дошки | 10 | 105,00 |
| Шафи | 30 | 77,00 |
| книги | 160 | NULL |
В результуючої таблиці, на відміну від таблиці з прикладу 1, є Книги, але значення стовпця Ціни (Price) у них - NULL, так як цей запис має ідентифікатор категорії, якої немає в таблиці Categories.
RIGHT OUTER JOIN (праве зовнішнє з'єднання)
Запит з оператором RIGHT OUTER JOIN призначений для з'єднання таблиць і виведення результуючої таблиці, в якій дані повністю перетинаються за умовою, вказаною після ON, і доповнюються записами з другої по порядку (правої) таблиці, навіть якщо вони не відповідають умові. У записів правої таблиці, які не відповідають умові, значення стовпця з лівої таблиці буде NULL (невизначеним).
Приклад 5.
Для отримання результуючої таблиці, в якій дані з двох таблиць повністю перетинаються за умовою і доповнюються усіма даними з таблиці Categories, які не відповідають умові, пишемо наступний запит:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS RIGHT OUTER JOIN CATEGORIES ON PARTS.Cat \u003d CATEGORIES.Cat_ID
Результатом виконання запиту буде наступна таблиця:
| Part | Cat | Price |
| квартири | 505 | 210,00 |
| автомашини | 205 | 160,00 |
| дошки | 10 | 105,00 |
| Шафи | 30 | 77,00 |
| NULL | 45 | 65,00 |
В результуючої таблиці, на відміну від таблиці з прикладу 1, є запис з категорією 45 і ціною 65,00, але значення стовпця Частини (Part) у неї - NULL, так як цей запис має ідентифікатор категорії, на яку немає посилань в таблиці Parts .
FULL OUTER JOIN (повне зовнішнє з'єднання)
Запит з оператором FULL OUTER JOIN призначений для з'єднання таблиць і виведення результуючої таблиці, в якій дані повністю перетинаються за умовою, вказаною після ON, і доповнюються записами з першої (лівої) і другий (правої) таблиць, навіть якщо вони не відповідають умові. У записів, які не відповідають умові, значення стовпців з іншої таблиці буде NULL (невизначеним).
Приклад 6. База даних і таблиці - ті ж, що і в попередніх прикладах.
Для отримання результуючої таблиці, в якій дані з двох таблиць повністю перетинаються за умовою і доповнюються усіма даними як з таблиці Parts, так і з таблиці Categories, які не відповідають умові, пишемо наступний запит:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS FULL OUTER JOIN CATEGORIES ON PARTS.Cat \u003d CATEGORIES.Cat_ID
Результатом виконання запиту буде наступна таблиця:
| Part | Cat | Price |
| квартири | 505 | 210,00 |
| автомашини | 205 | 160,00 |
| дошки | 10 | 105,00 |
| Шафи | 30 | 77,00 |
| книги | 160 | NULL |
| NULL | 45 | 65,00 |
У результуючій таблиці є записи Книги (з лівої таблиці) і з категорією 45 (з правої таблиці), причому у першій з них невизначена ціна (стовпець з правої таблиці), а у другій - невизначена частина (стовпець з лівої таблиці).
Псевдоніми з'єднуються таблиць
У попередніх запитах ми вказували з назвами видобутих стовпців з різних таблиць повні імена цих таблиць. Такі запити виглядають громіздко: одне і те ж слово повторюється кілька разів. Чи не можна якось спростити конструкцію? Виявляється, можна. Для цього слід використовувати псевдоніми таблиць - їх скорочені імена. Ім'я користувача може складатися і з однієї літери. Можливо будь-яку кількість букв в псевдонім, головне, щоб запит після скорочення був зрозумілий Вам самим. Загальне правило: в секції запиту, визначальною з'єднання, тобто навколо слова JOIN потрібно вказати повні імена таблиць, а за кожним ім'ям повинен слідувати псевдонім таблиці.
Приклад 7. Переписати запит з прикладу 1 з використанням псевдонімів з'єднуються таблиць.
Запит буде наступним:
SELECT P.Part, C.Cat_ID AS Cat, C.Price FROM PARTS P INNER JOIN CATEGORIES C ON P.Cat \u003d C.Cat_ID
Запит поверне те ж саме, що і запит в прикладі 1, але він набагато компактніше.
JOIN і з'єднання більше двох таблиць
Реляційні бази даних повинні підкорятися вимогам цілісності і ненадлишкових даних, в зв'язку з чим дані про один бізнес-процесі можуть міститися не тільки в одній, двох, але і в трьох і більше таблицях. У цих випадках для аналізу даних використовуються ланцюжки з'єднаних таблиць: наприклад, в одній (першої) таблиці міститься деякий кількісний показник, другу таблицю з першої і третьої пов'язують зовнішні ключі - дані перетинаються, але тільки третя таблиця містить умову, в залежності від якого може бути виведений кількісний показник з першої таблиці. І таблиць може бути ще більше. За допомогою оператора SQL JOIN в одному запиті можна поєднати велику кількість таблиць. У таких запитах за однією секцією з'єднання слід інша, причому кожен наступний JOIN з'єднує з наступною таблицею таблицю, яка була другою в попередньому ланці ланцюжка. Таким чином, синтаксис SQL запиту для з'єднання більше двох таблиць наступний:
SELECT ІМЕНА_СТОЛБЦОВ (1..N) FROM ІМЯ_ТАБЛІЦИ_1 JOIN ІМЯ_ТАБЛІЦИ_2 ON УМОВА JOIN ІМЯ_ТАБЛІЦИ_3 ON УМОВА ... JOIN ІМЯ_ТАБЛІЦИ_M ON УМОВА
Приклад 8. База даних - та ж, що і в попередніх прикладах. До таблиць Categories і Parts в цьому прикладі додасться таблиця Ads, яка містить дані про опубліковані на порталі оголошеннях. Наведемо фрагмент таблиці Ads, в якому серед записів є записи про тих оголошеннях, термін публікації яких закінчується 2018-04-02.
| A_Id | Part_ID | Date_start | Date_end | Text |
| 21 | 1 | "2018-02-11" | "2018-04-20" | "Продаю ..." |
| 22 | 1 | "2018-02-11" | "2018-05-12" | "Продаю ..." |
| ... | ... | ... | ... | ... |
| 27 | 1 | "2018-02-11" | "2018-04-02" | "Продаю ..." |
| 28 | 2 | "2018-02-11" | "2018-04-21" | "Продаю ..." |
| 29 | 2 | "2018-02-11" | "2018-04-02" | "Продаю ..." |
| 30 | 3 | "2018-02-11" | "2018-04-22" | "Продаю ..." |
| 31 | 4 | "2018-02-11" | "2018-05-02" | "Продаю ..." |
| 32 | 4 | "2018-02-11" | "2018-04-13" | "Продаю ..." |
| 33 | 3 | "2018-02-11" | "2018-04-12" | "Продаю ..." |
| 34 | 4 | "2018-02-11" | "2018-04-23" | "Продаю ..." |
Уявімо, що сьогодні "2018-04-02", тобто це значення приймає функція CURDATE () - поточна дата . Потрібно дізнатися, до яких категорій належать оголошення, термін публікації яких закінчується сьогодні. Назви категорій є тільки в таблиці CATEGORIES, а дати закінчення терміну публікації оголошень - тільки в таблиці ADS. У таблиці PARTS - частини категорій (або простіше, підкатегорії) опублікованих оголошень. Але зовнішнім ключем Cat_ID таблиця PARTS пов'язана з таблицею CATEGORIES, а таблиця ADS пов'язана зовнішнім ключем Part_ID з таблицею PARTS. Тому з'єднуємо в одному запиті три таблиці і цей запит можна з максимальною коректністю назвати ланцюжком.
Запит буде наступним:
Результат запиту - таблиця, яка містить назви двох категорій - "Нерухомість" і "Транспорт":
| Cat_name |
| Нерухомість |
| транспорт |
CROSS JOIN (перехресне з'єднання)
Використання оператора SQL CROSS JOIN в найбільш простій формі - без умови з'єднання - реалізує операцію декартова твори в реляційній алгебрі . Результатом такого з'єднання буде зчеплення кожного рядка першої таблиці з кожним рядком другої таблиці. Таблиці можуть бути записані в запиті або через оператор CROSS JOIN, або через кому між ними.
Приклад 9. База даних - все та ж, таблиці - Categories і Parts. Реалізувати операцію декартова твори цих двох таблиць.
Запит буде наступним:
SELECT (*) Categories CROSS JOIN Parts
Або без явної вказівки CROSS JOIN - через кому:
SELECT (*) Categories, Parts
Запит поверне таблицю з 5 * 5 \u003d 25 рядків, фрагмент якої наведено нижче:
| Cat_ID | Cat_name | Price | Part_ID | Part | Cat |
| 10 | будматеріали | 105,00 | 1 | квартири | 505 |
| 10 | будматеріали | 105,00 | 2 | автомашини | 205 |
| 10 | будматеріали | 105,00 | 3 | дошки | 10 |
| 10 | будматеріали | 105,00 | 4 | Шафи | 30 |
| 10 | будматеріали | 105,00 | 5 | книги | 160 |
| ... | ... | ... | ... | ... | ... |
| 45 | техніка | 65,00 | 1 | квартири | 505 |
| 45 | техніка | 65,00 | 2 | автомашини | 205 |
| 45 | техніка | 65,00 | 3 | дошки | 10 |
| 45 | техніка | 65,00 | 4 | Шафи | 30 |
| 45 | техніка | 65,00 | 5 | книги | 160 |
Як видно з прикладу, якщо результат такого запиту і має будь-яку цінність, то це, можливо, наочна цінність в деяких випадках, коли не потрібно вивести структуровану інформацію, тим більше, навіть найпростішу аналітичну вибірку. До речі, можна вказати виведені стовпці з кожної таблиці, але і тоді інформаційна цінність такого запиту не підвищиться.
Але для CROSS JOIN можна задати умову з'єднання! Результат буде зовсім іншим. При використанні оператора "кома" замість явного вказівки CROSS JOIN умова з'єднання задається не словом ON, а словом WHERE.
Приклад 10. Та ж база даних порталу оголошень, таблиці Categories і Parts. Використовуючи перехресне з'єднання, з'єднати таблиці так, щоб дані повністю перетиналися за умовою. Умова - збіг ідентифікатора категорії в таблиці Categories і посилання на категорію в таблиці Parts.
Запит буде наступним:
Запит поверне те ж саме, що і запит в прикладі 1:
| Part | Cat | Price |
| квартири | 505 | 210,00 |
| автомашини | 205 | 160,00 |
| дошки | 10 | 105,00 |
| Шафи | 30 | 77,00 |
І це збіг не випадковий. Запит c перехресним з'єднанням за умовою сполуки повністю аналогічний запитом з внутрішнім з'єднанням - INNER JOIN - або, враховуючи, що слово INNER - не обов'язкова, просто JOIN.
Таким чином, який варіант запиту використовувати - питання стилю або навіть звички фахівця по роботі з базою даних. Можливо, злиття у формі перехресного з'єднання з умовою для двох таблиць може представлятися більш компактним. Але перевага перехресного з'єднання для більш ніж двох таблиць (це також можливо) дуже суперечливе. В цьому випадку WHERE-умови перетину перераховуються через слово AND. Така конструкція може бути громіздкою і важкою для читання, якщо в кінці запиту є також секція WHERE з умовами вибірки.
Реляційні бази даних і мова SQL
