Соединения в SQL: особенности и подводные камни

Введение

SQL-фрагмент

Предложение объединения (join clause) в языке структурированных запросов (SQL) объединяет столбцы из одной или нескольких таблиц в новую таблицу. Эта операция соответствует операции соединения в реляционной алгебре. Неформально, соединение объединяет две таблицы и помещает в одну строку записи с совпадающими полями: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS.

Внутреннее объединение и значения NULL

Программистам следует проявлять особую осторожность при объединении таблиц по столбцам, которые могут содержать значения NULL, поскольку NULL никогда не совпадает ни с каким другим значением (даже с самим NULL), если только условие объединения явно не использует составной предикат, который сначала проверяет, что столбцы объединения не равны NULL, прежде чем применять остальные условия предиката. Внутреннее соединение можно безопасно использовать только в базе данных, обеспечивающей ссылочную целостность, или когда гарантировано, что столбцы объединения не будут содержать NULL. Многие реляционные базы данных, предназначенные для обработки транзакций, опираются на стандарты обновления данных ACID (атомарность, согласованность, изолированность, долговечность), чтобы обеспечить целостность данных, что делает внутренние соединения подходящим выбором. Однако в базах данных транзакций часто встречаются желательные столбцы объединения, которым разрешено содержать NULL. Многие реляционные базы данных и хранилища данных используют пакетные обновления с большим объемом данных посредством процессов ETL (извлечение, преобразование, загрузка), что затрудняет или делает невозможным обеспечение ссылочной целостности, в результате чего столбцы объединения могут содержать NULL, которые автор SQL-запроса не может изменить и из-за которых внутренние соединения могут пропускать данные без индикации ошибки. Выбор использования внутреннего соединения зависит от структуры базы данных и характеристик данных. Левое внешнее соединение обычно может заменить внутреннее соединение, если столбцы объединения в одной из таблиц могут содержать значения NULL. Любой столбец данных, который может быть NULL (пустым), не следует использовать в качестве связующего звена во внутреннем соединении, если только не предполагается исключить строки, содержащие NULL-значения. Если NULL-столбцы объединения должны быть намеренно исключены из результирующего набора, внутреннее соединение может быть быстрее, чем внешнее, поскольку соединение таблицы и фильтрация выполняются за один шаг. И наоборот, внутреннее соединение может привести к катастрофически низкой производительности или даже к сбою сервера при использовании в запросе с большим объемом данных в сочетании с функциями базы данных в предложении SQL WHERE. Функция в предложении SQL WHERE может привести к тому, что база данных будет игнорировать относительно компактные индексы таблиц. База данных может прочитать и выполнить внутреннее соединение выбранных столбцов из обеих таблиц, прежде чем уменьшить количество строк с помощью фильтра, зависящего от вычисленного значения, что приведет к значительному объему неэффективной обработки. Когда результирующий набор формируется путем объединения нескольких таблиц, включая главные таблицы, используемые для поиска полных текстовых описаний числовых идентификаторов (таблица подстановки), значение NULL в любом из внешних ключей может привести к исключению всей строки из результирующего набора без индикации ошибки. Сложный SQL-запрос, включающий одно или несколько внутренних соединений и несколько внешних соединений, подвержен тому же риску, что и NULL-значения в столбцах внутренних соединений. Использование кода SQL, содержащего внутренние соединения, предполагает, что NULL-столбцы объединения не будут добавлены в будущем, включая обновления от поставщиков, изменения в структуре и массовую обработку за пределами правил проверки данных приложения, таких как преобразования данных, миграции, массовый импорт и слияния. Внутренние соединения можно классифицировать как соединения по равенству, как естественные соединения или как перекрестные соединения.

Programmers should take special care when joining tables on columns that can contain NULL values, since NULL will never match any other value (not even NULL itself), unless the join condition explicitly uses a combination predicate that first checks that the joins columns are NOT NULL before applying the remaining predicate condition(s). The Inner Join can only be safely used in a database that enforces referential integrity or where the join columns are guaranteed not to be NULL. Many transaction processing relational databases rely on atomicity, consistency, isolation, durability (ACID) data update standards to ensure data integrity, making inner joins an appropriate choice. However, transaction databases usually also have desirable join columns that are allowed to be NULL. Many reporting relational database and data warehouses use high volume extract, transform, load (ETL) batch updates which make referential integrity difficult or impossible to enforce, resulting in potentially NULL join columns that an SQL query author cannot modify and which cause inner joins to omit data with no indication of an error. The choice to use an inner join depends on the database design and data characteristics. A left outer join can usually be substituted for an inner join when the join columns in one table may contain NULL values. Any data column that may be NULL (empty) should never be used as a link in an inner join, unless the intended result is to eliminate the rows with the NULL value. If NULL join columns are to be deliberately removed from the result set, an inner join can be faster than an outer join because the table join and filtering is done in a single step. Conversely, an inner join can result in disastrously slow performance or even a server crash when used in a large volume query in combination with database functions in an SQL Where clause. A function in an SQL Where clause can result in the database ignoring relatively compact table indexes. The database may read and inner join the selected columns from both tables before reducing the number of rows using the filter that depends on a calculated value, resulting in a relatively enormous amount of inefficient processing. When a result set is produced by joining several tables, including master tables used to look up full text descriptions of numeric identifier codes (a Lookup table), a NULL value in any one of the foreign keys can result in the entire row being eliminated from the result set, with no indication of error. A complex SQL query that includes one or more inner joins and several outer joins has the same risk for NULL values in the inner join link columns. A commitment to SQL code containing inner joins assumes NULL join columns will not be introduced by future changes, including vendor updates, design changes and bulk processing outside of the application's data validation rules such as data conversions, migrations, bulk imports and merges. One can further classify inner joins as equi joins, as natural joins, or as cross joins.

Внешнее соединение

В результирующей таблице сохраняется каждая строка, даже если не существует других соответствующих строк. Внешние соединения подразделяются на левые внешние соединения, правые внешние соединения и полные внешние соединения, в зависимости от того, строки какой таблицы сохраняются: левые, правые или обе (в этом случае "левые" и "правые" относятся к сторонам ключевого слова JOIN). Как и внутренние соединения, все типы внешних соединений могут быть дополнительно классифицированы как эквисоединения, естественные соединения, ON <predicate> (θ-соединения) и т.д. В стандартном SQL не существует неявной записи для внешних соединений.

Самосоединяющийся

Самосоединение — это соединение таблицы с самой собой.

Алгоритмы присоединения

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

Объединить индексы

Индексы объединения — это индексы баз данных, которые облегчают обработку запросов объединения в хранилищах данных: на данный момент (2012 год) они доступны в реализациях Oracle и Teradata. В реализации Teradata указанные столбцы, агрегирующие функции над столбцами или компоненты столбцов даты из одной или нескольких таблиц задаются с использованием синтаксиса, аналогичного определению представления базы данных: в одном индексе объединения можно указать до 64 столбцов/выражений столбцов. Опционально может быть указан столбец, определяющий первичный ключ составных данных: на параллельном оборудовании значения этого столбца используются для разделения содержимого индекса по нескольким дискам. При интерактивном обновлении исходных таблиц пользователями содержимое индекса объединения автоматически обновляется. Любой запрос, в предложении WHERE которого указана любая комбинация столбцов или выражений столбцов, являющаяся точным подмножеством тех, что определены в индексе объединения (так называемый "покрывающий запрос"), будет использовать индекс объединения, а не исходные таблицы и их индексы, при выполнении запроса. Реализация Oracle ограничивается использованием битовых индексов. Битовый индекс объединения используется для столбцов с низкой кардинальностью (то есть столбцов, содержащих менее 300 различных значений, согласно документации Oracle): он объединяет столбцы с низкой кардинальностью из нескольких связанных таблиц. В качестве примера Oracle приводит систему учета запасов, где разные поставщики предоставляют разные детали. Схема состоит из трех связанных таблиц: две "главные таблицы" — Деталь и Поставщик, и "детальная таблица" — Запасы. Последняя представляет собой таблицу типа "многие ко многим", связывающую Поставщика и Деталь, и содержит наибольшее количество строк. Каждая деталь имеет Тип детали, а каждый поставщик базируется в США и имеет столбец Штат. В США не более 60 штатов и территорий, и не более 300 типов деталей. Битовый индекс объединения определяется с использованием стандартного трехтабличного соединения по указанным выше трем таблицам и указанием столбцов Тип детали и Штат поставщика для индекса. Однако он определен в таблице Запасы, хотя столбцы Тип детали и Штат поставщика "заимствованы" соответственно из таблиц Поставщик и Деталь. Что касается Teradata, то битовый индекс объединения Oracle используется только для ответа на запрос, если в предложении WHERE запроса указаны столбцы, ограниченные теми, которые включены в индекс объединения.

Join indexes are database indexes that facilitate the processing of join queries in data warehouses: they are currently (2012) available in implementations by Oracle and Teradata. In the Teradata implementation, specified columns, aggregate functions on columns, or components of date columns from one or more tables are specified using a syntax similar to the definition of a database view: up to 64 columns/column expressions can be specified in a single join index. Optionally, a column that defines the primary key of the composite data may also be specified: on parallel hardware, the column values are used to partition the index's contents across multiple disks. When the source tables are updated interactively by users, the contents of the join index are automatically updated. Any query whose WHERE clause specifies any combination of columns or column expressions that are an exact subset of those defined in a join index (a so called "covering query") will cause the join index, rather than the original tables and their indexes, to be consulted during query execution. The Oracle implementation limits itself to using bitmap indexes. A bitmap join index is used for low cardinality columns (i. e., columns containing fewer than 300 distinct values, according to the Oracle documentation): it combines low cardinality columns from multiple related tables. The example Oracle uses is that of an inventory system, where different suppliers provide different parts. The schema has three linked tables: two "master tables", Part and Supplier, and a "detail table", Inventory. The last is a many to many table linking Supplier to Part, and contains the most rows. Every part has a Part Type, and every supplier is based in the US, and has a State column. There are not more than 60 states+territories in the US, and not more than 300 Part Types. The bitmap join index is defined using a standard three table join on the three tables above, and specifying the Part Type and Supplier State columns for the index. However, it is defined on the Inventory table, even though the columns Part Type and Supplier State are "borrowed" from Supplier and Part respectively. As for Teradata, an Oracle bitmap join index is only utilized to answer a query when the query's WHERE clause specifies columns limited to those that are included in the join index.

Прямое соединение

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