Как джойнить таблицу саму на себя

Inner Join таблицы на себя

И нужно результат запроса как-то связать с таблицей [stat].[dbo].[bill_dir_Tariffs] так, чтобы в итоге выводились все остальные колонки таблицы, а не только [code], [trunkid], [Comments]. Запуталась в JOIN-овском синтаксисе, две таблицы без проблем свяжу, а вот саму на себя замкнуть — путаюсь. Есть идеи?

Добавлено через 22 минуты

1 2 3 4 5 6

SELECT die.[code], die.[trunkid], die.[Comments], COUNT(*) FROM [stat].[dbo].[bill_dir_Tariffs] AS die, [stat].[dbo].[bill_dir_Tariffs] AS live INNER JOIN die ON die.[trunkid]=live.[trunkid] WHERE NOT code IS NULL GROUP BY [code], [trunkid], [Comments] HAVING COUNT(*)>1 ORDER BY [code];

Вот такой код не работает у меня, ошибка 208, инвалид обджект нейм «дай».

Как джойнить таблицу саму на себя

До этого все наши запросы обращались только к одной таблице. Однако запросы могут также обращаться сразу к нескольким таблицам или обращаться к той же таблице так, что одновременно будут обрабатываться разные наборы её строк. Запрос, обращающийся к разным наборам строк одной или нескольких таблиц, называется соединением (JOIN). Например, мы захотели перечислить все погодные события вместе с координатами соответствующих городов. Для этого мы должны сравнить столбец city каждой строки таблицы weather со столбцом name всех строк таблицы cities и выбрать пары строк, для которых эти значения совпадают.

Примечание

Это не совсем точная модель. Обычно соединения выполняются эффективнее (сравниваются не все возможные пары строк), но это скрыто от пользователя.

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

SELECT * FROM weather, cities WHERE city = name;

city |temp_lo|temp_hi| prcp| date | name | location --------------+-------+-------+-----+-----------+--------------+---------- San Francisco| 46| 50| 0.25| 1994-11-27| San Francisco| (-194,53) San Francisco| 43| 57| 0| 1994-11-29| San Francisco| (-194,53) (2 rows)

Обратите внимание на две особенности полученных данных:

В результате нет строки с городом Хейуорд (Hayward). Так получилось потому, что в таблице cities нет строки для данного города, а при соединении все строки таблицы weather , для которых не нашлось соответствие, опускаются. Вскоре мы увидим, как это можно исправить.

Название города оказалось в двух столбцах. Это правильно и объясняется тем, что столбцы таблиц weather и cities были объединены. Хотя на практике это нежелательно, поэтому лучше перечислить нужные столбцы явно, а не использовать * :

SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

Упражнение: Попробуйте определить, что будет делать этот запрос без предложения WHERE .

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

SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather, cities WHERE cities.name = weather.city;

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

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

SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);

Эта запись не так распространена, как первый вариант, но мы показываем её, чтобы вам было проще понять следующие темы.

Сейчас мы выясним, как вернуть записи о погоде в городе Хейуорд. Мы хотим, чтобы запрос просканировал таблицу weather и для каждой её строки нашёл соответствующую строку в таблице cities . Если же такая строка не будет найдена, мы хотим, чтобы вместо значений столбцов из таблицы cities были подставлены « пустые значения » . Запросы такого типа называются внешними соединениями. (Соединения, которые мы видели до этого, называются внутренними.) Эта команда будет выглядеть так:

SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name); city |temp_lo|temp_hi| prcp| date | name | location --------------+-------+-------+-----+-----------+--------------+---------- Hayward | 37| 54| | 1994-11-29| | San Francisco| 46| 50| 0.25| 1994-11-27| San Francisco| (-194,53) San Francisco| 43| 57| 0| 1994-11-29| San Francisco| (-194,53) (3 rows)

Этот запрос называется левым внешним соединением, потому что из таблицы в левой части оператора будут выбраны все строки, а из таблицы справа только те, которые удалось сопоставить каким-нибудь строкам из левой. При выводе строк левой таблицы, для которых не удалось найти соответствия в правой, вместо столбцов правой таблицы подставляются пустые значения (NULL).

Упражнение: Существуют также правые внешние соединения и полные внешние соединения. Попробуйте выяснить, что они собой представляют.

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

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi >W2.temp_hi; city | low | high | city | low | high ---------------+-----+------+---------------+-----+------ San Francisco | 43 | 57 | San Francisco | 46 | 50 Hayward | 37 | 54 | San Francisco | 46 | 50 (2 rows)

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

SELECT * FROM weather w, cities c WHERE w.city = c.name;

Вы будете встречать сокращения такого рода довольно часто.

Пред.	Наверх	След.
2.5. Выполнение запроса	Начало	2.7. Агрегатные функции

Как получить дважды одну и ту же таблицу с JOIN?

Добрый вечер, столкнулся с очередной задачей с запросом sql, который я решил усложнить и у меня все сломалось. Использую Postgresql, но значения я думаю это не имеет.

У меня был следующий запрос, где «car» содержала client_id столбик таблицы «client»:

SELECT client.someone_field, car.* FROM car INNER JOIN client ON client.id = car.client_id WHERE 1=1 ';

Запрос простой, возвращает данные из связанной по id данные из второй таблицы.

Но нужно сделать ещё одну привязку с той же второй таблицей (client), но уже через связывающую таблицу, я прикинул это так:

SELECT client.someone_field, // данные от первой связки client_second.someone_field // данные от второй связки car.* FROM car INNER JOIN client ON client.id = car.client_id INNER JOIN client_car ON client_car.car_id = car.id // связывающая таблица которая имеет id одного поля и id другого поля (один к одному) INNER JOIN client client_second ON client_second.id = client_car.client_id // тут пытаюсь взять ID с противоположного столбика у связывающей таблицей и сравнить с таблицей client WHERE 1=1 ';

Собственно, кто осилил, такой запрос ничего не возвращает. Если это перевести в логику, то первый join это получаем владельца текущего «car», следующие 2 JOIN подразумевались для получения уже того кто управляет текущим «car».

• Вопрос задан более трёх лет назад
• 1794 просмотра

1 комментарий

Средний 1 комментарий

Понимание джойнов сломано. Это точно не пересечение кругов, честно

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

Чаще всего ответ примерно такой: «inner join — это как бы пересечение множеств, т.е. остается только то, что есть в обеих таблицах, а left join — это когда левая таблица остается без изменений, а от правой добавляется пересечение множеств. Для всех остальных строк добавляется null». Еще, бывает, рисуют пересекающиеся круги.

Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей.

Дело в том, что этот ответ в общем случае неверен. Ну или, как минимум, не точен.

Давайте рассмотрим почему, и заодно затронем еще парочку тонкостей join-ов.

Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин «пересечение» только путает.

(Update. В комментах идут жаркие споры о теории множеств и уникальности. Очень интересно, много нового узнал, спасибо)

INNER JOIN

Давайте сразу пример.

Итак, создадим две одинаковых таблицы с одной колонкой id, в каждой из этих таблиц пусть будет по две строки со значением 1 и еще что-нибудь.

INSERT INTO table1 (id) VALUES (1), (1) (3); INSERT INTO table2 (id) VALUES (1), (1), (2);

Давайте, их, что ли, поджойним

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Если бы это было «пересечение множеств», или хотя бы «пересечение таблиц», то мы бы увидели две строки с единицами.

На практике ответ будет такой:

| id | id | | --- | --- | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 |

Для начала рассмотрим, что такое CROSS JOIN. Вдруг кто-то не в курсе.

CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц. Например, есть две таблицы, в одной из них 3 строки, в другой — 2:

select * from t1;

id ---- 1 2 3

select * from t2;

id ---- 4 5

Тогда CROSS JOIN будет порождать 6 строк.

select * from t1 cross join t2; 

id | id ----+---- 1 | 4 1 | 5 2 | 4 2 | 5 3 | 4 3 | 5

Так вот, вернемся к нашим баранам.
Конструкция

t1 INNER JOIN t2 ON condition

— это, можно сказать, всего лишь синтаксический сахар к

t1 CROSS JOIN t2 WHERE condition

Т.е. по сути INNER JOIN — это все комбинации соединений строк с неким фильтром condition . В общем-то, можно это представлять по разному, кому как удобнее, но точно не как пересечение каких-то там кругов.

Небольшой disclaimer: хотя inner join логически эквивалентен cross join с фильтром, это не значит, что база будет делать именно так, в тупую: генерить все комбинации и фильтровать. На самом деле там более интересные алгоритмы.

LEFT JOIN

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

Опять же, создадим две таблицы:

insert into t1 (id) values (1), (1), (3); insert into t2 (id) values (1), (1), (4), (5);

Теперь сделаем LEFT JOIN:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

Результат будет содержать 5 строк, а не по количеству строк в левой таблице, как думают очень многие.

| id | id | | --- | --- | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 1 | 1 | | 3 | |

Так что, LEFT JOIN — это тоже самое что и INNER JOIN (т.е. все комбинации соединений строк, отфильтрованных по какому-то условию), и плюс еще записи из левой таблицы, для которых в правой по этому фильтру ничего не совпало.

LEFT JOIN можно переформулировать так:

SELECT * FROM t1 CROSS JOIN t2 WHERE t1.id = t2.id UNION ALL SELECT t1.id, null FROM t1 WHERE NOT EXISTS ( SELECT FROM t2 WHERE t2.id = t1.id )

Сложноватое объяснение, но что поделать, зато оно правдивее, чем круги с пересечениями и т.д.

Условие ON

Удивительно, но по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй. На самом деле там любое булево выражение.

Например, есть таблица со статистикой юзеров users_stats, и таблица с ip адресами городов.
Тогда к статистике можно прибавить город

SELECT s.id, c.city FROM users_stats AS s JOIN cities_ip_ranges AS c ON c.ip_range && s.ip

где && — оператор пересечения (см. расширение посгреса ip4r)

Если в условии ON поставить true, то это будет полный аналог CROSS JOIN

"table1 JOIN table2 ON true" == "table1 CROSS JOIN table2"

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

Есть люди, которые боятся join-ов как огня. Потому что «они тормозят». Знаю таких, где есть полный запрет join-ов по проекту. Т.е. люди скачивают две-три таблицы себе в код и джойнят вручную в каком-нибудь php.

Это, прямо скажем, странно.

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

Сложность этой задачи O(n!), где n — количество объединяемых таблиц. Поэтому для большого количества таблиц, потратив некоторое время на поиски оптимальной последовательности, планировщик прекращает эти поиски и делает такой план, какой успел придумать. В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.

Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не ‘LEFT JOIN… WHERE… IS NULL’, а конструкцию EXISTS. Это и читабельнее, и быстрее.

Выводы

Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина «пересечение».

Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты.

Update В этом видео я наглядно объясняю, как правильно визуализировать джойны (English):

Update2 Продолжение статьи здесь: https://habr.com/ru/post/450528/

Больше полезного можно найти на telegram-канале о разработке «Cross Join», где мы обсуждаем базы данных, языки программирования и всё на свете!