Это уже реальный продвинутый уровень, и я помню сам не с первого раза понял, что именно и как делает объединение таблиц. Чтобы все это понять пришлось решить несколько различных задач и каждый раз смотреть на результат.
Я постараюсь рассказать про объединение таблицы максимально просто и очень надеюсь, что у меня получится.
У нас есть две таблицы Phone и City. В первой из них находятся телефоны, а во второй. . . внимание. . . подождите, подождите. . . Города!
Вспомним, как выглядит таблица телефонов:
SELECT * FROM phone;
Результат
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | | 5 | Alice | Cooper | 414254234 | 2 | | 6 | Michael | Jackson | 4142544 | 3 | | 7 | John | Abama | 414254422 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | | 9 | Mark | Oh | 414254422 | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | +---------+-----------+-----------+-----------+--------+
В колонке cityid находиться только ID города, но не сам город. Как мы можем вместо ID вывести имя города? Тут нам помогает объединение join таблиц.
Объединение таблиц происходит в секции FROM и имеет формат:
FROM таблица1 JOIN таблица2 ON колонки для связи.
Снова лингвистически все выглядит достаточно плавно, потому что это можно прочитать как:
ИЗ таблица1 СВЯЗАНА С таблица2 ПО колонкам
Тут нужно быть внимательным, потому что JOIN не создает новой секции в SQL запросе, он является частью FROM. В SQL можно выделить три основные секции:
SELECT . . . FROM . . . WHERE . . .
Связи JOIN – это что-то, что мы пишем внутри секции FROM. Именно поэтому я делаю небольшое отступление, когда пишу связи JOIN, чтобы подчеркнуть, что мы пишем этот код в секции FROM.
Итак, давайте наведем связь между телефонами и городами.
FROM phone JOIN city ON phone.cityid = city.cityid
Этим кодом мы просим показать нам данные из таблицы телефонов phone и привязать таблицу городов city. Связь происходит по колонке cityid в телефонах и cityid в городах. Так уже получилось, что в обеих таблицах колонка для ID города названа одинаково и так программисты поступают очень часто. Я не зря назвал колонку для хранения городов cityid, это сделано намеренно, потому что в таблице городов есть колонка с таким же именем и по одинаковому имени мы можем догадаться, что значения в них равны.
Итак, полный запрос будет выглядеть так:
SELECT * FROM phone JOIN city ON phone.cityid = city.cityid
Как сервер будет его выполнять?
Он берет первую строку из таблицы phone:
| phoneid | firstname | lastname | phone | cityid | | 1 | John | Doe | 4144122 | 1 |
Смотрит, что там cityid равен 1, ищет в связанной таблице city таблице запись, где cityid тоже равен 1:
| cityid | cityname | | 1 | Toronto |
И соединяет оба результата вместе (делает операцию join):
| phoneid | firstname | lastname | phone | cityid | cityid | cityname | | 1 | John | Doe | 4144122 | 1 | 1 | Toronto |
Потом переходит к следующей строке в таблице phone:
| phoneid | firstname | lastname | phone | cityid | | 2 | Steve | Doe | 414124 | 1 |
Здесь снова cityid равен 1 и значит снова нужно добавить слить эту строку с Торонто:
| phoneid | firstname | lastname | phone | cityid | cityid | cityname | | 2 | Steve | Doe | 414124 | 1 | 1 | Toronto |
Следующая строка:
| phoneid | firstname | lastname | phone | cityid | | 3 | Johnatan | Something | 4142947 | 2 |
Здесь уже cityid равен 2. Ищем в таблице городов, какой город имеет id равный 2:
| cityid | cityname | | 2 | Vancouver |
Отлично, это Ванкувер, значит база данных должна объединить телефон Johnatan Something с городом Vancouver:
| 3 | Johnatan | Something | 4142947 | 2 | 2 | Vancouver |
И так процесс связи продолжается, пока база данных не вернет нам все записи, которые мы запросили:
+---------+-----------+-----------+-----------+--------+--------+-----------+ | phoneid | firstname | lastname | phone | cityid | cityid | cityname | +---------+-----------+-----------+-----------+--------+--------+-----------+ | 1 | John | Doe | 4144122 | 1 | 1 | Toronto | | 2 | Steve | Doe | 414124 | 1 | 1 | Toronto | | 3 | Johnatan | Something | 4142947 | 2 | 2 | Vancouver | | 4 | Donald | Trump | 414251123 | 2 | 2 | Vancouver | | 5 | Alice | Cooper | 414254234 | 2 | 2 | Vancouver | | 6 | Michael | Jackson | 4142544 | 3 | 3 | Montreal | | 7 | John | Abama | 414254422 | 3 | 3 | Montreal | | 8 | Andre | Jackson | 414254422 | 3 | 3 | Montreal | +---------+-----------+-----------+-----------+--------+--------+-----------+
А что, если мы при наведении связи напишем что-то типа:
SELECT * FROM phone JOIN city ON phone.phoneid = city.cityid
Здесь я говорю, что связь между таблицами происходит по колонке phoneid в таблице телефонов с колонкой cityid в таблице городов:
Он берет первую строку из таблицы phone:
| phoneid | firstname | lastname | phone | cityid | | 1 | John | Doe | 4144122 | 1 |
Смотрит, что там phoneid равен 1, ищет в связанной таблице city таблице запись, где cityid тоже равен 1:
| cityid | cityname | | 1 | Toronto |
Случайным образом мы попали и получили правильную строку:
| 1 | John | Doe | 4144122 | 1 | 1 | Toronto |
Потом берем следующую строку:
| phoneid | firstname | lastname | phone | cityid | | 2 | Steve | Doe | 414124 | 1 |
Так как мы сказали, что связь должна быть по phoneid мы берем 2 из первой колонки и ищем в таблице городов город под номером 2. Но это неверно? Мы найдем не тот город!
В общем результат выполнения такого запроса будет таким и он неверный:
+---------+-----------+-----------+---------+--------+--------+-----------+ | phoneid | firstname | lastname | phone | cityid | cityid | cityname | +---------+-----------+-----------+---------+--------+--------+-----------+ | 1 | John | Doe | 4144122 | 1 | 1 | Toronto | | 2 | Steve | Doe | 414124 | 1 | 2 | Vancouver | | 3 | Johnatan | Something | 4142947 | 2 | 3 | Montreal | +---------+-----------+-----------+---------+--------+--------+-----------+
Поэтому очень важно указать правильную связь – по каким колонкам мы хотим выводить данные, тогда сервер вернет правильный результат.
Получается, что правильный способ связывания двух таблиц:
SELECT * FROM phone JOIN city ON phone.cityid = city.cityid
В данном запросе мы выбираем все колонки, поэтому cityid попадает в результат дважды, один раз из таблицы phone, второй раз из таблицы city:
+---------+-----------+-----------+-----------+--------+--------+-----------+ | phoneid | firstname | lastname | phone | cityid | cityid | cityname | +---------+-----------+-----------+-----------+--------+--------+-----------+
Чаще всего мы не хотим этого делать, поэтому нужно перечислить колонки, которые мы хотим получить:
SELECT firstname, lastname, phone, cityname
FROM phone
JOIN city ON phone.cityid = city.cityid;
Круто, теперь результат на много красивее:
+-----------+-----------+-----------+-----------+ | firstname | lastname | phone | cityname | +-----------+-----------+-----------+-----------+ | John | Doe | 4144122 | Toronto | | Steve | Doe | 414124 | Toronto | | Johnatan | Something | 4142947 | Vancouver | | Donald | Trump | 414251123 | Vancouver | | Alice | Cooper | 414254234 | Vancouver | | Michael | Jackson | 4142544 | Montreal | | John | Abama | 414254422 | Montreal | | Andre | Jackson | 414254422 | Montreal | +-----------+-----------+-----------+-----------+
Когда мы наводим связи, то мы должны указать по каким колонкам нужно соединять данные и в нашем случае это cityid:
FROM phone
JOIN city ON phone.cityid = city.cityid;
Так как cityid колонка есть в обеих таблицах, мы вынуждены перед колонкой указать, какую колонку мы конкретно имеет в виду. То же самое и при выводе данных. Если попытаться вывести cityid, указав его в SELECT, сервер попросит нас уточнить, а какую мы имеем в виду – та, что в phone или та, что в city:
SELECT firstname, lastname, phone, cityname, cityid
FROM phone
JOIN city ON phone.cityid = city.cityid;
В результате будет ошибка:
ERROR 1052 (23000): Column 'cityid' in field list is ambiguous
Здесь нам говорят, что Колонка cityid в списке полей двусмысленная. Она двусмысленна, потому что находиться в двух разных колонках. Это мы знаем, что они равны и нам все равно, какую из них выбрать, а сервер не особо думает об этом. Поэтому правильно было бы указать так:
SELECT firstname, lastname, phone, cityname, phone.cityid
FROM phone
JOIN city ON phone.cityid = city.cityid;
Вот теперь мы точно сказали, что мы хотим именно из таблицы phone. Каждый раз писать имя таблицы не очень приятно. И вот тут мы вспоминаем великолепную возможность – псевдонимы. Мы можем дать псевдонимы обеим таблицам и использовать их. Давайте телефону дадим псевдоним p, а городу c.
SELECT firstname, lastname, phone, cityname, p.cityid
FROM phone p
JOIN city c ON p.cityid = c.cityid;
Теперь вместо того, чтобы писать полное имя таблицы мы можем писать их псевдонимы. Вот оно преимущество псевдонимов.
Я не помню в какой базе данных, но где-то я видел прикол, когда я давал хотя бы один псевдоним, то я обязан был указывать его перед каждой колонкой. Не помню уже какая это была база данных, но точно не MySQL и не MS SQL Server.
SELECT *
FROM phone p
JOIN city c ON p.cityid = c.cityid;
Если вы столкнулись с тем, что колонка не найдена, то возможно вам просто нужно добавить везде псевдонимы.
Давайте добавим еще один город в таблицу городов.
insert into city (cityname) values ('New York');
Давайте снова посмотрим на результат, который мы получаем при объединении двух таблиц:
SELECT *
FROM phone p
JOIN city c ON p.cityid = c.cityid;
Все круто, но есть одно НО – мы не видим записей с телефонных номеров, у которых cityid равен null.
SELECT * FROM phone p where cityid is null;
У меня таких записей две:
+---------+-----------+----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+-----------+--------+ | 9 | Mark | Oh | 414254422 | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | +---------+-----------+----------+-----------+--------+
Но мы их не видим потому, что сервер не смог найти города для этих записей.
По умолчанию, если мы связываем две таблицы с помощью join, то происходит inner join, то есть мы могли бы написать команду так:
SELECT *
FROM phone p
INNER JOIN city c ON p.cityid = c.cityid;
Результат не изменился, мы все также видим те же данные. INNER JOIN равен просто JOIN и приводит к тому, что в результате мы видим только те записи, для которых найдены данные в обеих связанных таблицах. Если хотя бы в одной из этих таблиц данные отсутствуют или колонка равна нулю, то мы эти данные не увидим.
Так произошло со строками телефонных записей, у которых в поле cityid находится null и то же самое произошло и с городом New York, который мы недавно добавили, но при этом у нас нет ни одного телефонного номера, который бы был связан с этим городом.
Помимо INNER JOIN есть еще два популярных метода связывать таблицы LEFT JOIN и RIGHT JOIN. В чем смысл и что означают эти магические LEFT (лево) и RIGHT (право)?
Давайте напишем LEFT JOIN:
SELECT * FROM phone p LEFT JOIN city c ON p.cityid = c.cityid;
В этот раз я записал связь двух таблиц в одну строку и сделал это специально, потому что так проще объяснять. Левая связь работает так, что мы увидим все записи, которые находятся в левой таблице и в тех случаях, где база данных найдет соответствующие данные из второй таблице, они будут добавлены.
Смотрим на результат:
+---------+-----------+-----------+-----------+--------+--------+-----------+ | phoneid | firstname | lastname | phone | cityid | cityid | cityname | +---------+-----------+-----------+-----------+--------+--------+-----------+ | 1 | John | Doe | 4144122 | 1 | 1 | Toronto | | 2 | Steve | Doe | 414124 | 1 | 1 | Toronto | | 3 | Johnatan | Something | 4142947 | 2 | 2 | Vancouver | | 4 | Donald | Trump | 414251123 | 2 | 2 | Vancouver | | 5 | Alice | Cooper | 414254234 | 2 | 2 | Vancouver | | 6 | Michael | Jackson | 4142544 | 3 | 3 | Montreal | | 7 | John | Abama | 414254422 | 3 | 3 | Montreal | | 8 | Andre | Jackson | 414254422 | 3 | 3 | Montreal | | 9 | Mark | Oh | 414254422 | NULL | NULL | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | NULL | NULL | +---------+-----------+-----------+-----------+--------+--------+-----------+
Теперь мы видим все телефоны и даже те, у которых нет города. Там, где город отсутствует значения в колонках города тоже равны NULL, потому что ничего не найдено и поэтому нам говорят, что здесь значения отсутствуют.
LEFT JOIN – показать все записи из левой таблицы и там где возможно, показать значения из правой.
Но мы не видим город New York, потому что он находится для нас в правой таблице, но для него в левой нет ничего. Чтобы исправить эту ситуацию можно заменить LEFT JOIN на RIGHT JOIN:
SELECT * FROM phone p RIGHT JOIN city c ON p.cityid = c.cityid;
В результате мы увидим все записи из правой таблицы от команды JOIN (у нас это city), включая New York. Если для соответствующей записи есть данные в левой таблице phone, то они будут отображены:
+---------+-----------+-----------+-----------+--------+--------+-----------+ | phoneid | firstname | lastname | phone | cityid | cityid | cityname | +---------+-----------+-----------+-----------+--------+--------+-----------+ | 1 | John | Doe | 4144122 | 1 | 1 | Toronto | | 2 | Steve | Doe | 414124 | 1 | 1 | Toronto | | 3 | Johnatan | Something | 4142947 | 2 | 2 | Vancouver | | 4 | Donald | Trump | 414251123 | 2 | 2 | Vancouver | | 5 | Alice | Cooper | 414254234 | 2 | 2 | Vancouver | | 6 | Michael | Jackson | 4142544 | 3 | 3 | Montreal | | 7 | John | Abama | 414254422 | 3 | 3 | Montreal | | 8 | Andre | Jackson | 414254422 | 3 | 3 | Montreal | | NULL | NULL | NULL | NULL | NULL | 4 | New York | +---------+-----------+-----------+-----------+--------+--------+-----------+
Теперь мы видим New York и раз ни одного телефона в Нью Йорке в нашей базе нет, то все поля из таблицы phone показывают отсутствующее значение – NULL.
Нужно ли запоминать Right Join? Из личного опыта LEFT JOIN не все сразу схватывают, а если и схватывают, почему-то испытывают проблемы с RIGHT JOIN.
Я не вижу ничего сложного, но в реальной жизни я вижу большинство использует все же LEFT JOIN. Кажется, в КВН была шутка, что в казахском языке нет слова назад. Просто, когда нужно отступать, они разворачиваются на 180 градусов и бегут вперед. Шутка шуткой, но она четко подходит здесь.
Посмотрим на наш запрос RIGHT JOIN:
SELECT * FROM phone p RIGHT JOIN city c ON p.cityid = c.cityid;
А что, если мы поменяем местами таблицы phone и city? Нужная нам таблица окажется слева, а значит мы сможем поменять RIGHT на LEFT и получить нужный нам результат:
SELECT * FROM city c LEFT JOIN phone p ON p.cityid = c.cityid;
Давайте добавим телефон с ID города 10. Такого города не существует и именно от подобных вещей нас умеет защищать сама база данных, но я специально пока эту защиту не сделал, чтобы было пространство для маневров:
insert into phone (firstname, cityid) values ('Mark', 10);
Теперь как нам найти все записи телефонов, города которых реально не существуют в таблице городов. Нам нужно пробежаться по всем записям в телефонах и для каждой из них проверить – существует его cityid в городах или нет. Вот именно так мы это и можем написать это на языке SQL.
SELECT * FROM phone p WHERE NOT EXISTS (SELECT * FROM city c WHERE c.cityid = p.cityid);
Здесь в одном запросе два SELECT и их можно рассматривать по-отдельности. Первый - выбрать все записи из таблицы phone где не существует (и тут у нас запрос, который проверяет на существование записи в таблице city). Второй запрос проверяет существование, и он возвращает все записи из таблицы city, для которых cityid равен текущего cityid в таблице телефонов.
В результате мы получим две записи телефонов с нулевым cityid и один с 10:
+---------+-----------+----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+----------+-----------+--------+ | 9 | Mark | Oh | 414254422 | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | | 11 | Mark | NULL | NULL | 10 | +---------+-----------+----------+-----------+--------+
Все верно, таких городов не существует в таблице city.
EXISTS может проверять и на существование. Если мы хотим проверить на то, какие города ЕСТЬ, мы можем использовать EXISTS:
SELECT * FROM phone p WHERE EXISTS (SELECT * FROM city c WHERE c.cityid = p.cityid);
+---------+-----------+-----------+-----------+--------+ | phoneid | firstname | lastname | phone | cityid | +---------+-----------+-----------+-----------+--------+ | 1 | John | Doe | 4144122 | 1 | | 2 | Steve | Doe | 414124 | 1 | | 3 | Johnatan | Something | 4142947 | 2 | | 4 | Donald | Trump | 414251123 | 2 | | 5 | Alice | Cooper | 414254234 | 2 | | 6 | Michael | Jackson | 4142544 | 3 | | 7 | John | Abama | 414254422 | 3 | | 8 | Andre | Jackson | 414254422 | 3 | +---------+-----------+-----------+-----------+--------+
Запрос с EXISTS идентичен INNER JOIN и следующие два запроса выведут абсолютно идентичный результат:
SELECT * FROM phone p WHERE EXISTS (SELECT * FROM city c WHERE c.cityid = p.cityid);
И
SELECT p.*
FROM phone p
JOIN city c ON c.cityid = p.cityid;
Можете выполнить их, чтобы убедиться в идентичности.
NOT EXISTS можно реализовать с помощью LEFT JOIN. Давайте выполним LEFT JOIN и посмотрим на результат:
SELECT *
FROM phone p
LEFT JOIN city c ON c.cityid = p.cityid;
Результат будет таким:
+---------+-----------+-----------+-----------+--------+--------+-----------+ | phoneid | firstname | lastname | phone | cityid | cityid | cityname | +---------+-----------+-----------+-----------+--------+--------+-----------+ | 1 | John | Doe | 4144122 | 1 | 1 | Toronto | | 2 | Steve | Doe | 414124 | 1 | 1 | Toronto | | 3 | Johnatan | Something | 4142947 | 2 | 2 | Vancouver | | 4 | Donald | Trump | 414251123 | 2 | 2 | Vancouver | | 5 | Alice | Cooper | 414254234 | 2 | 2 | Vancouver | | 6 | Michael | Jackson | 4142544 | 3 | 3 | Montreal | | 7 | John | Abama | 414254422 | 3 | 3 | Montreal | | 8 | Andre | Jackson | 414254422 | 3 | 3 | Montreal | | 9 | Mark | Oh | 414254422 | NULL | NULL | NULL | | 10 | Charly | Lownoise | 414254422 | NULL | NULL | NULL | | 11 | Mark | NULL | NULL | 10 | NULL | NULL | +---------+-----------+-----------+-----------+--------+--------+-----------+
Первая колонка с cityid из таблицы телефонов. Вторая колонка – из таблицы городов. Обратите внимание, что для нужных нам записей вторая cityid и cityname равны NULL, там отсутствуют значения. Это же логично и именно эти записи нам нужны. Так что задачу NOT EXIST можно решить с помощью LEFT JOIN, если попросить вернуть записи, в которых cityid в колонке городов равен NULL. Главное не забывать, что проверка на NULL производиться с помощью IS:
SELECT p.*
FROM phone p
LEFT JOIN city c ON c.cityid = p.cityid
WHERE c.cityid IS NULL;
Так что этот запрос идентичен NOT NULL.
Что если мы хотим объединить две таблицы в одну? Без проблем, для этого существует оператор UNION, который объединяет два запроса. Вы просто пишете два SELECT запроса, которые возвращают какие-то данные и между ними пишите UNION или UNION ALL. Разница в том, что в первом случае база данных берет два результата запросов и убирает повторения из них, возвращая только уникальные строки из обеих строк. Во втором случае вернуться все записи, без попытки избавиться от дубликатов.
Например, запрос, который возвращает имена и фамилии и запрос, который возвращает названия городов?
select concat(firstname, ' ', lastname) from phone
и
select cityname from city;
превращается в:
select concat(firstname, ' ', lastname) from phone union select cityname from city;
В результате мы увидим в качестве результата абсолютно все имена+фамилии и города.
+----------------------------------+ | concat(firstname, ' ', lastname) | +----------------------------------+ | John Doe | | Steve Doe | | Johnatan Something | | Donald Trump | | Alice Cooper | | Michael Jackson | | John Abama | | Andre Jackson | | Mark Oh | | Charly Lownoise | | NULL | | Toronto | | Vancouver | | Montreal | +----------------------------------+
При объединении запросов с помощью UNION нужно соблюдать достаточно простое правило – количество колонок должно совпадать. У меня в обеих таблицах один только одна колонка, поэтому проблем нет.
Если имена колонок в обеих таблицах разные, то в качестве финального имени будет выбрано имя колонки из первого запроса.
Обратите внимание, что у нас в заголовке имя – это операция объединения. Я это сделал специально. Чтобы у заголовка появилась имя, в данном случае нужно дать псевдоним колонке первого запроса.
select concat(firstname, ' ', lastname) as name from phone union select cityname from city;
Мы можем получать данные не только из таблиц в базе данных, но и из других SELECT запросов. Мы только что разобрались с union запросом и возникает вопрос – а как отсортировать все данные? В нашем случае сначала были выбраны колонки из первой таблицы, а потом из второй. Чтобы отсортировать этот результат мы можем написать запрос, который будет обращаться к другому запросу.
Для этого мы берем запрос, к которому мы будем впоследствии обращаться, помещаем его в круглые скобки и даем ему псевдоним:
( select concat(firstname, ' ', lastname) as name from phone union select cityname from city ) g
Вот эту всю конструкцию мы можем воспринимать как что-то, к чему можно обращаться. Главное, чтобы все поля этого нашего запроса имели имена, иначе как к ним обращаться?
Итак, пишем SELECT к запросу:
SELECT * FROM ( select concat(firstname, ' ', lastname) as name from phone union select cityname from city ) g Order by name
Таким образом мы можем реализовать что-то типа глобального поиска, который ищет по именам людей и городов одновременно.
Мы можем добавлять и WHERE к такому запросу и фильтровать данные:
SELECT * FROM ( select concat(firstname, ' ', lastname) as name from phone union select cityname from city ) g WHERE Name like 'A%' Order by name
Программист, автор нескольких книг серии глазами хакера и просто блогер. Интересуюсь безопасностью, хотя хакером себя не считаю
Copyright © 2024 Михаил Флёнов. Тираж 1 штука. Отпечатано на ноутбуке. А за права отвечаю, или постараюсь.
А здесь еще и копирайт поставлю: Professional Web Development.
На сайте используются Cookie. Если вы из ЕС или Калифорнии США, то вы должны согласиться с их использованием - Согласен. Если вы из нормальной страны, то вам пох.