Можно смело сказать, что операция объединения (JOIN) является наиболее мощной функциональной особенностью языка SQL. Эта операция — предмет зависти для всех нереляционных СУБД, поскольку ее концепция очень проста, но при этом широко применима в случаях, когда нужно объединить два набора данных.
Простыми словами, объединение двух таблиц заключается в объединении каждой строки первой таблицы с каждой строкой второй таблицы, для которых истинно значение некоторого предиката. Иллюстрация из мастер-класса по SQL демонстрирует эту концепцию:
Обратите также внимание на следующую статью, посвященную использованию диаграмм Венна (Venn diagram) для объяснения операции JOIN.
На рисунке выше представлена схема операции внутреннего объединения (INNER JOIN) в сравнении с различными операциями внешнего объединения (OUTER JOIN), но это далеко не все возможные варианты. Далее мы рассмотрим каждый из них в отдельности.
Обратите внимание, когда в данной статье мы говорим «X следует перед Y», имеется в виду, что «X логически следует перед Y». То есть, оптимизатор СУБД может выполнить Y раньше, чем X, в целях обеспечения более высокой производительности при неизменном результате. Подробнее о синтаксическом и логическом порядке операций вы моете прочитать в следующей статье.
Итак, давайте последовательно рассмотрим все типы объединений!
Перекрестное объединение (CROSS JOIN)
CROSS JOIN является базовым вариантом объединения и представляет собой декартово произведение (Cartesian product). Эта операция просто объединяет каждую строку первой таблицы с каждой строкой второй таблицы. Лучший пример, иллюстрирующий декартово произведение, представлен в Википедии. В этом примере мы получаем колоду карт, выполнив «перекрестное объединение» таблицы достоинств и таблицы мастей.
В реальных сценариях операция CROSS JOIN может быть очень полезна при создании отчетов. Например, мы можем сгенерировать набор дат (например, дни в месяце) (days) и выполнить перекрестное объединение со всеми отделами (departments), имеющимися в базе данных. В результате мы получим полную таблицу день/отдел. Используя синтаксис PostgreSQL:
SELECT * -- This just generates all the days in January 2017 FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) -- Here, we're combining all days with all departments CROSS JOIN departments
Представим себе, что мы имеем следующие данные:
+--------+ +------------+ | day | | department | +--------+ +------------+ | Jan 01 | | Dept 1 | | Jan 02 | | Dept 2 | | ... | | Dept 3 | | Jan 30 | +------------+ | Jan 31 | +--------+
Результат операции CROSS JOIN будет выглядеть следующим образом:
+--------+------------+ | day | department | +--------+------------+ | Jan 01 | Dept 1 | | Jan 01 | Dept 2 | | Jan 01 | Dept 3 | | Jan 02 | Dept 1 | | Jan 02 | Dept 2 | | Jan 02 | Dept 3 | | ... | ... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | +--------+------------+
Теперь для каждой комбинации день/отдел мы можем вычислить дневную выручку для данного отдела или другие аналогичные показатели.
Свойства
Как мы уже сказали, операция CROSS JOIN представляет собой декартово произведение. Соответственно, в математической нотации для описания данной операции используется знак умножения: A × B, или в нашем случае days × departments.
Как и в случае «обычного» арифметического умножения, если одна из двух таблиц пустая (имеет нулевой размер), результат также будет пустым. Это абсолютно логично. Если мы объединим 31 день и 0 отделов, мы получим 0 комбинаций день/отдел. Аналогично, если мы объединим пустой диапазон дат с любым количеством отделов, мы также получим 0 комбинаций день/отдел. Другими словами:
size(result) = size(days) * size(departments)
Альтернативный синтаксис
До того, как синтаксис операции JOIN был стандартизирован ANSI, чтобы реализовать CROSS JOIN, программисты просто использовали список разделенных запятыми таблиц в предложении FROM. Рассмотренный выше запрос эквивалентен следующему:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day), departments
В общем случае для выполнения перекрестного объединения настоятельно рекомендуется использовать ключевые слова CROSS JOIN вместо альтернативного синтаксиса. Благодаря этому, другой программист сможет легко понять назначение данного фрагмента кода! Кроме того, использование альтернативного синтаксиса на основе списка разделенных запятыми таблиц чревато появлением ошибок, например, может произойти ненамеренное перекрестное объединение. Нам же не нужны такие проблемы!
Внутреннее объединение (INNER JOIN) или тета-объединение (THETA JOIN)
Развивая идею предыдущей операции CROSS JOIN, операция INNER JOIN (или просто JOIN, иногда также THETA JOIN) позволяет выполнять фильтрацию результата декартова произведения на основе некоторого предиката. Как правило, мы помещаем этот предикат в предложение ON. Таким образом, запрос принимает следующий вид:
SELECT * -- Same as before FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) -- Now, exclude all days/departments combinations for -- days before the department was created JOIN departments AS d ON day >= d.created_at
В большинстве СУБД ключевое слово INNER является необязательным, поэтому мы просто не указываем его.
Операция INNER JOIN позволяет нам использовать произвольные предикаты в предложении ON, что опять же очень удобно при создании отчетов. Аналогично CROSS JOIN мы объединяем все дни со всеми отделами, но потом оставляем только те комбинации день/отдел, для которых данный отдел уже существовал в данный день.
Используем те же исходные данные:
+--------+ +------------+------------+ | day | | department | created_at | +--------+ +------------+------------+ | Jan 01 | | Dept 1 | Jan 10 | | Jan 02 | | Dept 2 | Jan 11 | | ... | | Dept 3 | Jan 12 | | Jan 30 | +------------+------------+ | Jan 31 | +--------+
Получим следующий результат:
+--------+------------+ | day | department | +--------+------------+ | Jan 10 | Dept 1 | | Jan 11 | Dept 1 | | Jan 11 | Dept 2 | | Jan 12 | Dept 1 | | Jan 12 | Dept 2 | | Jan 12 | Dept 3 | | Jan 13 | Dept 1 | | Jan 13 | Dept 2 | | Jan 13 | Dept 3 | | ... | ... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | +--------+------------+
Результат операции содержит данные, начиная с 10 января. Более ранние даты были отфильтрованы.
Свойства
Операция INNER JOIN представляет собой операцию CROSS JOIN с фильтрацией. Это означает, что если одна из таблиц пустая, то результат также гарантированно будет пустым. По причине наличия предиката, результат операции INNER JOIN может быть меньшего объема, чем результат операции CROSS JOIN. Другими словами:
size(result) <= size(days) * size(departments)
Альтернативный синтаксис
Несмотря на то, что предложение ON является обязательным для операции INNER JOIN, мы не обязаны указывать в нем предикат (хотя это крайне желательно в целях улучшения читаемости). Рассмотренный выше запрос эквивалентен следующему:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) -- You can always JOIN .. ON true (or 1 = 1 in other DBs) -- to turn an syntactic INNER JOIN into a semantic CROSS JOIN JOIN departments AS d ON true -- ... and then turn the CROSS JOIN back into an INNER JOIN -- by putting the JOIN predicate in the WHERE clause: WHERE day >= d.created_at
Безусловно, это просто запутывание кода, но ведь у нас могут быть свои причины, не так ли? Сделав еще один шаг, мы можем написать следующий запрос, который также является эквивалентным, поскольку большинство оптимизаторов способны распознать равнозначность и выполнить INNER JOIN:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) -- Now, this is really a syntactic CROSS JOIN CROSS JOIN departments AS d WHERE day >= d.created_at
Как мы уже говорили, CROSS JOIN это лишь удобный синтаксис для списка разделенных запятыми таблиц. Во фрагменте кода, представленном ниже, мы также используем предложение WHERE, чтобы сформировать запрос, которым программисты часто пользовались до того, как синтаксис JOIN был стандартизирован:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day), departments AS d WHERE day >= d.created_at
Все эти варианты альтернативного синтаксиса выполняют одну и ту же задачу, как правило, без потери производительности. Однако очевидно, что все они значительно хуже читаются по сравнению со стандартным синтаксисом INNER JOIN.
Объединение на основе равенства (EQUI JOIN)
Иногда в литературе встречается термин EQUI JOIN. На самом деле, «EQUI» не является ключевым словом SQL, а просто обозначает специальный вариант записи особого случая операции INNER JOIN.
Следует отметить, что не совсем правомерно называть EQUI JOIN особым случаем, поскольку эту операцию мы выполняем чаще всего в SQL и OLTP приложениях, когда просто объединяем таблицы на основе отношения первичного/внешнего ключа. Например:
SELECT * FROM actor AS a JOIN film_actor AS fa ON a.actor_id = fa.actor_id JOIN film AS f ON f.film_id = fa.film_id
Представленный выше запрос извлекает всех актеров и фильмы, в которых они снимались. В нем присутствуют две операции INNER JOIN. Первая из них объединяет таблицу актеров actor и соответствующие записи из таблицы film_actor, содержащей информацию об отношениях фильм/актер (поскольку каждый актер может играть во множестве фильмов, а в каждом фильме может играть множество актеров). Вторая операция INNER JOIN выполняет объединение с таблицей film, содержащей информацию о фильмах.
Свойства
Данная операция имеет те же свойства, что и «обычная» операция INNER JOIN. То есть EQUI JOIN также является декартовым произведением (CROSS JOIN) с отфильтрованным результатом. В частности, в нашем случае результат содержит только те комбинации актер/фильм, для которых данный актер действительно играл в данном фильме. Таким образом, мы снова имеем соотношение:
size(result) <= size(actor) * size(film)
Объем результата может быть равен полному декартову произведению таблиц actor и film только в том случае, если каждый актер играл в каждом фильме, что маловероятно.
Альтернативный синтаксис: USING
Опять же, мы могли бы записать операцию EQUI JOIN, используя CROSS JOIN или список разделенных запятыми таблиц, но это уже не интересно. Значительно больший интерес представляют два варианта альтернативного синтаксиса, представленные ниже, один из которых является очень полезным.
SELECT * FROM actor JOIN film_actor USING (actor_id) JOIN film USING (film_id)
Предложение USING заменяет предложение ON и позволяет указать набор столбцов, которые должны присутствовать в обеих объединяемых таблицах. Если наша база данных была хорошо спроектирована (как, например, база данных Sakila), то есть, если каждый внешний ключ имеет такое же имя, как и соответствующий первичный ключ (например, actor.actor_id = film_actor.actor_id), тогда мы можем использовать предложение USING для реализации операции EQUI JOIN, как минимум, в следующих СУБД:
- Derby
- Firebird
- HSQLDB
- Ingres
- MariaDB
- MySQL
- Oracle
- PostgreSQL
- SQLite
- Vertica
Следующие СУБД, к сожалению, не поддерживают данный синтаксис:
- Access
- Cubrid
- DB2
- H2
- HANA
- Informix
- SQL Server
- Sybase ASE
- Sybase SQL Anywhere
Запрос с предложением USING (почти) идентичен запросу с предложением ON, однако значительно более удобен для написания и восприятия. Мы сказали «почти», потому что согласно спецификации некоторых СУБД (и стандарту SQL) столбец, используемый в предложении USING, не должен иметь квалификатор. Например:
SELECT f.title, -- Ordinary column, can be qualified f.film_id, -- USING column, shouldn't be qualified film_id -- USING column, correct / non-ambiguous here FROM actor AS a JOIN film_actor AS fa USING (actor_id) JOIN film AS f USING (film_id)
Безусловно, этот синтаксис также имеет свои ограничения. Иногда в таблице может быть внешний ключ, имя которого не соответствует первичному ключу. Например:
CREATE TABLE film ( .. language_id BIGINT REFERENCES language, original_language_id BIGINT REFERENCES language, )
Если мы хотим выполнить объединение по original_language_id, нам придется использовать предложение ON.
Альтернативный синтаксис: Естественное объединение (NATURAL JOIN)
Более экстремальным и значительно менее полезным вариантом синтаксиса операции EQUI JOIN является синтаксис на основе предложения NATURAL JOIN. Рассмотренный выше синтаксис на основе USING можно «улучшить», заменив USING на NATURAL JOIN следующим образом:
SELECT * FROM actor NATURAL JOIN film_actor NATURAL JOIN film
Обратите внимание, в этом запросе нет необходимости указывать какие-либо критерии объединения, поскольку предложение NATURAL JOIN автоматически определяет столбцы, имеющие одинаковые имена в обеих объединяемых таблица, и помещает их в «скрытое» предложение USING. Если первичные и внешние ключи имеют одинаковые имена, этот подход может показаться полезным, однако это не так.
В базе данных Sakila, каждая таблица имеет столбец last_update, который автоматически используется предложением NATURAL JOIN. Таким образом, запрос NATURAL JOIN эквивалентен следующему запросу, который, конечно же, не имеет никакого смысла:
SELECT * FROM actor JOIN film_actor USING (actor_id, last_update) JOIN film USING (film_id, last_update)
Итак, сразу же забудьте о NATURAL JOIN и никогда не используйте этот вариант (за исключением очень редких случаев, таких как объединение диагностических представлений Oracle, например, v$sql NATURAL JOIN v$sql_plan, в целях специализированной аналитики).
Внешнее объединение (OUTER JOIN)
Мы рассмотрели операцию INNER JOIN, возвращающую только те комбинации строк левой/правой таблицы, для которых значение предиката в предложении ON является истинным.
Операция OUTER JOIN позволяет нам включить в результат строки одной таблицы, для которых не были найдены соответствующие строки в другой таблице.
Левое внешнее объединение (LEFT OUTER JOIN)
Давайте вернемся к примеру с датами и отделами:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) LEFT JOIN departments AS d ON day >= d.created_at
Ключевое слово «OUTER» является необязательным, поэтому мы его не указываем.
Этот запрос отличается от подобного запроса INNER JOIN лишь тем, что всегда будет возвращать хотя бы одну строку для каждого дня, даже если в данный день еще не существовало ни одного отдела. В частности, в нашем примере все отделы были созданы не ранее 10 января, но запрос все равно вернет строки, соответствующие 1–9 января.
+--------+ +------------+------------+ | day | | department | created_at | +--------+ +------------+------------+ | Jan 01 | | Dept 1 | Jan 10 | | Jan 02 | | Dept 2 | Jan 11 | | ... | | Dept 3 | Jan 12 | | Jan 30 | +------------+------------+ | Jan 31 | +--------+
Кроме строк, которые мы получили бы с помощью запроса INNER JOIN, в результате запроса LEFT OUTER JOIN также присутствуют строки, соответствующие 1–9 января, с пустыми (NULL) значениями отделов:
+--------+------------+ | day | department | +--------+------------+ | Jan 01 | | -- Extra rows with no match here | Jan 02 | | -- Extra rows with no match here | ... | | -- Extra rows with no match here | Jan 09 | | -- Extra rows with no match here | Jan 10 | Dept 1 | | Jan 11 | Dept 1 | | Jan 11 | Dept 2 | | Jan 12 | Dept 1 | | Jan 12 | Dept 2 | | Jan 12 | Dept 3 | | Jan 13 | Dept 1 | | Jan 13 | Dept 2 | | Jan 13 | Dept 3 | | ... | ... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | +--------+------------+
Как видите, каждый день хотя бы один раз присутствует в результате запроса. LEFT OUTER JOIN выполняет данную операцию для левой таблицы, то есть возвращает все строки левой таблицы.
Формально, операцию LEFT OUTER JOIN можно выразить операцией INNER JOIN с предложением UNION:
-- Convenient syntax: SELECT * FROM a LEFT JOIN b ON-- Cumbersome, equivalent syntax: SELECT a.*, b.* FROM a JOIN b ON UNION ALL SELECT a.*, NULL, NULL, ..., NULL FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE )
Мы обсудим NOT EXISTS далее в этой статье, когда будем рассматривать операцию SEMI JOIN.
Правое внешнее объединение (RIGHT OUTER JOIN)
Операция RIGHT OUTER JOIN выполняет ту же задачу, что и LEFT OUTER JOIN, но для правой таблицы, то есть возвращает в результате все строки правой таблицы. Немного модифицируем наши данные, добавив пару отделов:
+--------+ +------------+------------+ | day | | department | created_at | +--------+ +------------+------------+ | Jan 01 | | Dept 1 | Jan 10 | | Jan 02 | | Dept 2 | Jan 11 | | ... | | Dept 3 | Jan 12 | | Jan 30 | | Dept 4 | Apr 01 | | Jan 31 | | Dept 5 | Apr 02 | +--------+ +------------+------------+
Новые отделы 4 и 5 не попали бы в результат запроса INNER JOIN, поскольку были созданы после 31 января. Однако эти отделы появятся в результате запроса RIGHT OUTER JOIN, поскольку эта операция возвращает все строки правой таблицы.
Выполним следующий запрос:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) RIGHT JOIN departments AS d ON day >= d.created_at
Получим следующий результат:
+--------+------------+ | day | department | +--------+------------+ | Jan 10 | Dept 1 | | Jan 11 | Dept 1 | | Jan 11 | Dept 2 | | Jan 12 | Dept 1 | | Jan 12 | Dept 2 | | Jan 12 | Dept 3 | | Jan 13 | Dept 1 | | Jan 13 | Dept 2 | | Jan 13 | Dept 3 | | ... | ... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | | | Dept 4 | -- Extra rows with no match here | | Dept 5 | -- Extra rows with no match here +--------+------------+
В большинстве случаев (я еще не сталкивался с ситуацией, для которой это утверждение не верно), выражение LEFT OUTER JOIN можно преобразовать в эквивалентное выражение RIGHT OUTER JOIN, и наоборот. Поскольку RIGHT OUTER JOIN обеспечивает меньшее удобство восприятия, большинство программистов используют только LEFT OUTER JOIN.
Полное внешнее объединение (FULL OUTER JOIN)
Существует также операция FULL OUTER JOIN, которая возвращает в результате все строки как левой, так и правой таблицы. Для нашего примера это означает, что каждый день и каждый отдел хотя бы один раз появляются в результате запроса.
Используем те же данные:
+--------+ +------------+------------+ | day | | department | created_at | +--------+ +------------+------------+ | Jan 01 | | Dept 1 | Jan 10 | | Jan 02 | | Dept 2 | Jan 11 | | ... | | Dept 3 | Jan 12 | | Jan 30 | | Dept 4 | Apr 01 | | Jan 31 | | Dept 5 | Apr 02 | +--------+ +------------+------------+
Выполним следующий запрос:
SELECT * FROM generate_series( '2017-01-01'::TIMESTAMP, '2017-01-01'::TIMESTAMP + INTERVAL '1 month -1 day', INTERVAL '1 day' ) AS days(day) FULL JOIN departments AS d ON day >= d.created_at
Получим следующий результат:
+--------+------------+ | day | department | +--------+------------+ | Jan 01 | | -- row from the left table | Jan 02 | | -- row from the left table | ... | | -- row from the left table | Jan 09 | | -- row from the left table | Jan 10 | Dept 1 | | Jan 11 | Dept 1 | | Jan 11 | Dept 2 | | Jan 12 | Dept 1 | | Jan 12 | Dept 2 | | Jan 12 | Dept 3 | | Jan 13 | Dept 1 | | Jan 13 | Dept 2 | | Jan 13 | Dept 3 | | ... | ... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | | | Dept 4 | -- row from the right table | | Dept 5 | -- row from the right table +--------+------------+
Формально, операцию FULL OUTER JOIN можно выразить операцией INNER JOIN с предложением UNION:
-- Convenient syntax: SELECT * FROM a FULL JOIN b ON-- Cumbersome, equivalent syntax: SELECT a.*, b.* FROM a JOIN b ON -- LEFT JOIN part UNION ALL SELECT a.*, NULL, NULL, ..., NULL FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE ) -- RIGHT JOIN part UNION ALL SELECT NULL, NULL, ..., NULL, b.* FROM b WHERE NOT EXISTS ( SELECT * FROM a WHERE )
Альтернативный синтаксис: Внешнее объединение на основе равенства (EQUI OUTER JOIN)
Рассмотренные выше операции опять же представляют собой объединения типа «декартово произведение с фильтрацией». Однако более распространенным является подход EQUI OUTER JOIN, в рамках которого мы выполняем объединение на основе отношения первичного/внешнего ключа. Используем для примера базу данных Sakila. Некоторые актеры не снялись ни в одном фильме. Мы можем извлечь их следующим образом:
SELECT * FROM actor LEFT JOIN film_actor USING (actor_id) LEFT JOIN film USING (film_id)
В результате этого запроса каждый актер будет присутствовать хотя бы один раз, независимо от того, принимал ли он участие в каком-либо фильме. Если мы также хотим извлечь все фильмы, в которых не снимался ни один из данных актеров, мы можем применить FULL OUTER JOIN:
SELECT * FROM actor FULL JOIN film_actor USING (actor_id) FULL JOIN film USING (film_id)
Безусловно, в качестве альтернативы можно было бы использовать NATURAL LEFT JOIN, NATURAL RIGHT JOIN, NATURAL FULL JOIN, но, как мы уже говорили ранее, в таком случае в объединении автоматически был бы учтен столбец last_update, присутствующий во всех таблицах базы данных Sakila (т.е. USING (…, last_update)), что лишает операцию всякого смысла.
Альтернативный синтаксис: Внешнее объединение (OUTER JOIN) в стиле Oracle и SQL Server
До введения стандартного синтаксиса СУБД Oracle и SQL Server поддерживали операцию внешнего объединения в следующем виде:
-- Oracle SELECT * FROM actor a, film_actor fa, film f WHERE a.actor_id = fa.actor_id(+) AND fa.film_id = f.film_id(+) -- SQL Server SELECT * FROM actor a, film_actor fa, film f WHERE a.actor_id *= fa.actor_id AND fa.film_id *= f.film_id
Можно смело сказать, что этот синтаксис является устаревшим.
Разработчики SQL Server поступили правильно, вначале объявив этот синтаксис нежелательным, и в дальнейшем отказавшись от него. Oracle по-прежнему поддерживает его для обратной совместимости.
Нет никаких аргументов в пользу данного альтернативного синтаксиса. Используйте вместо него стандартный синтаксис ANSI.
Внешнее объединение с разделением (PARTITIONED OUTER JOIN)
Эта операция поддерживается только Oracle. На самом деле, просто удивительно, что другие СУБД до сих пор не реализовали ее. Помните операцию CROSS JOIN, которую мы использовали, чтобы получить все комбинации день/отдел? Так вот, иногда мы хотим получить следующий результат: все комбинации, а также, если выполняется условие, поместить в данную строку соответствующее значение.
Эту операцию трудно объяснить словами. Намного легче сделать это на примере. Ниже представлен запрос, использующий синтаксис Oracle:
WITH -- Using CONNECT BY to generate all dates in January days(day) AS ( SELECT DATE '2017-01-01' + LEVEL - 1 FROM dual CONNECT BY LEVEL <= 31 ), -- Our departments departments(department, created_at) AS ( SELECT 'Dept 1', DATE '2017-01-10' FROM dual UNION ALL SELECT 'Dept 2', DATE '2017-01-11' FROM dual UNION ALL SELECT 'Dept 3', DATE '2017-01-12' FROM dual UNION ALL SELECT 'Dept 4', DATE '2017-04-01' FROM dual UNION ALL SELECT 'Dept 5', DATE '2017-04-02' FROM dual ) SELECT * FROM days LEFT JOIN departments PARTITION BY (department) -- This is where the magic happens ON day >= created_at
Предложение PARTITION BY используется в различных контекстах для решения различных задач (например, для реализации оконных функций (window function)). В нашем случае PARTITION BY означает, что мы «разделяем» наши данные по значениям столбца departments.department, создавая таким образом «подгруппу» для каждого отдела. Затем каждая «подгруппа» получает копию всех дней, независимо от того, выполняется ли условие предиката (в отличие от обычной операции LEFT OUTER JOIN, в результате которой, часть дней имели пустые значения отделов). Представленный выше запрос даст следующий результат:
+--------+------------+------------+ | day | department | created_at | +--------+------------+------------+ | Jan 01 | Dept 1 | | -- Didn't match, but still get row | Jan 02 | Dept 1 | | -- Didn't match, but still get row | ... | Dept 1 | | -- Didn't match, but still get row | Jan 09 | Dept 1 | | -- Didn't match, but still get row | Jan 10 | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 11 | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 12 | Dept 1 | Jan 10 | -- Matches, so get join result | ... | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 31 | Dept 1 | Jan 10 | -- Matches, so get join result | Jan 01 | Dept 2 | | -- Didn't match, but still get row | Jan 02 | Dept 2 | | -- Didn't match, but still get row | ... | Dept 2 | | -- Didn't match, but still get row | Jan 09 | Dept 2 | | -- Didn't match, but still get row | Jan 10 | Dept 2 | | -- Didn't match, but still get row | Jan 11 | Dept 2 | Jan 11 | -- Matches, so get join result | Jan 12 | Dept 2 | Jan 11 | -- Matches, so get join result | ... | Dept 2 | Jan 11 | -- Matches, so get join result | Jan 31 | Dept 2 | Jan 11 | -- Matches, so get join result | Jan 01 | Dept 3 | | -- Didn't match, but still get row | Jan 02 | Dept 3 | | -- Didn't match, but still get row | ... | Dept 3 | | -- Didn't match, but still get row | Jan 09 | Dept 3 | | -- Didn't match, but still get row | Jan 10 | Dept 3 | | -- Didn't match, but still get row | Jan 11 | Dept 3 | | -- Didn't match, but still get row | Jan 12 | Dept 3 | Jan 12 | -- Matches, so get join result | ... | Dept 3 | Jan 12 | -- Matches, so get join result | Jan 31 | Dept 3 | Jan 12 | -- Matches, so get join result | Jan 01 | Dept 4 | | -- Didn't match, but still get row | Jan 02 | Dept 4 | | -- Didn't match, but still get row | ... | Dept 4 | | -- Didn't match, but still get row | Jan 31 | Dept 4 | | -- Didn't match, but still get row | Jan 01 | Dept 5 | | -- Didn't match, but still get row | Jan 02 | Dept 5 | | -- Didn't match, but still get row | ... | Dept 5 | | -- Didn't match, but still get row | Jan 31 | Dept 5 | | -- Didn't match, but still get row +--------+------------+------------+
Как видите, мы имеем 5 «подгрупп», соответствующих 5 отделам. Каждая «подгруппа» объединяет данный отдел с каждым днем, но в отличие от CROSS JOIN, мы получаем результат LEFT OUTER JOIN .. ON .. в том случае, когда выполняется условие предиката. Это действительно полезная функциональность для создания отчетов в Oracle!
You have to think anyway, so why not think big? https://helloworld.com
Спасибо за подробную статью с примерами SQL-операций, просто объясняет все особенности запросов.
ewfw
Good article.
600 руб. в месяц; Абсолютный безлимит; Бесплатные входящие вызовы; Лимит на исходящие вызовы— 500 мин. Для выгодных звонков на городские телефоны подключите один из пакетов в разделе «Для звонков на городские номера»