Left join (SQL) - приклад, докладний опис, помилки використання

В будь-якій реальній реляційній базі даних вся інформація розподіляється за окремими таблицями. Багато з таблиць мають зафіксовані в схемі встановлені зв'язки один з одним. Однак з допомогою запитів Sql цілком реально прокласти зв'язок між даними, що не закладену в схемі. Це здійснюється шляхом виконання операції з'єднання join, яка дозволяє вибудувати відносини між будь-якою кількістю таблиць і з'єднати навіть, здавалося б, розрізнені дані. В даній статті піде мова конкретно про лівому зовнішньому з'єднанні. Перш ніж приступити до опису даного типу з'єднання, додамо в базу даних, деякі таблиці.

Підготовка необхідних таблиць

Припустимо, у нашій базі даних є інформація про людей і їх нерухоме майно. Основна інформація ґрунтується на трьох таблицях: Peoples (люди), Realty (нерухомість), Realty_peoples (таблиця з відносинами, кому з людей яка нерухомість належить). Припустимо, в таблицях зберігаються наступні дані по людям:




Peoples



id



L_name



F_name



Middle_name



Birthday



1



Іванова



Дарина



Борисівна



16072000



2



Пугін



Владислав



Миколайович



29011986



3



Евгеньин



Олександр



Федорович



30041964



4



Аннина



Любов



Павлівна



31121989



5



Герасимовская



Надія



Павлівна



14031992



6



Герасимовський



Олег



Альбертович



29011985



7



Сухановский



Юрій



Андрійович



25091976



8



Сухановская



Юлія



Юріївна



01102001

По нерухомості:

Realty



id



address



1



р. Архангельськ, вул. Вороніна, д. 7 кв. 6



2



р. Архангельськ, вул. Северодвинск, д. 84 кв. 9 кімн. 5



3



Архангельська область, р. Северодвинск, вул. Леніна, д. 134 кв. 85



4



Архангельська область, р. Новодвинск, вул. Пролетарська, д. 16 кв. 137



5



р. Архангельськ, пл. Терьохіна, д. 89 кв. 13

По відносинам люди - нерухомість:

Realty_peoples



id_peoples



id_realty



type



7



3



Загальна спільна власність



8



3



Загальна спільна власність



3



5



Власність



7



1



Власність



5



4



Спільна часткова власність



6



4



Спільна часткова власність

Left join (Sql) – опис

Ліве з'єднання має наступний синтаксис:

Table_A LEFT JOIN table_B[{ON предикат} | {USING список_столбцов}]

І схематично виглядає так: І перекладається цей вираз як «Вибрати всі без винятку рядка з таблиці, А таблиці В вивести тільки співпадаючі по предикату рядка. Якщо в таблиці не знайшлося пари для рядків таблиці А, то заповнити результуючі стовпці Null - значення».


Найчастіше при виконанні лівого з'єднання вказується ON USING використовують, лише коли назви стовпців, за якими планується виконати з'єднання, збігаються.

Left join - приклади використання

З допомогою лівого з'єднання ми можемо подивитися, чи у всіх людей зі списку Peoples є нерухомість. Для цього виконаємо наступний в left join приклад sql запиту:

SELECT Peoples.*, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples;

І отримаємо наступний результат:

Запрос1



id



L_name



F_name



Middle_name



Birthday



id_realty



type



1



Іванова



Дарина



Борисівна



16072000











2



Пугін



Владислав



Миколайович



29011986











3



Евгеньин



Олександр



Федорович



30041964



5



Власність



4



Аннина



Любов



Павлівна



31121989











5



Герасимовская



Надія



Павлівна



14031992



4



Спільна часткова власність



6



Герасимовський



Олег



Альбертович



29011985



4



Спільна часткова власність



7



Сухановский



Юрій



Андрійович



25091976



1



Власність



7



Сухановский



Юрій



Андрійович



25091976



3



Загальна спільна власність



8



Сухановская



Юлія



Юріївна



01102001



3



Загальна спільна власність

Як бачимо, у Іванової Дар'ї, Пугина Владислава і Анниній Любові немає зареєстрованих прав на нерухомість. А що б ми отримали, використовуючи внутрішнє з'єднання Inner join? Як відомо, воно виключає неспівпадаючі рядка, тому три людини з нашої підсумкової вибірки просто випали:

Запрос1



id



L_name



F_name



Middle_name



Birthday



id_realty



type



3



Евгеньин



Олександр



Федорович



30041964



5



Власність



5



Герасимовская



Надія



Павлівна



14031992



4



Спільна часткова власність



6



Герасимовський



Олег



Альбертович



29011985



4



Спільна часткова власність



7



Сухановский



Юрій



Андрійович



25091976



1



Власність



7



Сухановский



Юрій



Андрійович



25091976



3



Загальна спільна власність



8



Сухановская



Юлія



Юріївна



01102001



3



Загальна спільна власність

Здавалося б, другий варіант так само відповідає умовам нашої задачі. Однак, якщо ми далі почнемо приєднувати ще і ще таблиці, три людини з результату вже безповоротно зникнуть. Тому на практиці при об'єднанні безлічі таблиць набагато частіше використовуються сполуки Left і Right, ніж Inner join.
Продовжимо розглядати з left join sql приклади. Приєднаємо таблицю з адресами наших об'єктів нерухомості:

SELECT Peoples.*, Realty_peoples.id_realty, Realty_peoples.type, Realty.address FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples LEFT JOIN Realty ON Realty.id = Realty_peoples.id_realty

Тепер ми отримаємо не тільки вид права, але і адреси об'єктів нерухомості:

Запрос1



id



L_name



F_name



Middle_name



Birthday



id_realty



type



address



1



Іванова



Дарина



Борисівна



16072000















2



Пугін



Владислав



Миколайович



29011986















3



Евгеньин



Олександр



Федорович



30041964



5



Власність



р. Архангельськ, пл. Терьохіна, д. 89 кв. 13



4



Аннина



Любов



Павлівна



31121989















5



Герасимовская



Надія



Павлівна



14031992



4



Спільна часткова власність



Архангельська область, р. Новодвинск, вул. Пролетарська, д. 16 кв. 137



6



Герасимовський



Олег



Альбертович



29011985



4



Спільна часткова власність



Архангельська область, р. Новодвинск, вул. Пролетарська, д. 16 кв. 137



7



Сухановский



Юрій



Андрійович



25091976



3



Загальна спільна власність



Архангельська область, р. Северодвинск, вул. Леніна, д. 134 кв. 85



7



Сухановский



Юрій



Андрійович



25091976



1



Власність



р. Архангельськ, вул. Вороніна, д. 7 кв. 6



8



Сухановская



Юлія



Юріївна



01102001



3



Загальна спільна власність



Архангельська область, р. Северодвинск, вул. Леніна, д. 134 кв. 85

Left join - типові помилки використання: неправильний порядок таблиць

Основних помилок, що допускаються при лівому зовнішньому з'єднанні таблиць, дві: Невірно обраний порядок таблиць, з-за якого були втрачені дані. Помилки при використанні Where в запиті з об'єднаними таблицями. Розглянемо першу помилку. Перед рішенням завдання варто чітко розуміти, що саме ми хочемо отримати в підсумку. У розглянутому вище прикладі ми вивели всіх до єдиного людей, але абсолютно втратили відомості про об'єкт під номером 2 у якого власника не знайшлося.
Якщо б ми переставили таблиці в запиті місцями, і почали б з « From Realty left join Peoples» то ні одну нерухомість ми б не втратили, чого не скажеш про людей. Однак не варто, злякавшись лівого з'єднання, переходити на повне зовнішнє, яке включить в результаті і збігаються, і не збігаються рядки. Адже обсяг вибірок часто дуже великий, і зайві дані реально ні до чого. Головне - розібратися, що ви хочете в результаті отримати: всіх людей зі списком наявних у них нерухомості, або список всієї нерухомості з їх власниками (якщо є).

Left join - типові помилки використання: правильність запиту при завданні умов Відбору

Друга проблема пов'язана з втратою даних, причому не завжди відразу очевидною. Повернемося до запиту, коли ми з допомогою лівого з'єднання отримали дані по всім людям і наявною у них нерухомості. Згадайте наступний із застосуванням left join sql приклад:

FROM Peoples LEFT JOIN Realty_peoples ON Peoples.id = Realty_peoples.id_peoples;

Припустимо, ми хочемо уточнити запит і не виводити дані, де тип права – «Власність». Якщо ми просто допишемо, застосовуючи left join sql, приклад наступним умовою:

Where type <> "Власність"

то втратимо дані по людям, у яких немає ніякої нерухомості, адже порожнє значення Null не порівнюється таким чином:

Запрос1



id



L_name



F_name



Middle_name



Birthday



id_realty



type



5



Герасимовская



Надія



Павлівна



14031992



4



Спільна часткова власність



6



Герасимовський



Олег



Альбертович



29011985



4



Спільна часткова власність



7



Сухановский



Юрій



Андрійович



25091976



3



Загальна спільна власність



8



Сухановская



Юлія



Юріївна



01102001



3



Загальна спільна власність

Щоб попередити появу помилок з цієї причини, краще всього задати умову відбору відразу при з'єднанні. Пропонуємо розглянути наступний з left join sql приклад.

SELECT Peoples.*, Realty_peoples.id_realty, Realty_peoples.type FROM Peoples LEFT JOIN Realty_peoples ON (Peoples.id = Realty_peoples.id_peoples AND type <> "Власність")

Результат буде наступним:

Запрос1



id



L_name



F_name



Middle_name



Birthday



id_realty



type



1



Іванова



Дарина



Борисівна



16072000











2



Пугін



Владислав



Миколайович



29011986











3



Евгеньин



Олександр



Федорович



30041964











4



Аннина



Любов



Павлівна



31121989











5



Герасимовская



Надія



Павлівна



14031992



4



Спільна часткова власність



6



Герасимовський



Олег



Альбертович



29011985



4



Спільна часткова власність



7



Сухановский



Юрій



Андрійович



25091976



3



Загальна спільна власність



8



Сухановская



Юлія



Юріївна



01102001



3



Загальна спільна власність

Таким чином, виконавши простий з left join sql приклад, ми отримали список всіх людей, вивівши додатково, у кого з них нерухомість в частковій/спільної власності. В якості висновку хотілося б ще раз підкреслити, що до вибірки інформації з бази даних потрібно поставитися відповідально. Багато нюансів відкрив перед нами із застосуванням left join sql простий приклад, пояснення яким одне – перед тим як приступити до складання навіть елементарного запиту, необхідно ретельно розібратися, що саме ми хочемо отримати в результаті. Удачі!