Для чего нужен cross join

JOIN в MySQL: INNER, LEFT, RIGHT, FULL, CROSS. Что это и как использовать

Опубликовано: 20 Июня 2019

Оператор JOIN, формирует результирующую таблицу по заданным условиям, из одной или нескольких уже существующих таблиц SQL. В зависимости от необходимого алгоритма формирования таблицы, к оператору можно подставлять ключевые слова: INNER, CROSS, FULL, LEFT, RIGHT.

Данный оператор полезен для возможности выстраивать более гибкие таблицы в SQL. Чтобы иметь возможность выносить «необязательные» или повторяющиеся данные в отдельные таблицы.

Обобщенный синтаксис оператора выглядит так:

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

Таблица со списком брендов:

Таблица со списком цветов:

INNER JOIN / CROSS JOIN

В некоторых SQL базах INNER JOIN не может идти без условия, но в MySQL это возможно, поэтому INNER JOIN и CROSS JOIN в данной SQL системе идентичны, как и JOIN, который является синонимом для INNER JOIN.

Простая выборка, без условий, подставит ко всем вариантам из левой таблицы, все варианты из правой таблицы (перекрестное соединение):

Тот же самый результат можно получить путем следующих записей, которые идентичны:

К выборке можно добавить условие, это актуально как для CROSS, так и для INNER JOIN. Выборку можно производить следующими способами:

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

Ту же самую таблицу можно получить следущими записями:

Если бы столбец id у таблицы с цветами назывался бы color_id, то запись для ON и WHERE была бы следующей:

LEFT JOIN / RIGHT JOIN / FULL JOIN

LEFT JOIN, RIGHT JOIN и FULL JOIN считаются внешними соединениями (OUTER JOIN), поэтому у них также есть синонимы: LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN.

LEFT JOIN и RIGHT JOIN отличаются от INNER JOIN тем, что к результирующей таблице добавляются строки не имеющие совпадений в соседней таблице. Если используется LEFT JOIN, добавляются все записи из таблицы указанной по левую сторону от оператора, если RIGHT JOIN, то из таблицы по правую сторону от оператора. В пару к таким строкам устанавливается значение NULL. Оба оператора не возможно использовать без какого-либо условия.

Это используется если, к примеру, надо вывести все доступные бренды машин, не зависимо от того указан у них цвет или нет:

Или все возможные цвета, независимо от того есть ли у брендов такой цвет в наличии:

Можно дополнить запрос условием на проверку несуществования соседних данных, и получить список записей, которые не имеют пары, при этом поля, которые необходимо вывести, можно указать, как и при обычном SELECT запросе:

FULL JOIN объединяет в себе LEFT JOIN и RIGHT JOIN.

В MySQL он используется без условий, результат использования этого оператора будет таким:

Но, при добавлении сравнения USING в MySQL, результат будет аналогичен INNER JOIN:

Другие условия с оператором FULL JOIN в MySQL использовать нельзя, по крайней мере на момент написания статьи.

Сложные и многотабличные запросы

В примере для простоты использовалось только 2 простых таблицы, в реальности же количество условий и таблиц может быть значительно больше. При необходимости таблицу так же можно объединять саму с собой, но в этом случае стоит использовать алиас для имени таблицы:

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

Объединения таблиц можно использовать не только в выборке, но и при удалении и изменении таблиц (UPDATE, DELETE).

Источник

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

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

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

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

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

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

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

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

INNER JOIN

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

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

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

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

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

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

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

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

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

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

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

LEFT JOIN

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

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

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

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

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

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

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

Условие ON

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

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

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

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

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

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

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

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

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

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

Выводы

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

Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты. А мы обсудим это в одном из ближайших выпусков подкаста «Цинковый прод». Не забудьте подписаться.

Источник

Понимание джойнов сломано. Продолжение. Попытка альтернативной визуализации

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

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

Все желающие приглашаются под кат

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

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

Допустим, у нас есть две таблицы. В одной из них

Сразу disclaimer: я назвал поле словом «id» просто для краткости. Многие в прошлой статье возмущались, как это так — id повторяются, безобразие. Не стоит сильно переживать, ну
представьте, например, что это таблица с ежедневной статистикой, где для каждого дня и каждого юзера есть данные по посещению какого-нибудь сайта. В общем, не суть.

Итак, мы хотим узнать, что же получится при различных джойнах таблиц. Начнем с CROSS JOIN:

CROSS JOIN

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

Визуализировать это можно так: по оси x — одна таблица, по оси y — другая, все клеточки внутри (выделены оранжевым) — это результат

INNER JOIN

INNER JOIN (или просто JOIN) — это тот же самый CROSS JOIN, у которого оставлены только те элементы, которые удовлетворяют условию, записанному в конструкции «ON». Обратите внимание на ситуацию, когда записи дублируются — результатов с единичками будет четыре штуки.

LEFT JOIN

LEFT OUTER JOIN (или просто LEFT JOIN) — это тоже самое, что и INNER JOIN, но дополнительно мы добавляем null для строк из первой таблицы, для которой ничего не нашлось во второй

RIGHT JOIN

RIGHT OUTER JOIN ( или RIGHT JOIN) — это тоже самое, что и LEFT JOIN, только наоборот. Т.е. это INNER JOIN + null для строк из второй таблицы, для которой ничего не нашлось в первой

→ Поиграть с запросами можно здесь

Выводы

Вроде бы получилась простая визуализация. Хотя в ней есть ограничения: здесь показан случай, когда в ON записано равенство, а не что-то хитрое (любое булево выражение). Кроме того не рассмотрен случай, когда среди значений таблицы есть null. Т.е. это всё равно некоторое упрощение, но вроде бы получилось лучше и точнее, чем круги Венна.

Подписывайтесь на наш подкаст «Цинковый прод», там мы обсуждаем базы данных, разработку софта и прочие интересные штуки.

Источник

SQL JOIN: руководство по объединению таблиц

Перевод первой части статьи «SQL Joins Tutorial: Cross Join, Full Outer Join, Inner Join, Left Join, and Right Join».

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

В этой статье вы рассмотрим все виды JOIN в SQL и расскажем, как ими пользоваться.

В первой части статьи:

(Спойлер: мы рассмотрим пять разных видов объединений, но на самом деле вам нужно знать только два из них!)

Что такое JOIN?

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

Прежде чем мы разберем, как писать JOIN-ы, давайте посмотрим, как выглядит объединение таблиц.

Возьмем для примера систему, в которой хранится информация о пользователях и их адресах.

Таблица, хранящая информацию о пользователях, может выглядеть следующим образом:

А таблица с адресами может быть такой:

Чтобы получить информацию и о пользователе, и о его адресе, мы можем написать два разных запроса. Но в идеале можно написать один запрос и получить все необходимые сведения в одном ответе.

Именно для этого, собственно, и нужны операции объединения!

Чуть позже мы рассмотрим, как составлять подобные запросы, а пока взгляните, как может вы глядеть результат объединения таблиц:

Мы видим всех наших пользователей сразу с их адресами.

Но операции объединения позволяют не просто выводить такие вот комбинированные сведения. У них есть еще одна важная функция: с их помощью можно получать отфильтрованные результаты.

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

Настройка базы данных

Прежде чем писать какие-либо запросы, нужно настроить базу данных.

Для примеров в этой статье будет использоваться PostgreSQL, но запросы и концепции, показанные здесь, легко применимы в любой другой современной СУБД (MySQL, SQL Server и т. д.).

Для работы с нашей базой данных PostgreSQL мы будем пользоваться интерактивной cli-программой psql. Если у вас установлен другой клиент, вы прекрасно можете работать с ним!

Теперь давайте воспользуемся интерактивной консолью (запустив команду psql ) и подключимся к только что созданной базе данных при помощи команды \c :

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

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

CROSS JOIN (перекрестное объединение)

Самое простое объединение, которое мы можем сделать, это CROSS JOIN (перекрестное объединение) или «декартово произведение».

При этом объединении мы берем каждую строку одной таблицы и соединяем ее с каждой строкой другой таблицы.

Каждое значение из первого списка соединено с каждым значением второго списка.

Давайте перепишем этот пример в виде SQL-запроса.

Для начала создадим две очень похожих таблицы и внесем в них данные:

Наши таблицы letters и numbers имеют по одному столбцу с простыми текстовыми полями.

Теперь давайте объединим эти таблицы, используя CROSS JOIN:

Хотя этот пример часто упоминается как чисто учебный, и для него есть практическое применение: покрытие диапазона дат.

CROSS JOIN с диапазонами дат

Хороший вариант использования CROSS JOIN — брать каждую строку таблицы и объединять ее с каждым днем из диапазона дат.

Скажем, вы создаете приложение, которое должно отслеживать ежедневную рутину (чистка зубов, завтрак, душ).

Если вы хотите генерировать запись для каждой задачи за каждый день прошлой недели, вы можете использовать CROSS JOIN с диапазоном дат.

Чтобы создать диапазон дат, мы можем воспользоваться функцией generate_series:

Функция generate_series принимает три параметра.

Второй параметр — текущая дата ( CURRENT_DATE ).

Третий параметр — шаг. То есть, на сколько мы хотим инкрементировать значение. Поскольку это ежедневные задачи, мы устанавливаем в качестве интервала один день ( INTERVAL ‘1 day’ ).

Все вместе генерирует серию дат, начиная с даты пятидневной давности и заканчивая сегодняшним днем, по дню за раз.

Результат запроса за последние пять дней плюс сегодняшний:

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

В нашей таблице tasks есть только один столбец — name — в который мы добавили несколько задач.

Теперь давайте осуществим перекрестное объединение наших задач с запросом на генерацию дат:

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

В результате мы получаем название задачи и день. Выглядит это следующим образом:

Как и ожидалось, мы получили по строке для каждой задачи на каждый день из нашего диапазона дат.

CROSS JOIN это простейшее объединение. Дальнейшие примеры потребуют более «жизненной» настройки таблиц.

Создание таблиц directors и movies

Чтобы проиллюстрировать следующие виды объединений, мы воспользуемся примером с фильмами (movies) и режиссерами (movie directors).

Каждый фильм имеет режиссера, но это не является обязательным условием. Может быть ситуация, когда фильм уже анонсировали, но режиссер еще не выбран.

В нашей таблице directors будут храниться имена всех режиссеров, а в таблице movies — названия фильмов, а также отсылка к режиссеру (если он известен).

Давайте создадим эти таблицы и внесем в них необходимые данные:

У нас есть пять режиссеров и пять фильмов, причем для трех фильмов указаны режиссеры. У режиссера с ID 1 есть два фильма, а у режиссера с ID 2 — один фильм.

FULL OUTER JOIN (полное внешнее объединение)

Теперь у нас есть данные, с которыми можно работать, так что переходим к следующему виду объединения — FULL OUTER JOIN.

FULL OUTER JOIN похоже на CROSS JOIN, но имеет важные отличия.

Первое отличие состоит в том, что для FULL OUTER JOIN требуется условие объединения.

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

Вот как это выглядит в коде:

Наш результат выглядит, как некое странное декартово произведение:

Сначала идут несколько строк с фильмами, для которых указаны режиссеры: здесь наше условие явно соблюдается.

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

Примечание: если вы не знакомы со значениями NULL, объяснение можно почитать здесь.

Здесь мы также видим второе различие между перекрестным и полным внешним объединением. FULL OUTER JOIN соединяет одну строку первой таблицы с определенной строкой второй таблицы, а при CROSS JOIN каждая строка одной таблицы соединяется с каждой строкой другой.

INNER JOIN

Следующий вид объединения — внутреннее, INNER JOIN. Внутреннее объединение является наиболее используемым.

INNER JOIN возвращает только те строки, где соблюдается условие объединения.

Если брать наш пример с таблицами movies и directors, внутреннее объединение вернет только те записи, где у фильма есть указанный режиссер.

Синтаксис практически тот же:

В результирующую выборку попали только фильмы с режиссерами:

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

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

Единственное отличие в том, что поскольку мы выбрали все столбцы ( SELECT * ) и сначала указали таблицу directors, то в результате первыми будут идти данные из этой таблицы, а дальше — из таблицы movies. Но данные, попавшие в выборку, те же.

Это полезное свойство операции внутреннего объединения: не у всех видов объединений оно есть.

Конец первой части. Читайте во второй части:

Источник

Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть четвертая

Предыдущие части

В данной части мы рассмотрим

Добавим немного новых данных

Для демонстрационных целей добавим несколько отделов и должностей:

JOIN-соединения – операции горизонтального соединения данных

Здесь нам очень пригодится знание структуры БД, т.е. какие в ней есть таблицы, какие данные хранятся в этих таблицах и по каким полям таблицы связаны между собой. Первым делом всегда досконально изучайте структуру БД, т.к. нормальный запрос можно написать только тогда, когда ты знаешь, что откуда берется. У нас структура состоит из 3-х таблиц Employees, Departments и Positions. Приведу здесь диаграмму из первой части:

Если суть РДБ – разделяй и властвуй, то суть операций объединений снова склеить разбитые по таблицам данные, т.е. привести их обратно в человеческий вид.

Если говорить просто, то операции горизонтального соединения таблицы с другими таблицами используются для того, чтобы получить из них недостающие данные. Вспомните пример с еженедельным отчетом для директора, когда при запросе из таблицы Employees, нам для получения окончательного результата недоставало поля «Название отдела», которое находится в таблице Departments.

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

Настало время вспомнить про псевдонимы таблиц

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

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

В нем поля с именами ID и Name есть в обоих таблицах и в Employees, и в Departments. И чтобы их различать, мы предваряем имя поля псевдонимом и точкой, т.е. «emp.ID», «emp.Name», «dep.ID», «dep.Name».

Вспоминаем почему удобнее пользоваться именно короткими псевдонимами – потому что, без псевдонимов наш запрос бы выглядел следующим образом:

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

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

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

Т.е. здесь одной таблице Employees, мы дали псевдоним «e1», а второй «e2».

Разбираем каждый вид горизонтального соединения

Для этой цели рассмотрим 2 небольшие абстрактные таблицы, которые так и назовем LeftTable и RightTable:

Посмотрим, что в этих таблицах:

Здесь были возвращены объединения строк для которых выполнилось условие (l.LCode=r.RCode)

LEFT JOIN

Здесь были возвращены все строки LeftTable, которые были дополнены данными строк из RightTable, для которых выполнилось условие (l.LCode=r.RCode)

RIGHT JOIN

Здесь были возвращены все строки RightTable, которые были дополнены данными строк из LeftTable, для которых выполнилось условие (l.LCode=r.RCode)

По сути если мы переставим LeftTable и RightTable местами, то аналогичный результат мы получим при помощи левого соединения:

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

FULL JOIN – это по сути одновременный LEFT JOIN + RIGHT JOIN

Вернулись все строки из LeftTable и RightTable. Строки для которых выполнилось условие (l.LCode=r.RCode) были объединены в одну строку. Отсутствующие в строке данные с левой или правой стороны заполняются NULL-значениями.

CROSS JOIN

Каждая строка LeftTable соединяется с данными всех строк RightTable.

Возвращаемся к таблицам Employees и Departments

Надеюсь вы поняли принцип работы горизонтальных соединений. Если это так, то возвратитесь на начало раздела «JOIN-соединения – операции горизонтального соединения данных» и попробуйте самостоятельно понять примеры с объединением таблиц Employees и Departments, а потом снова возвращайтесь сюда, обсудим это вместе.

Давайте попробуем вместе подвести резюме для каждого запроса:

Обратите внимание, что в случае повторения значений DepartmentID в таблице Employees, произошло соединение каждой такой строки со строкой из таблицы Departments с таким же ID, то есть данные Departments объединились со всеми записями для которых выполнилось условие (emp.DepartmentID=dep.ID):

В нашем случае все получилось правильно, т.е. мы дополнили таблицу Employees, данными таблицы Departments. Я специально заострил на этом внимание, т.к. бывают случаи, когда такое поведение нам не нужно. Для демонстрации поставим задачу – для каждого отдела вывести последнего принятого сотрудника, если сотрудников нет, то просто вывести название отдела. Возможно напрашивается такое решение – просто взять предыдущий запрос и поменять условие соединение на RIGHT JOIN, плюс переставить поля местами:

Но мы для ИТ-отдела получили три строчки, когда нам нужна была только строчка с последним принятым сотрудником, т.е. Николаевым Н.Н.

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

При помощи предварительного объединения Employees с данными подзапроса, мы смогли оставить только нужных нам для соединения с Departments сотрудников.

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

Посмотрите отдельно, что возвращает подзапрос:

Т.е. он вернул только идентификаторы последних принятых сотрудников, в разрезе отделов.

Соединения выполняются последовательно сверху-вниз, наращиваясь как снежный ком, который катится с горы. Сначала происходит соединение «Employees emp JOIN (Подзапрос) lastEmp», формируя новый выходной набор:

Потом идет объединение набора, полученного «Employees emp JOIN (Подзапрос) lastEmp» (назовем его условно «ПоследнийРезультат») с Departments, т.е. «ПоследнийРезультат RIGHT JOIN Departments dep»:

Самостоятельная работа для закрепления материала

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

Для закрепления материала про JOIN-соединения сделаем следующее:

Посмотрим, что в таблицах:

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

Еще раз про JOIN-соединения

Еще один пример с использованием нескольких последовательных операций соединении. Здесь повтор получился не специально, так получилось – не выбрасывать же материал. 😉 Но ничего «повторение – мать учения».

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

Первым делом выбрались все записи таблицы Employees:

Дальше произошло соединение с таблицей Departments:

Дальше уже идет соединение этого набора с таблицей Positions:

Т.е. это выглядит примерно так:

И в последнюю очередь идет возврат тех данных, которые мы просим вывести:

Соответственно, ко всему этому полученному набору можно применить фильтр WHERE и сортировку ORDER BY:

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

То есть если раньше в роли источника выступала только одна таблица, то теперь на это место мы просто подставляем наше выражение:

В результате чего получаем тот же самый базовый запрос:

А теперь, применим группировку:

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

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

Обещанный пример с CROSS JOIN

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

В данном случае сначала выполнилось соединение при помощи CROSS JOIN, а затем к полученному набору сделалось соединение с данными из подзапроса при помощи LEFT JOIN. Вместо таблицы в LEFT JOIN мы использовали подзапрос.

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

Вместе с псевдонимом «e» мы можем использовать имена DepartmentID, PositionID и EmplCount. По сути дальше подзапрос ведет себя так же, как если на его месте стояла таблица. Соответственно, как и у таблицы,
все имена колонок, которые возвращает подзапрос, должны быть заданы явно и не должны повторяться.

Связь при помощи WHERE-условия

Для примера перепишем следующий запрос с JOIN-соединением:

Через WHERE-условие он примет следующую форму:

Здесь плохо то, что происходит смешивание условий соединения таблиц (emp.DepartmentID=dep.ID) с условием фильтрации (emp.DepartmentID=3).

Теперь посмотрим, как сделать CROSS JOIN:

Через WHERE-условие он примет следующую форму:

Т.е. в этом случае мы просто не указали условие соединения таблиц Employees и Departments. Чем плох этот запрос? Представьте, что кто-то другой смотрит на ваш запрос и думает «кажется тот, кто писал запрос забыл здесь дописать условие (emp.DepartmentID=dep.ID)» и с радостью, что обнаружил косяк, дописывает это условие. В результате чего задуманное вами может сломаться, т.к. вы подразумевали CROSS JOIN. Так что, если вы делаете декартово соединение, то лучше явно укажите, что это именно оно, используя конструкцию CROSS JOIN.

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

UNION-объединения – операции вертикального объединения результатов запросов

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

Давайте первым делом вспомним как мы делали первую версию отчета для директора:

Так вот, если бы мы не знали, что существует операция группировки, но знали бы, что существует операция объединения результатов запроса при помощи UNION ALL, то мы могли бы склеить все эти запросы следующим образом:

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

Соответственно количество колонок в каждом запросе должно быть одинаковым, а также должны быть совместимыми и типы этих колонок, т.е. строка под строкой, число под числом, дата под датой и т.п.

Немного теории

В MS SQL реализованы следующие виды вертикального объединения:

Все это проще понять на наглядном примере.

Создадим 2 таблицы и наполним их данными:

Посмотрим на содержимое:

UNION ALL

UNION

По сути UNION можно представить, как UNION ALL, к которому применена операция DISTINCT:

Источник