Как соединить таблицы в sql ключами

Внешний ключ (FOREIGN KEY) в SQL

Внешний ключ (FOREIGN KEY) нужен для того, чтобы связать две разные таблицы между собой. Внешний ключ может ссылаться на любой столбец в родительской таблице. Однако общепринятой практикой является ссылка внешнего ключа на первичный ключ (primary key) родительской таблицы. Например:

Здесь поле customer_id в таблице Orders является FOREIGN KEY , который ссылается на поле id в таблице Customers. Это означает, что значением customer_id (таблицы Orders) должно быть значение из столбца id (таблицы Customers).

Создание внешнего ключа

Теперь давайте посмотрим, как мы можем добавить ограничение FOREIGN KEY :

— Эта таблица не имеет внешнего ключа
CREATE TABLE Customers (
first_name VARCHAR ( 40 ) ,
last_name VARCHAR ( 40 ) ,
country VARCHAR ( 10 ) ,
CONSTRAINT CustomersPK PRIMARY KEY ( id )
— Добавляем внешний ключ к полю customer_id.
— Внешний ключ ссылается на поле id таблицы Customers
CREATE TABLE Orders (
order_id INT ,
item VARCHAR ( 40 ) ,
amount INT ,
customer_id INT REFERENCES Customers ( id ) ,
CONSTRAINT OrdersPK PRIMARY KEY ( order_id )

Здесь столбец customer_id таблицы Orders ссылается на столбец id таблицы Customers.

Примечание: Вышеприведенный код создания внешнего ключа может отличаться в некоторых СУБД.

Вставка данных в таблицу с внешним ключом

Попробуем вставить данные в таблицу с внешним ключом.

— Сначала вставляем данные в таблицу без внешнего ключа
INSERT INTO Customers
( 1 , ‘John’ , ‘Doe’ , 31 , ‘USA’ ) ,
( 2 , ‘Robert’ , ‘Luna’ , 22 , ‘USA’ ) ;
— Первая операция вставки данных проходит успешно
INSERT INTO Orders
( 1 , ‘Keyboard’ , 400 , 2 ) ,
( 2 , ‘Mouse’ , 300 , 2 ) ,
( 3 , ‘Monitor’ , 12000 , 1 ) ;

— Вторая операция вставки данных приводит к ошибке, поскольку customer_id со значением 7 не существует

INSERT INTO Orders
VALUES ( 4 , ‘Keyboard’ , 400 , 7 ) ;

Зачем использовать внешний ключ?

Две главные причины:

Нормализация данных. FOREIGN KEY помогает нормализовать данные в нескольких таблицах и уменьшить избыточность. Это означает, что в базе данных может быть несколько таблиц, связанных друг с другом.

Предотвращение вставки некорректных данных. Если две таблицы в базе данных связаны через поле (атрибут), использование FOREIGN KEY гарантирует, что в это поле не будут вставлены неверные данные. Это помогает устранить ошибки на уровне базы данных.

FOREIGN KEY с оператором ALTER TABLE

Можно добавить ограничение FOREIGN KEY к существующей таблице с помощью оператора ALTER TABLE. Например:

Базовый курс SQL. JOIN-ы, или объединения таблиц

Один из самых мощных инструментов извлечения данных в SQL — это объединение таблиц. SQL может «на лету» объединять таблицы, и это даёт широчайший спектр возможностей для работы с данными.

Чтобы разобраться в этой теме, однозначно не достаточно базового курса по SQL, но мы постараемся дать основы работы с объединениями, которые впоследствии вы сможете эффективно углублять.

Реляционные таблицы

Разобраться с понятием реляционных таблиц нам поможет пример.

В нашей базе данных есть таблица Students, содержащая информацию о студентах: ФИО, возраст, эл.почта, а также сведения о группе, в которой учится студент. Но очевидно, что в одной группе учатся несколько студентов, и значит, данные о группе для них будут повторяться.

Крайне нежелательно допускать такое дублирование потому что:

• это избыточные данные, а значит, и место на диске, и затраченное время разработчика
• существенное повышение опасности ошибиться при вводе данных
• при изменении данных придётся править каждую строку, что тоже повышает трудозатраты и количество опечаток

Наилучшим решением будет создать ещё одну таблицу, Groups, в строках которой будут храниться данные о группе. И в таблицу Students добавить поле group_id — внешний ключ. Значение внешнего ключа Students будет соответствовать значению первичного ключа Groups. Так СУБД поймёт, какая именно строка таблицы Groups соответствует данному студенту.

Таблицы Students и Groups и являются реляционными, т.е. имеющими relation (с англ. — связь).

И, соответственно, преимущества реляционных таблиц:

• отсутствие избыточных данных
• лёгкость в случае необходимости внесения изменений — только в одном месте
• сокращение потенциальных опечаток и ошибок по невнимательности

Также хорошо спроектированная база данных легче поддаётся масштабированию, что немаловажно в работе над реальными проектами.

Для чего используются объединения

Итак, мы убедились, что использование реляционных таблиц в базе данных имеет много преимуществ. Но как же тогда извлечь данные с помощью SELECT, если они хранятся в другой таблице?

Для этого и служат объединения таблиц, или JOIN-ы.

С помощью определённого синтаксиса можно «на лету» соединить две или более таблиц базы в одну и так же, применяя условия, достать все необходимые данные. Нужно понимать, что объединение — это не реально существующая таблица, а сформированная в оперативной памяти на время запроса.

Ссылочная целостность

Важным моментом при создании связей является соблюдение ссылочной целостности между таблицами. Это означает, что крайне важно, чтобы внешний ключ был указан корректно. Иначе мы свяжем неверные строки и получим данные, не соответствующие действительности, или вовсе ошибку, если в другой таблице такого ключа не существует. Зачастую, контроль этого обеспечивается интерфейсами СУБД на этапе ввода данных, но логических ошибок это избежать не поможет, так что будьте внимательными.

Создание объединений

Для того, чтобы извлечь объединённые данные двух таблиц, нужно указать обе таблицы после оператора FROM, а также условие, по которому будут соотноситься строки, после оператора WHERE:

SELECT student_surname, group_name FROM Students, Groups WHERE Students.group_id = Groups.group_id;

Получим список студентов и названия групп, в которых они обучаются:

student_name | group_name ------------------------------- Шульгина | 304F Колобков | 304F Распопов | 304F Адамченко | 304F Сейдинай | 304F Петрашевский | 304F Римский | 580Z Грошев | 117F Легран | 117F Егорова | 117F

Теперь мы имеем возможность извлечь столбцы из двух таблиц одновременно: student_surname и group_name находятся в разных таблицах. Оператор FROM указывает БД на то, какие таблицы объединять: Students и Groups. WHERE подсказывает, какая строка из Groups соответствует данному студенту. Для каждой уникальной пары будет выведена строка в результирующей таблице.

Обратите внимание на то, что мы использовали полное название столбцов group_id для обоих таблиц: Students.group_id и Groups.group_id. Это необходимо, если имена столбцов совпадают. Всегда указывайте полные имена столбцов, если возможна неоднозначность, иначе СУБД выдаст ошибку.

Важность условия WHERE

При объединении таблиц отношения между ними формируются «на лету», и делается это следующим образом: к каждой строке первой таблицы добавляются все возможные варианты строк из второй таблицы, не зависимо ни от какой логики. Получается одна большая таблица, количество столбцов в которой равно сумме столбцов двух таблиц, а количество строк — произведению их строк. Такой результат называется декартово произведение, или перекрёстное объединение.

На нашем примере это бы выглядело так:

SELECT student_surname, group_name FROM Students, Groups;

Как мы видим, данные некорректны, как будто каждый студент обучается сразу в 3х группах:

student_name | group_name ------------------------------- Шульгина | 304F Колобков | 304F Распопов | 304F Адамченко | 304F Сейдинай | 304F Петрашевский | 304F Римский | 304F Грошев | 304F Легран | 304F Егорова | 304F Шульгина | 580Z Колобков | 580Z Распопов | 580Z Адамченко | 580Z Сейдинай | 580Z Петрашевский | 580Z Римский | 580Z Грошев | 580Z Легран | 580Z Егорова | 580Z Шульгина | 117F Колобков | 117F Распопов | 117F Адамченко | 117F Сейдинай | 117F Петрашевский | 117F Римский | 117F Грошев | 117F Легран | 117F Егорова | 117F

Но в действительности нам редко требуются такие избыточные комбинации, поэтому, если вы хотите получить связанные данные, не забывайте указывать конкретное условие для объединения таблиц.

Внутреннее объединение. INNER JOIN

Объединение, которое даёт нам корректный результат студентов и их групп, называется внутренним объединением. И его также можно реализовать с помощью альтернативного синтаксиса:

SELECT student_surname, group_name FROM Students INNER JOIN Groups ON Students.group_id = Groups.group_id;

Здесь мы явно указали СУБД, что объединение будет внутренним (да, бывают и другие типы, о которых можно почитать в отдельной статье).

При этом синтаксисе для указания условия используется специальный оператор ON вместо WHERE. Фактически это тоже самое, но ON является обязательным продолжением JOIN, что не позволит допустить извлечения избыточных данных.

В большинстве СУБД поддерживается оба синтаксиса создания объединений, но JOIN-ы являются предпочтительными согласно спецификации ANSI SQL.

Объединения нескольких таблиц

Мы можем объединить и больше двух таблиц, главное не забывать указывать условия связей:

SELECT student_surname, group_name, teacher_surname FROM Students, Groups, Teachers WHERE Students.group_id = Groups.group_id AND curator_id = teacher_id;

student_surname | group_name | teacher_surname ------------------------------------------------- Шульгина | 304F | Потапова Колобков | 304F | Потапова Распопов | 304F | Потапова Адамченко | 304F | Потапова Сейдинай | 304F | Потапова Петрашевский | 304F | Потапова Римский | 580Z | Игнатьева Грошев | 117F | Донских Легран | 117F | Донских Егорова | 117F | Донских

В этом примере мы присоединили ещё одну таблицу — Teachers, — и вывели фамилии кураторов групп. Внешним ключом, связывающим Groups с таблицей Teachers, является поле curator_id, которому соответствуют первичные ключи Groups.

Язык SQL не накладывает ограничений на количество таблиц, которые вы хотите объединить, тем не менее многие СУБД такие ограничения имеют. Что, впрочем, вполне оправдано: не следует использовать JOIN-ы без особой надобности — это может повлечь существенное снижение производительности.

И теперь самое время вернуться к примеру из предыдущего урока. Напомним, с помощью 2х подзапросов мы получали список студентов, обучающихся на факультете Компьютерных технологий:

SELECT student_surname FROM Students WHERE group_id IN (SELECT group_id FROM Groups WHERE faculty_id = (SELECT faculty_id FROM Faculties WHERE faculty_name = 'Компьютерных технологий'));

Давайте попробуем переписать этот запрос с помощью объединений:

SELECT student_surname FROM Students, Groups, Faculties WHERE Students.group_id = Groups.group_id AND Groups.faculty_id = Faculties.faculty_id AND faculty_name = 'Компьютерных технологий';

Выглядит нагляднее, а результат, как вы понимаете, тот же:

student_surname ----------------------- Петрашевский Сейдинай Егорова Адамченко Легран Распопов Колобков Шульгина Грошев

В запросе участвуют три таблицы и три условия WHERE, два из которых служат для связи таблиц, и одно — для стандартной фильтрации данных.

Экспериментируйте

Зачастую один и тот же результат можно получить несколькими разными способами. Какой из них будет более уместным, зависит от многих факторов: используемая СУБД, объём данных в таблице, наличие индексов и ключей и др.

Key Words for FKN + antitotal forum (CS VSU):

• SQL как начать
• sql базовый курс
• sql join
• LEFT JOIN
• right join
• inner join
• реляционные таблицы
• объединения таблиц

Соединения таблиц

Как уже отмечалось, различные виды соединений используются для получения составных наборов данных, содержащих столбцы из нескольких таблиц, которые хранят связанные данные. Формат секций FROM и WHERE при неявном соединении таблиц имеет вид

FROM [[AS] псевдоним].], [[AS] псевдоним2]. .. [WHERE [AND ]. ]

Алгоритм выполнения запросов на неявное соединение таблиц состоит из нескольких этапов [25]:

• 1) вычисляется декартово произведение таблиц, входящих в соединение, т. е. для каждой строки одной из таблиц берутся все возможные сочетания строк из других таблиц;
• 2) производится отбор строк из полученной таблицы согласно условиям в секции WHERE;
• 3) осуществляется проекция (вывод) по столбцам, указанным в списке возвращаемых элементов.

Среди запросов на соединение таблиц наиболее распространены запросы к таблицам, которые связаны с помощью отношения «роди- телъ-потомок». Чтобы использовать в запросе отношение «родитель- потомок», необходимо задать , в котором первичный ключ родительской таблицы сравнивается с внешним ключом таблицы-потомка (обычно имена этих столбцов совпадают в связанных таблицах). Несмотря на то, что в языке определения данных присутствует возможность декларативного задания первичных и внешних ключей таблиц, связь, о которой идет речь, должна всегда явно указываться в секции WHERE запроса SELECT.

Например, необходимо вывести для всех абонентов названия улиц, на которых они проживают (рис. 3.73). Для этого нужно каждую строку из таблицы Abonent соединить по полю внешнего ключа (столбец StreetCD в таблице Abonent) с таблицей улиц (столбец StreetCD в таблице Street). Следующий запрос позволяет получить требуемый результат:

SELECT Abonent.Fio, Street.StreetNM FROM Abonent, Street

WHERE Abonent.StreetCD = Street.StreetCD;

Следует обратить внимание на то, что к столбцам таблиц обращение производится на основе полного имени столбца (с указанием таблицы, к которой он относится), чтобы исключить неоднозначности, возникающие в случае присутствия в разных таблицах столбцов с одинаковыми именами (например, столбцов StreetCD в таблицах Abonent и Street).

Рис. 3.73. Результат выполнения многотабличного запроса

Чтобы ускорить ввод запросов и сделать их более понятными, в списке таблиц можно определить псевдонимы таблиц (сокращенные имена). Например, предыдущий запрос можно записать более кратко:

SELECT A.Fio, S.StreetNM FROM Abonent A, Street S WHERE A.StreetCD = S.StreetCD;

В многотабличном запросе можно комбинировать условие соединения, в котором задаются связанные столбцы, с условиями поиска. Например, для вывода ФИО абонентов, которым за 2018 г. начислены суммы более 350 (рис. 3.74), можно использовать запрос

SELECT A.Fio, N.NachislSum FROM Abonent A, NachislSumma N WHERE A.AccountCD = N.AccountCD

AND NachislYear = 2018 AND NachislSum > 350;

Термин «соединение» применяется к любому запросу, который объединяет данные нескольких таблиц БД путем сравнения значений в парах столбцов этих таблиц. Самыми распространенными являются соединения по равенству.

Рис. 3.74. Результат выполнения многотабличного запроса с комбинированным условием поиска

Кроме того, имеется возможность соединять таблицы с помощью других операций сравнения. Например, чтобы вывести все комбинации ФИО абонентов и исполнителей ремонтных заявок так, чтобы ФИО абонентов были различными при сравнении, можно использовать следующий запрос с соединением таблиц по неравенству:

SELECT A.Fio, E.Fio FROM Abonent A, Executor E WHERE A.Fio != E.Fio;

Как следует из данного примера, соединения таблиц по условиям, отличающимся от равенства, во многом искусственны. Поэтому в подавляющем большинстве случаев таблицы соединяются по равенству, а другие операции сравнения используются для дополнительного отбора строк в условии поиска.

Язык SQL позволяет соединять три таблицы и более, используя ту же самую методику, что и при соединении данных из двух таблиц. Например, чтобы в предыдущем запросе вместе со значениями начислений вывести и значения оплат за тот же период и за ту же услугу (рис. 3.75), можно использовать соединение трех таблиц:

SELECT A.Fio, N.NachislSum, P.PaySum FROM Abonent A, NachislSumma N, PaySumma P WHERE A.AccountCD = N.AccountCD

AND A.AccountCD = P.AccountCD AND N.ServiceCD = P.ServiceCD AND N.NachislMonth = P.PayMonth AND N.NachislYear = P.PayYear AND NachislYear = 2018 AND NachislSum > 350;

Puc. 3.75. Результат выполнения запроса на соединение трех таблиц

Следует отметить, что результат выполнения запроса на неявное соединение более двух таблиц не зависит от порядка перечисления этих таблиц в секции FROM и от порядка указания условий соединения в секции WHERE. В таблицах соединяются только те строки, для которых выполняется , и независимо от порядка соединения результат будет одинаковый.

Можно соединять данные из трех и более таблиц, связанных более чем одним отношением «родитель-потомок».

Например, чтобы определить исполнителей, которым назначены ремонтные заявки абонентов, необходимо соединить таблицы Abonent,

Request и Executor. Для этого нужно каждую строку из таблицы Request связать по полю внешнего ключа AccountCD со справочником абонентов (столбец AccountCD в таблице Abonent), а по полю внешнего ключа ExecutorCD — со справочником исполнителей (столбец ExecutorCD в таблице Executor). Для этого можно использовать такой запрос:

SELECT DISTINCT A.Fio AS Fio_Abonent, E.Fio AS Fio_Executor FROM Abonent A, Executor E, Request R

WHERE R.AccountCD = A.AccountCD AND R.ExecutorCD = E.ExecutorCD; Результат выполнения запроса представлен на рис. 3.76.

Рис. 3.76. Результат выполнения запроса на соединение трех таблиц

Таким образом, при выполнении операции неявного соединения данные из двух таблиц комбинируются с образованием пар связанных строк, в которых значения сопоставляемых столбцов являются одинаковыми. Если одно из значений в сопоставляемом столбце одной таблицы не совпадает ни с одним из значений в сопоставляемом столбце другой таблицы, то соответствующая строка удаляется из результирующей таблицы.

3.3.1.2. Явное соединение

Другим способом связывания таблиц является явное соединение, осуществляемое с помощью JOIN. Формат секции FROM при таком соединении таблиц имеет вид

Итак, существуют различные типы явного соединения таблиц.

1. Перекрестное соединение CROSS JOIN используется без конструкции ON . CROSS JOIN эквивалентно декарто- вому произведению таблиц. Иными словами, конструкция . FROM A CROSS JOIN В полностью эквивалентна конструкции . FROM А, В.

Перекрестное (полное) соединение группирует строки таблиц по правилу «каждая с каждой». Первая строка первой таблицы соединяется с первой строкой второй таблицы, потом первая строка первой таблицы соединяется со второй строкой второй таблицы и так до тех пор, пока в первой таблице не закончатся строки.

2. Уточненные соединения, которые предполагают явное задание условия соединения после ON или имен столбцов, по которым производится соединение, после USING.

Для визуального представления различных типов соединения часто используются диаграммы Эйлера — Венна (также используется сокращенное название — диаграммы Венна) [26]. Диаграмма Венна представляет собой схематичное изображение всех возможных пересечений нескольких множеств и полезна для понимания, как происходит выборка данных в таблицах с использованием соединений и условий.

Среди уточненных соединений выделяют:

• INNER JOIN — «внутреннее» соединение (рис. 3.77). В таблицах соединяются только те строки, для которых выполняется : все значения связанных столбцов одной таблицы попарно находятся в заданном оператором сравнения отношении с соответствующими значениями связанных столбцов другой таблицы. Остальные строки из соединения исключаются. Этот вид соединения используется чаще всего; • [1]

Рис. 3.77. Диаграмма Венна для «внутреннего» соединения чевым словом определения типа внешнего соединения. Внешние соединения бывают трех типов:

— LEFT [OUTER] JOIN — «левое (внешнее)» соединение (рис. 3.78); выполняется сравнение значений связанных столбцов и в результат запроса включаются все строки левой таблицы и только те строки правой таблицы, для которых выполняется (для строк из левой таблицы, для которых не найдено соответствия в правой таблице, в столбцы, извлекаемые из правой таблицы, заносится NULL);

Рис. 3.78. Диаграмма Венна для «левого (внешнего)» соединения

— RIGHT [OUTER] JOIN — «правое (внешнее)» соединение (рис. 3.79); является зеркальным отображением левого внешнего соединения, в нем также сначала выполняется сравнение значений связанных столбцов, но в результат запроса включаются все строки правой таблицы и только те строки левой таблицы, для которых выполняется (для строк из правой таблицы, для которых не найдено соответствия в левой таблице, в столбцы, извлекаемые из левой таблицы, заносится NULL);

Рис. 3.79. Диаграмма Венна для «правого (внешнего)» соединения

— FULL [OUTER] JOIN — «полное (внешнее)» соединение — это комбинация «левого» и «правого» соединений таблиц (рис. 3.80); позволяет возвращать все строки как из правой, так и из левой таблицы, включая те, которые не удовлетворяют условиям соединения (строки, у которых нет пары по связанным столбцам, возвращаются со значениями NULL в выходных столбцах «противоположной» таблицы).

Если не указан тип соединения в JOIN, то он по умолчанию принимается за INNER.

3. Естественное соединение (NATURAL JOIN). Стандарт SQL определяет это соединение как результат соединения таблиц по всем одноименным столбцам. Соединяются те строки, в которых все значения одноименных столбцов одной таблицы попарно совпадают с соответствующими значениями одноименных столбцов другой таблицы. Остальные строки из соединения исключаются. Если одноименных столбцов нет, то выполняется перекрестное соединение CROSS JOIN. Естественное соединение не требует задания каких-либо условий (используется без ON ). Может применяться при внутреннем и внешнем соединении таблиц.

Рис. 3.80. Диаграмма Венна для «полного (внешнего)» соединения

В уточненных явных соединениях , указываемое после ON, аналогично рассмотренному выше условию соединения в секции WHERE. При соединении данных из нескольких таблиц конструкции JOIN и WHERE в большинстве случаев взаимозаменяемы. Однако зачастую конструкция JOIN является более удобной для понимания. Например, конструкция JOIN. ON дает возможность отличать условие соединения, указываемое после ON, от условия поиска, указываемого в секции WHERE.

Например, необходимо вывести для всех абонентов названия улиц, на которых они проживают. Решение этого примера на основе конструкции WHERE было приведено выше (см. рис. 3.73). Реализация запроса посредством явного внутреннего соединения таблиц, позволяющего выбрать только строки, которые есть и в таблице Street, и в таблице Abonent, имеет вид

SELECT A.Fio, S.StreetNM

FROM Abonent A INNER DOIN Street S ON A.StreetCD = S.StreetCD;

Если в этом же примере использовать правое внешнее соединение, то получится список всех улиц и проживающих на них абонентов, если таковые имеются. Запрос выглядит так:

SELECT A.Fio, S.StreetNM

FROM Abonent A RIGHT DOIN Street S ON A.StreetCD = S.StreetCD;

Результат выполнения запроса представлен на рис. 3.81.

Для каждого абонента выводится название улицы, на которой он проживает, а если на какой-либо улице не проживают абоненты, то в столбце Fio строки для данной улицы выводится NULL.

Если в данном примере использовать левое внешнее соединение, то результат будет совпадать с результатом внутреннего соединения

(см. рис. 3.73), так как в учебной БД нет абонентов, для которых не указана улица проживания. Если же использовать полное внешнее соединение, то для данного примера результат будет совпадать с результатом правого внешнего соединения (см. рис. 3.81).

Рис. 3.81. Результат правого внешнего соединения

Нужно отметить, что необязательно в качестве имен соединяемых таблиц использовать только явные имена таблиц или только псевдонимы таблиц. Допускается применять их сочетание. Следующий запрос (использующий по умолчанию «внутреннее» соединение таблиц) вернет правильный результат:

SELECT Abonent.Fio, S.StreetCD, S.StreetNM

FROM Abonent DOIN Street S ON Abonent.StreetCD = S.StreetCD;

Рассмотрим примеры запросов, в которых используются явное соединение таблиц и условие поиска в секции WHERE.

Например, если предыдущий запрос с явным «правым» соединением дополнить условием и выполнить запрос

SELECT A.Fio, S.StreetCD, S.StreetNM

FROM Abonent A RIGHT DOIN Street S ON A.StreetCD = S.StreetCD WHERE A.StreetCD IS NULL;

то будет выведена информация только об улицах с кодами 1, 2 и 5, на которых не проживают абоненты.

Для выбора ФИО абонентов, которые производили оплату услуги с кодом 2 позднее 1 октября 2016 г. (рис. 3.82), запрос с явным «внутренним» соединением может быть построен так:

SELECT A.Fio, P.PaySum

FROM Abonent A JOIN PaySumma P ON A.AccountCD = P.AccountCD WHERE ServiceCD=2 AND PayDate > ‘01.10.2016’;

Puc. 3.82. Результат наложения дополнительных условий поиска для соединяемых таблиц

В Firebird существует возможность более простого по сравнению с конструкцией ON задания условия соединения — соединение по именам столбцов. Если таблицы соединяются по одноименным столбцам, то можно использовать конструкцию USING ( ).

В указываются имена всех столбцов, по значениям в которых требуется соединить таблицы.

При создании соединения по именам столбцов нужно помнить:

• • что все столбцы, указанные в списке столбцов, должны существовать в соединяемых таблицах;
• • по всем указанным столбцам автоматически создается такое соединение, как если бы было указано .столбец = . столбец в секции WHERE при неявном соединении или после ON при явном соединении.

Например, рассмотренный ранее запрос, выводящий для всех абонентов названия и коды улиц, можно реализовать с использованием конструкции USING:

SELECT A.Fio, S.StreetCD, S.StreetNM

FROM Abonent A INNER DOIN Street S USING (StreetCD);

Этот же запрос можно реализовать с помощью естественного соединения:

SELECT A.Fio, S.StreetCD, S.StreetNM FROM Abonent A NATURAL DOIN Street S;

С помощью оператора JOIN можно соединять три таблицы и более. Порядок соединений может уточняться круглыми скобками, так как результат нескольких внешних соединений зависит от порядка их выполнения.

Например, вывести 10 первых строк с адресом и ФИО абонентов, проживающих на улицах, наименования которых начинаются с букв Г или М, указав для каждого абонента значения начислений, можно с помощью запроса

SELECT FIRST 10 S.StreetNM,(‘д.’||A.HouseNo) AS House,

(‘kb.’||A.FlatNo) AS Flat,

(N.NachislMonth || ’ месяц ‘ || N.NachislYear

II ‘ r.’) AS Period

FROM (Abonent A RIGHT DOIN Street S

ON A.StreetCD = S.StreetCD)

FULL JOIN NachislSumma N

ON A.AccountCD = N.AccountCD WHERE (S.StreetNM LIKE ‘M%’) OR (S.StreetNM LIKE ‘Г%’)

ORDER BY N.NachislYear ASC, N.NachislMonth ASC;

Результат выполнения запроса представлен на рис. 3.83.

Рис. 3.83. Результат соединения трех таблиц

В НД включена и ГАГАРИНА УЛИЦА, на которой не проживают абоненты, так как сначала выполнялось правое внешнее соединение таблиц

Abonent и Street, а затем полное внешнее соединение полученной таблицы с таблицей NachislSumma. Итоговый результат отсортирован по возрастанию года начислений, а внутри года — по возрастанию месяца.

3.3.1.3. Реализация стандартных операций реляционной алгебры Селекция (горизонтальное подмножество) создается из тех строк таблицы, которые удовлетворяют заданным условиям:

SELECT * FROM Abonent WHERE Phone IS NOT NULL;

Проекция (вертикальное подмножество) создается из указанных столбцов таблицы с последующим исключением избыточных дубликатов строк:

SELECT DISTINCT StreetCD FROM Abonent;

Декартово произведение. Для получения декартова произведения таблиц в секции FROM необходимо указать перечень перемножаемых таблиц, а в секции SELECT—все их столбцы. Перемножим таблицы Abonent (12 строк) и Street (8 строк) и получим результирующую таблицу (96 строк): SELECT Abonent.*, Street.* FROM Abonent, Street;

Фрагмент результата выполнения запроса представлен на рис. 3.84. Такой же результат может быть получен, если использовать запрос на явное перекрестное соединение:

SELECT Abonent.*, Street.* FROM Abonent CROSS DOIN Street;

Таким образом, декартово произведение двух таблиц — это набор всевозможных комбинаций строк из двух таблиц.

Экви-соединение. Для получения экви-соединения таблиц необходимо для декартова произведения таблиц установить имеющее смысл соответствие на основе равенства между столбцами соединяемых таблиц. Например, запрос на неявное экви-соединение таблиц Abonent и Street будет выглядеть таким образом:

SELECT Abonent.*, Street.* FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD;

Фрагмент результата выполнения запроса представлен на рис. 3.85. Такой же результат может быть получен, если использовать запрос на явное соединение:

SELECT Abonent.*, Street.*

FROM Abonent DOIN Street ON Abonent.StreetCD = Street.StreetCD;

Естественное соединение. Для получения естественного соединения таблиц необходимо из экви-соединения таблиц исключить дубликаты повторяющихся столбцов (входящих в условие соединения). Для предыдущего примера естественное соединение таблиц Abonent и Street по столбцу StreetCD выглядит так:

SELECT AccountCD, Street.StreetCD, StreetNM, HouseNo, FlatNo,

FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD;

Фрагмент результата выполнения запроса представлен на рис. 3.86.

Рис. 3.84. Декартово произведение таблиц Abonent и Street

Рис. 3.85. Экви-соединение таблиц Abonent и Street

Рис. 3.86. Естественное соединение таблиц Abonent и Street

Аналогичный результат может быть получен, если использовать следующий запрос на явное естественное соединение, например:

SELECT AccountCD, StreetCD, StreetNM, HouseNo, FlatNo, Fio, Phone FROM Abonent NATURAL JOIN Street;

Композиция. Для создания композиции таблиц нужно исключить из вывода все столбцы, по которым проводилось соединение таблиц:

SELECT AccountCD, StreetNM, HouseNo, FlatNo, Fio, Phone FROM Abonent, Street WHERE Abonent.StreetCD = Street.StreetCD;

Фрагмент результата выполнения запроса представлен на рис. 3.87.

Рис. 3.87. Композиция таблиц Abonent и Street

Тета-соединение. Тета-соединение предназначено для тех случаев, когда необходимо соединить две таблицы на основе некоторых условий, отличных от равенства. Получить тета-соединение таблиц Abonent и Street можно таким образом:

SELECT Abonent.*, Street.*

FROM Abonent, Street WHERE Abonent.StreetCD

• [1] OUTER JOIN — «внешнее» соединение. Данное ключевое словоявляется необязательным и имеет смысл только в комбинации с клю-

Секция JOIN

JOIN создаёт новую таблицу путем объединения столбцов из одной или нескольких таблиц с использованием общих для каждой из них значений. Это обычная операция в базах данных с поддержкой SQL, которая соответствует join из реляционной алгебры. Частный случай соединения одной таблицы часто называют self-join.

Синтаксис

SELECT expr_list> FROM left_table> [GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ASOF] JOIN right_table> (ON expr_list>)|(USING column_list>) ... 

Выражения из секции ON и столбцы из секции USING называются «ключами соединения». Если не указано иное, при присоединение создаётся Декартово произведение из строк с совпадающими значениями ключей соединения, что может привести к получению результатов с гораздо большим количеством строк, чем исходные таблицы.

Поддерживаемые типы соединения​

Все типы из стандартного SQL JOIN поддерживаются:

• INNER JOIN , возвращаются только совпадающие строки.
• LEFT OUTER JOIN , не совпадающие строки из левой таблицы возвращаются в дополнение к совпадающим строкам.
• RIGHT OUTER JOIN , не совпадающие строки из правой таблицы возвращаются в дополнение к совпадающим строкам.
• FULL OUTER JOIN , не совпадающие строки из обеих таблиц возвращаются в дополнение к совпадающим строкам.
• CROSS JOIN , производит декартово произведение таблиц целиком, ключи соединения не указываются.

Без указания типа JOIN подразумевается INNER . Ключевое слово OUTER можно опускать. Альтернативным синтаксисом для CROSS JOIN является указание нескольких таблиц, разделённых запятыми, в секции FROM.

Дополнительные типы соединений, доступные в ClickHouse:

• LEFT SEMI JOIN и RIGHT SEMI JOIN , белый список по ключам соединения, не производит декартово произведение.
• LEFT ANTI JOIN и RIGHT ANTI JOIN , черный список по ключам соединения, не производит декартово произведение.
• LEFT ANY JOIN , RIGHT ANY JOIN и INNER ANY JOIN , Частично (для противоположных сторон LEFT и RIGHT ) или полностью (для INNER и FULL ) отключает декартово произведение для стандартных видов JOIN .
• ASOF JOIN и LEFT ASOF JOIN , Для соединения последовательностей по нечеткому совпадению. Использование ASOF JOIN описано ниже.

Примечание

Если настройка join_algorithm установлена в значение partial_merge , то для RIGHT JOIN и FULL JOIN поддерживается только уровень строгости ALL ( SEMI , ANTI , ANY и ASOF не поддерживаются).

Настройки​

Значение строгости по умолчанию может быть переопределено с помощью настройки join_default_strictness.

Поведение сервера ClickHouse для операций ANY JOIN зависит от параметра any_join_distinct_right_table_keys.

См. также

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_optimizations
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

Условия в секции ON​

Секция ON может содержать несколько условий, связанных операторами AND и OR . Условия, задающие ключи соединения, должны содержать столбцы левой и правой таблицы и должны использовать оператор равенства. Прочие условия могут использовать другие логические операторы, но в отдельном условии могут использоваться столбцы либо только левой, либо только правой таблицы.

Строки объединяются только тогда, когда всё составное условие выполнено. Если оно не выполнено, то строки могут попасть в результат в зависимости от типа JOIN . Обратите внимание, что если то же самое условие поместить в секцию WHERE , то строки, для которых оно не выполняется, никогда не попаду в результат.

Оператор OR внутри секции ON работает, используя алгоритм хеш-соединения — на каждый аргумент OR с ключами соединений для JOIN создается отдельная хеш-таблица, поэтому потребление памяти и время выполнения запроса растет линейно при увеличении количества выражений OR секции ON .

Примечание

Если в условии использованы столбцы из разных таблиц, то пока поддерживается только оператор равенства ( = ).

Пример

Рассмотрим table_1 и table_2 :

┌─Id─┬─name─┐ ┌─Id─┬─text───────────┬─scores─┐ │ 1 │ A │ │ 1 │ Text A │ 10 │ │ 2 │ B │ │ 1 │ Another text A │ 12 │ │ 3 │ C │ │ 2 │ Text B │ 15 │ └────┴──────┘ └────┴────────────────┴────────┘ 

Запрос с одним условием, задающим ключ соединения, и дополнительным условием для table_2 :

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2 ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text'); 

Обратите внимание, что результат содержит строку с именем C и пустым текстом. Строка включена в результат, потому что использован тип соединения OUTER .

┌─name─┬─text───┐ │ A │ Text A │ │ B │ Text B │ │ C │ │ └──────┴────────┘ 

Запрос с типом соединения INNER и несколькими условиями:

SELECT name, text, scores FROM table_1 INNER JOIN table_2 ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text'); 

┌─name─┬─text───┬─scores─┐ │ B │ Text B │ 15 │ └──────┴────────┴────────┘ 

Запрос с типом соединения INNER и условием с оператором OR :

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;  CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;  INSERT INTO t1 SELECT number as a, -a as b from numbers(5);  INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);  SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key; 

┌─a─┬──b─┬─val─┐ │ 0 │ 0 │ 0 │ │ 1 │ -1 │ 1 │ │ 2 │ -2 │ 2 │ │ 3 │ -3 │ 3 │ │ 4 │ -4 │ 4 │ └───┴────┴─────┘ 

Запрос с типом соединения INNER и условиями с операторами OR и AND :

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3; 

┌─a─┬──b─┬─val─┐ │ 0 │ 0 │ 0 │ │ 2 │ -2 │ 2 │ │ 4 │ -4 │ 4 │ └───┴────┴─────┘ 

Использование ASOF JOIN​

ASOF JOIN применим в том случае, когда необходимо объединять записи, которые не имеют точного совпадения.

Для работы алгоритма необходим специальный столбец в таблицах. Этот столбец:

• Должен содержать упорядоченную последовательность.
• Может быть одного из следующих типов: Int, UInt, Float, Date, DateTime, Decimal.
• Не может быть единственным столбцом в секции JOIN .

Синтаксис ASOF JOIN . ON :

SELECT expressions_list FROM table_1 ASOF LEFT JOIN table_2 ON equi_cond AND closest_match_cond 

Можно использовать произвольное количество условий равенства и одно условие на ближайшее совпадение. Например, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t

Условия, поддержанные для проверки на ближайшее совпадение: > , >= , < ,

Синтаксис ASOF JOIN . USING :

SELECT expressions_list FROM table_1 ASOF JOIN table_2 USING (equi_column1, ... equi_columnN, asof_column) 

Для слияния по равенству ASOF JOIN использует equi_columnX , а для слияния по ближайшему совпадению использует asof_column с условием table_1.asof_column >= table_2.asof_column . Столбец asof_column должен быть последним в секции USING .

Например, рассмотрим следующие таблицы:

 table_1 table_2  event | ev_time | user_id event | ev_time | user_id ----------|---------|---------- ----------|---------|----------  . . event_1_1 | 12:00 | 42 event_2_1 | 11:59 | 42  . event_2_2 | 12:30 | 42 event_1_2 | 13:00 | 42 event_2_3 | 13:00 | 42  . . 

ASOF JOIN принимает метку времени пользовательского события из table_1 и находит такое событие в table_2 метка времени которого наиболее близка к метке времени события из table_1 в соответствии с условием на ближайшее совпадение. При этом столбец user_id используется для объединения по равенству, а столбец ev_time для объединения по ближайшему совпадению. В нашем примере event_1_1 может быть объединено с event_2_1 , event_1_2 может быть объединено с event_2_3 , а event_2_2 не объединяется.

Примечание

ASOF JOIN не поддержан для движка таблиц Join.

Чтобы задать значение строгости по умолчанию, используйте сессионный параметр join_default_strictness.

Распределённый JOIN​

Есть два пути для выполнения соединения с участием распределённых таблиц:

• При использовании обычного JOIN , запрос отправляется на удалённые серверы. На каждом из них выполняются подзапросы для формирования «правой» таблицы, и с этой таблицей выполняется соединение. То есть, «правая» таблица формируется на каждом сервере отдельно.
• При использовании GLOBAL . JOIN , сначала сервер-инициатор запроса запускает подзапрос для вычисления правой таблицы. Эта временная таблица передаётся на каждый удалённый сервер, и на них выполняются запросы с использованием переданных временных данных.

Будьте аккуратны при использовании GLOBAL . За дополнительной информацией обращайтесь в раздел Распределенные подзапросы.

Неявные преобразования типов​

Запросы INNER JOIN , LEFT JOIN , RIGHT JOIN и FULL JOIN поддерживают неявные преобразования типов для ключей соединения. Однако запрос не может быть выполнен, если не существует типа, к которому можно привести значения ключей с обеих сторон (например, нет типа, который бы одновременно вмещал в себя значения UInt64 и Int64 , или String и Int32 ).

Пример

Рассмотрим таблицу t_1 :

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐ │ 1 │ 1 │ UInt16 │ UInt8 │ │ 2 │ 2 │ UInt16 │ UInt8 │ └───┴───┴───────────────┴───────────────┘ 

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐ │ -1 │ 1 │ Int16 │ Nullable(Int64) │ │ 1 │ -1 │ Int16 │ Nullable(Int64) │ │ 1 │ 1 │ Int16 │ Nullable(Int64) │ └────┴──────┴───────────────┴─────────────────┘ 

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b); 

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐ │ 1 │ 1 │ Int32 │ Nullable(Int64) │ │ 2 │ 2 │ Int32 │ Nullable(Int64) │ │ -1 │ 1 │ Int32 │ Nullable(Int64) │ │ 1 │ -1 │ Int32 │ Nullable(Int64) │ └────┴──────┴───────────────┴─────────────────┘ 

Рекомендации по использованию​

Обработка пустых ячеек и NULL​

При соединении таблиц могут появляться пустые ячейки. Настройка join_use_nulls определяет, как ClickHouse заполняет эти ячейки.

Если ключами JOIN выступают поля типа Nullable, то строки, где хотя бы один из ключей имеет значение NULL, не соединяются.

Синтаксис​

Требуется, чтобы столбцы, указанные в USING , назывались одинаково в обоих подзапросах, а остальные столбцы — по-разному. Изменить имена столбцов в подзапросах можно с помощью синонимов.

В секции USING указывается один или несколько столбцов для соединения, что обозначает условие на равенство этих столбцов. Список столбцов задаётся без скобок. Более сложные условия соединения не поддерживаются.

Ограничения cинтаксиса​

Для множественных секций JOIN в одном запросе SELECT :

• Получение всех столбцов через * возможно только при объединении таблиц, но не подзапросов.
• Секция PREWHERE недоступна.

Для секций ON , WHERE и GROUP BY :

• Нельзя использовать произвольные выражения в секциях ON , WHERE , и GROUP BY , однако можно определить выражение в секции SELECT и затем использовать его через алиас в других секциях.

Производительность​

При запуске JOIN , отсутствует оптимизация порядка выполнения по отношению к другим стадиям запроса. Соединение (поиск в «правой» таблице) выполняется до фильтрации в WHERE и до агрегации. Чтобы явно задать порядок вычислений, рекомендуется выполнять JOIN подзапроса с подзапросом.

Каждый раз для выполнения запроса с одинаковым JOIN , подзапрос выполняется заново — результат не кэшируется. Это можно избежать, используя специальный движок таблиц Join, представляющий собой подготовленное множество для соединения, которое всегда находится в оперативке.

В некоторых случаях более эффективно использовать IN вместо JOIN .

Если JOIN необходим для соединения с таблицами измерений (dimension tables — сравнительно небольшие таблицы, которые содержат свойства измерений — например, имена для рекламных кампаний), то использование JOIN может быть не очень удобным из-за громоздкости синтаксиса, а также из-за того, что правая таблица читается заново при каждом запросе. Специально для таких случаев существует функциональность «Внешние словари», которую следует использовать вместо JOIN . Дополнительные сведения смотрите в разделе «Внешние словари».

Ограничения по памяти​

По умолчанию ClickHouse использует алгоритм hash join. ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти. При включённой настройке join_algorithm = ‘auto’ , после некоторого порога потребления памяти ClickHouse переходит к алгоритму merge join. Описание алгоритмов JOIN см. в настройке join_algorithm.

Если вы хотите ограничить потребление памяти во время выполнения операции JOIN , используйте настройки:

• max_rows_in_join — ограничивает количество строк в хеш-таблице.
• max_bytes_in_join — ограничивает размер хеш-таблицы.

По достижении любого из этих ограничений ClickHouse действует в соответствии с настройкой join_overflow_mode.

Примеры​

SELECT  CounterID,  hits,  visits FROM ( SELECT  CounterID, count() AS hits FROM test.hits GROUP BY CounterID ) ANY LEFT JOIN ( SELECT  CounterID, sum(Sign) AS visits FROM test.visits GROUP BY CounterID ) USING CounterID ORDER BY hits DESC LIMIT 10 

┌─CounterID─┬───hits─┬─visits─┐ │ 1143050 │ 523264 │ 13665 │ │ 731962 │ 475698 │ 102716 │ │ 722545 │ 337212 │ 108187 │ │ 722889 │ 252197 │ 10547 │ │ 2237260 │ 196036 │ 9522 │ │ 23057320 │ 147211 │ 7689 │ │ 722818 │ 90109 │ 17847 │ │ 48221 │ 85379 │ 4652 │ │ 19762435 │ 77807 │ 7026 │ │ 722884 │ 77492 │ 11056 │ └───────────┴────────┴────────┘