C, PHP, VB, .NET

Дневникът на Филип Петров


* Многотаблични заявки SELECT

Публикувано на 01 март 2009 в раздел Бази от Данни.

Вече сте разбрали, че разделянето на една база от данни се прави с цел да се спести обем информация и да имаме колкото се може по-малко дублиране на такава. Това естествено е добре, но си има и цена – много често ни се налага да комбинираме информация едновременно от две или повече таблици. В тези случаи стандартният формат на еднотаблична заявка SELECT не върши работа.

В най-простия си вариант с две таблици, които сравняваме, те ще имат две колони, по които лесно ще бъдат сравнявани. От примера с базата от данни за университет, ако искаме да изкараме имената на преподавателите заедно с името на факултета, в който преподават, то ще се наложи да направим многотаблична заявка. Това е така, защото в таблицата professors няма колона, в която пазим името на факултета, а пазим само неговото id. Ето как се изпълнява въпросната многотаблична заявка:

mysql> use university;
Database changed
mysql> SELECT professors.firstname, professors.lastname, faculties.name
    -> FROM professors, faculties
    -> WHERE professors.faculty_id = faculties.id;
+-----------+------------+----------------------------------+
| firstname | lastname   | name                             |
+-----------+------------+----------------------------------+
| Todor     | Ionkov     | Avtomatika                       |
| Emil      | Nikolov    | Avtomatika                       |
| Plamen    | Tzvetkov   | Avtomatika                       |
| Emil      | Garipov    | Avtomatika                       |
| Valeri    | Mladenov   | Avtomatika                       |
| Marin     | Hristov    | Elektronna Tehnika i Tehnologii  |
| Dimitar   | Todorov    | Elektronna Tehnika i Tehnologii  |
| Stela     | Mileva     | Elektronna Tehnika i Tehnologii  |
| Emil      | Manolov    | Elektronna Tehnika i Tehnologii  |
| Philip    | Koparanov  | Elektronna Tehnika i Tehnologii  |
| Stefcho   | Guninski   | Elektrotehnicheski               |
| Liubomir  | Balgaranov | Elektrotehnicheski               |
| Nadejda   | Peeva      | Elektrotehnicheski               |
| Petar     | Nakov      | Elektrotehnicheski               |
| Snejana   | Evtimova   | Elektrotehnicheski               |
| Boncho    | Bonev      | Energo-mashinostroitelen         |
| Hristina  | Antonova   | Energo-mashinostroitelen         |
| Ivailo    | Banov      | Energo-mashinostroitelen         |
| Emanuil   | Agoncev    | Energo-mashinostroitelen         |
| Hristo    | Petkov     | Energo-mashinostroitelen         |
| Ognian    | Nakov      | Kompiutarni sistemi i upravlenie |
| Daniela   | Gotceva    | Kompiutarni sistemi i upravlenie |
| Valentin  | Kamburov   | Mashino-tehnologichen            |
| Anelia    | Ivanova    | Mashino-tehnologichen            |
| Georgi    | Popov      | Mashino-tehnologichen            |
| Daniela   | Peneva     | Mashino-tehnologichen            |
| Nikolai   | Nikolov    | Mashino-tehnologichen            |
| Bojidar   | Galucov    | Biologicheski                    |
| Mariela   | Ojdakova   | Biologicheski                    |
| Iana      | Topalova   | Biologicheski                    |
| Natasha   | Tzanova    | Biologicheski                    |
| Toni      | Spasov     | Himicheski                       |
| Ivan      | Petkov     | Himicheski                       |
| Ivan      | Soskov     | Matematika i informatika         |
| Ivan      | Gantchev   | Matematika i informatika         |
| Liudmil   | Vasilev    | Fizicheski                       |
| Ivan      | Lalov      | Fizicheski                       |
+-----------+------------+----------------------------------+
37 rows in set (0.03 sec)

Въпреки, че в практиката най-често се използват външен и съответния му първичен ключ за сравнение, не е задължително колоната за връзка да е ключ. Съвсем възможно е да са дори съставни колони (повече от една). Указването на името на таблицата преди полето не е задължително, но е препоръчително. То може да се пропуска само при условие, че колоните в таблиците са с различни имена.

Така разгледаното съединение на таблици се нарича вътрешно (INNER JOIN). Дадения пример е от SQL1 стандартът и е лесно разбираем. В SQL2 стандарта този запис остава валиден, но се налага и алтернативен начин за осъществяване на същата заявка с използването на нова ключова дума JOIN в полето “FROM” на заявката. Горния пример е еквивалентен на:

SELECT professors.firstname, professors.lastname, faculties.name
FROM professors INNER JOIN faculties 
         ON professors.faculty_id = faculties.id;

Съществуват и три типа външно съединение на таблици. За съжаление от нашата примерна база от данни те не могат да се демонстрират, затова ще създадем нова:

mysql> CREATE DATABASE joins;
Query OK, 1 row affected (0.00 sec)

mysql> USE joins;
Database changed

mysql> CREATE TABLE books(
    ->  `name` VARCHAR(255) NULL DEFAULT NULL,
    ->  `author_id` INT NULL DEFAULT NULL);
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE authors(
    ->  `name` VARCHAR(255) NULL DEFAULT NULL,
    ->  `id` INT NULL DEFAULT NULL);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO books(name, author_id)
    -> VALUES   ('Da obichash nepoznat', 1),
    ->          ('Plut i kruv', 2),
    ->          ('Tainstvoto na Iuni', NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO authors(name, id)
    -> VALUES   ('Barbara Friiti', 1),
    ->          ('Michael Kuningham', 2),
    ->          ('Tuwe Janson', 3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

Нека видим какво се получава при вътрешно съединение на двете таблици. В примера ще покажем име на книга съединено с името на нейния автор:

mysql> SELECT books.name, authors.name
    -> FROM books INNER JOIN authors ON books.author_id = authors.id;
+----------------------+-------------------+
| name                 | name              |
+----------------------+-------------------+
| Da obichash nepoznat | Barbara Friiti    |
| Plut i kruv          | Michael Kuningham |
+----------------------+-------------------+
2 rows in set (0.00 sec)

Ключовата дума „INNER“ всъщност не е необходима. Ако я пропуснем MySQL ще разбере, че съединението е вътрешно. По същия начин стои положението и с външното съединение показано по-долу, при демонстрацията на което ще пропуснем ключовата дума „OUTER“ в заявките.

Виждаме, че въведохме три книги в таблицата „books“, но в списъка излязоха само две. Това, което се получи е напълно нормално, защото на третата книга (Tainstvoto na Iuni) не е въведен author_id. По този начин тя е пропусната, защото не дава истина при сравнението в клаузата ON на съединението на таблиците.

За да изведем списък на всички книги и имената техните автори, а ако автор не е въведен да бъде изведен просто като NULL, използваме т.нар. LEFT OUTER JOIN (накратко LEFT JOIN) или „ляво външно съединение“:

mysql> SELECT books.name, authors.name
    -> FROM books LEFT JOIN authors ON books.author_id = authors.id;
+----------------------+-------------------+
| name                 | name              |
+----------------------+-------------------+
| Da obichash nepoznat | Barbara Friiti    |
| Plut i kruv          | Michael Kuningham |
| Tainstvoto na Iuni   | NULL              |
+----------------------+-------------------+
3 rows in set (0.00 sec)

Виждате, че резултатът е получен точно както очаквахме. Досещате се, че съществува и дясно съединение на таблици. То действа по абсолютно същия начин, но в случая „натежава“ другата таблица:

mysql> SELECT books.name, authors.name
    -> FROM books RIGHT JOIN authors ON books.author_id = authors.id;
+----------------------+-------------------+
| name                 | name              |
+----------------------+-------------------+
| Da obichash nepoznat | Barbara Friiti    |
| Plut i kruv          | Michael Kuningham |
| NULL                 | Tuwe Janson       |
+----------------------+-------------------+
3 rows in set (0.00 sec)

В ANSI стандарта съществува и FULL JOIN, който обаче не се поддържа от MySQL (както и от почти всички системи за управление на бази от данни). Възможно е да бъде направен чрез няколко заявки, като се използва временна таблица за запазване на резултатите. Истината е, че FULL JOIN почти никога не се използва и затова няма да разглеждаме такъв пример.

Ако условие ON в съединението липсва (винаги е true) или то не отразява никаква връзка между съединяваните таблици, то получаваме т.нар. “произведение” на таблиците. Това означава, че всеки елемент от първата таблица ще бъде долепен със всеки от втората. Вижте подробно резултата, като от горния пример дадете винаги истинно условие след ON – например 1=1…

 



4 коментара


  1. Да, правилно би било да се укажат като PRIMARY KEY.

  2. Николай каза:

    Тук в база данни joins не трябва ли да се укажат PRIMARY KEY и FOREIGN KEY съответно за authors.id и books.author_id?

  3. Тази статия всъщност е част от учебник вече – Бази Данни с автори Гоцева, Ганчева и аз.

  4. Христо каза:

    Основен проблем на голяма част от университетите е , че учебниците издадени от техните преподаватели са написани на неразбираем и недостъпен за всички език. Може би да за да поддържат репутация на академично ниво знам ли . Те не се пишат за да може да се учи от тях а за да се продават. Казвам го във връзка с това , че започвайки да чета вашите лекции тук представа си нямах от база данни. Доста неща ми се изясниха ( разбира се нужно е и практика ) и съм Ви благодарен за отделеното време и усилия за всичко това.Ако всички учебници бяха написани разбираемо като този материал тук съм сигурен ,че много голяма част от студентите биха били значително по заинтересовани от изучаваните от тях предмети.

Добави коментар

Адресът на електронната поща няма да се публикува


*