Поделиться через

Предложение FROM и JOIN, APPLY, PIVOT (Transact-SQL)

  • Статья

Область применения: SQL Server 2016 (13.x) и более поздние версии База данных SQL Azure Управляемый экземпляр SQL Azure конечную точку аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в базе данных Microsoft Fabric SQL в Microsoft Fabric

В Transact-SQL предложение FROM доступно для следующих инструкций:

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

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для базы данных SQL Server, База данных SQL Azure и Fabric SQL:

[ FROM { <table_source> } [ , ...n ] ]
<table_source> ::=
{
    table_or_view_name [ FOR SYSTEM_TIME <system_time> ] [ [ AS ] table_alias ]
        [ <tablesample_clause> ]
        [ WITH ( < table_hint > [ [ , ] ...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
        [ ( bulk_column_alias [ , ...n ] ) ]
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML <openxml_clause>
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ , ...n ] ) ]
    | <joined_table>
    | <pivoted_table>
    | <unpivoted_table>
    | @variable [ [ AS ] table_alias ]
    | @variable.function_call ( expression [ , ...n ] )
        [ [ AS ] table_alias ] [ (column_alias [ , ...n ] ) ]
}
<tablesample_clause> ::=
    TABLESAMPLE [ SYSTEM ] ( sample_number [ PERCENT | ROWS ] )
        [ REPEATABLE ( repeat_seed ) ]

<joined_table> ::=
{
    <table_source> <join_type> <table_source> ON <search_condition>
    | <table_source> CROSS JOIN <table_source>
    | left_table_source { CROSS | OUTER } APPLY right_table_source
    | [ ( ] <joined_table> [ ) ]
}
<join_type> ::=
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

<pivoted_table> ::=
    table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]

<pivot_clause> ::=
        ( aggregate_function ( value_column [ [ , ] ...n ] )
        FOR pivot_column
        IN ( <column_list> )
    )

<unpivoted_table> ::=
    table_source UNPIVOT <unpivot_clause> [ [ AS ] table_alias ]

<unpivot_clause> ::=
    ( value_column FOR pivot_column IN ( <column_list> ) )

<column_list> ::=
    column_name [ , ...n ]

<system_time> ::=
{
      AS OF <date_time>
    | FROM <start_date_time> TO <end_date_time>
    | BETWEEN <start_date_time> AND <end_date_time>
    | CONTAINED IN (<start_date_time> , <end_date_time>)
    | ALL
}

    <date_time>::=
        <date_time_literal> | @date_time_variable

    <start_date_time>::=
        <date_time_literal> | @date_time_variable

    <end_date_time>::=
        <date_time_literal> | @date_time_variable

Синтаксис для параллельного хранилища данных Azure Synapse Analytics:

FROM { <table_source> [ , ...n ] }

<table_source> ::=
{
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias
    [ <tablesample_clause> ]
    | derived_table [ AS ] table_alias [ ( column_alias [ , ...n ] ) ]
    | <joined_table>
}

<tablesample_clause> ::=
    TABLESAMPLE ( sample_number [ PERCENT ] ) -- Azure Synapse Analytics Dedicated SQL pool only

<joined_table> ::=
{
    <table_source> <join_type> <table_source> ON search_condition
    | <table_source> CROSS JOIN <table_source>
    | left_table_source { CROSS | OUTER } APPLY right_table_source
    | [ ( ] <joined_table> [ ) ]
}

<join_type> ::=
    [ INNER ] [ <join hint> ] JOIN
    | LEFT  [ OUTER ] JOIN
    | RIGHT [ OUTER ] JOIN
    | FULL  [ OUTER ] JOIN

<join_hint> ::=
    REDUCE
    | REPLICATE
    | REDISTRIBUTE

Синтаксис Для Microsoft Fabric:

FROM { <table_source> [ , ...n ] }

<table_source> ::=
{
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias
    | derived_table [ AS ] table_alias [ ( column_alias [ , ...n ] ) ]
    | <joined_table>
}

<joined_table> ::=
{
    <table_source> <join_type> <table_source> ON search_condition
    | <table_source> CROSS JOIN <table_source>
    | left_table_source { CROSS | OUTER } APPLY right_table_source
    | [ ( ] <joined_table> [ ) ]
}

<join_type> ::=
    [ INNER ] [ <join hint> ] JOIN
    | LEFT  [ OUTER ] JOIN
    | RIGHT [ OUTER ] JOIN
    | FULL  [ OUTER ] JOIN

<join_hint> ::=
    REDUCE
    | REPLICATE
    | REDISTRIBUTE

Аргументы

<table_source>

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

Примечание.

Производительность выполнения запросов может снизиться из-за большого количества таблиц, указанных в запросе. На время компиляции и оптимизации также влияют дополнительные факторы. Они включают в себя наличие индексов и индексированных представлений в каждом <table_source> и размер <select_list> в инструкции SELECT.

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

table_or_view_name

Имя таблицы или представления.

Если таблица или представление существуют в другой базе данных в том же экземпляре SQL Server, используйте полное имя в базе данных формы.схема.object_name.

Если таблица или представление существуют вне экземпляра SQL Serverl, используйте четырехкомпонентное имя в форме linked_server.каталог.схема.объект. Дополнительные сведения см. в статье sp_addlinkedserver (Transact-SQL). Для указания удаленного источника таблицы также можно использовать четырехкомпонентное имя, где в качестве компонента сервера используется функция OPENDATASOURCE. Если указана функция OPENDATASOURCE, то аргументы database_name и schema_name могут применяться не ко всем источникам данных, а в зависимости от возможностей поставщика OLE DB, который обращается к удаленному объекту.

[AS] table_alias

Псевдоним для table_source, который можно использовать либо для удобства, либо для отличия таблицы или представления в самосоединяющемся или вложенном запросе. Псевдоним часто является сокращенным именем таблицы, использующимся для соотнесения с определенными столбцами таблиц в соединении. Если одно и то же имя столбца существует в нескольких таблицах в соединении, SQL Server может потребовать, чтобы имя столбца было квалифицировано по имени таблицы, имени представления или псевдониму, чтобы отличить эти столбцы. Имя таблицы нельзя использовать, если определен псевдоним.

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

WITH (<table_hint> )

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

rowset_function

Применимо к: SQL Server и База данных SQL.

Задает одну из функций набора строк, например OPENROWSET, которая возвращает объект, который можно использовать вместо ссылки на таблицу. Дополнительные сведения о списке функций набора строк см. в разделе Функции наборов строк (Transact-SQL).

Использование функций OPENROWSET и OPENQUERY для задания удаленного объекта зависит от возможностей поставщика OLE DB, который обращается к удаленному объекту.

bulk_column_alias

Применимо к: SQL Server и База данных SQL.

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

Примечание.

Данный псевдоним, если он присутствует, переопределяет атрибут NAME в элементах COLUMN файла XML.

user_defined_function

Указывает функцию с табличным значением.

OPENXML <openxml_clause>

Применимо к: SQL Server и База данных SQL.

Обеспечивает представление XML-документа в виде набора строк. Дополнительные сведения см в разделе OPENXML (Transact-SQL).

derived_table

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

Чтобы указать несколько строк в параметре derived_table, можно воспользоваться конструктором табличных значений Transact-SQL. Например, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. Дополнительные сведения см. в разделе Конструктор табличных значений (Transact-SQL).

column_alias

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

table_or_view_name FOR SYSTEM_TIME <system_time>

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL.

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

Предложение TABLESAMPLE

Область применения: SQL Server, База данных SQL и выделенные пулы SQL Azure Synapse Analytics

Указывает, что из таблицы возвращается выборка данных. Выборка может быть приблизительной. Это предложение может использоваться в инструкциях SELECT или UPDATE в отношении любой первичной или присоединенной таблицы. ТАБЛИМПЛ не удается указать с представлениями.

Примечание.

При использовании TABLEAMPLE для баз данных, обновляемых до SQL Server, уровень совместимости базы данных имеет значение 110 или выше, PIVOT не допускается в рекурсивном запросе общего табличного выражения (CTE). Дополнительные сведения см. в разделе Уровень совместимости ALTER DATABASE (Transact-SQL).

SYSTEM

Метод выборки, зависящий от реализации, указанный стандартами ISO. В SQL Server это единственный доступный метод выборки и применяется по умолчанию. SYSTEM использует основанный на страницах метод выборки со случайным набором страниц, все строки которых возвращаются как подмножество выборки.

sample_number

Точное или приблизительное числовое выражение константы, представляющее процент или количество строк. При указании PERCENT аргумент sample_number неявно преобразуется в тип float; в противном случае он преобразуется в тип bigint. PERCENT является параметром по умолчанию.

ПРОЦЕНТ

Указывает, что из таблицы должен быть извлечен процент строк таблицы, равный значению аргумента sample_number. При указании PERCENT SQL Server возвращает приблизительно указанное значение процента. При указании PERCENT выражение sample_number должно иметь значение от 0 до 100.

СТРОКИ

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

REPEATABLE

Указывает, что заданная выборка может быть возвращена снова. При указании с тем же значением repeat_seed SQL Server возвращает то же подмножество строк, пока никаких изменений не было внесены в строки таблицы. При указании с другим значением repeat_seed SQL Server, скорее всего, возвращает несколько разных примеров строк в таблице. Изменениями считаются следующие действия над таблицей: вставка, обновление, удаление, перестроение или дефрагментация индекса, а также восстановление или присоединение базы данных.

repeat_seed

Целочисленное выражение константы, используемое SQL Server для создания случайного числа. repeat_seed имеет тип bigint. Если repeat_seed не указан, SQL Server назначает значение случайным образом. Для определенного значения аргумента repeat_seed результат выборки всегда тот же, если в таблице не было произведено никаких изменений. Результат выражения repeat_seed должен быть целочисленным значением больше нуля.

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

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

Тип соединения

Указание типа операции соединения.

INNER

Указывает, что возвращаются все совпадающие пары строк. Отмена несовпадающих строк из обеих таблиц. Если тип соединения не указан, этот тип задается по умолчанию.

FULL [ OUTER ]

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

LEFT [ OUTER ]

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

RIGHT [ OUTER ]

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

Указание соединения

Для SQL Server и База данных SQL указывает, что оптимизатор запросов SQL Server использует одно указание соединения или алгоритм выполнения для каждого соединения, указанного в предложении FROM запроса. Дополнительные сведения см. в разделе Указания соединений (Transact-SQL).

Для Azure Synapse Analytics и analytics Platform System (PDW) эти указания присоединения применяются к соединениям INNER на двух несовместимых столбцах распределения. Они могут повысить производительность запросов, ограничивая объем перемещаемых данных, который происходит во время обработки запросов. Допустимые подсказки для платформы Azure Synapse Analytics и Analytics Platform System (PDW) приведены следующим образом:

REDUCE

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

REPLICATE

Приводит к репликации значений в столбце соединения из таблицы справа от соединения на все узлы. Таблица слева присоединяется к реплицированной версии этих столбцов.

REDISTRIBUTE

Принудительно распространяет два источника данных на столбцы, указанные в предложении JOIN. Для распределенной таблицы система платформы Аналитики (PDW) выполняет перетасовку. Для реплицированной таблицы система платформы Аналитики (PDW) выполняет обрезку. Сведения об этих типах перемещения см. в разделе "Операции плана запросов DMS" статьи "Общие сведения о планах запросов" в документации по продукту системы платформы аналитики (PDW). Это указание может повысить производительность в случае, когда план запроса использует широковещательное перемещение для разрешения несовместимого соединения распределения.

JOIN

Указывает, что данная операция соединения должна произойти между указанными источниками или представлениями таблицы.

ON <search_condition>

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

SELECT p.ProductID,
    v.BusinessEntityID
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS v
    ON (p.ProductID = v.ProductID);

Если условие указывает столбцы, столбцы не должны иметь одинаковое имя или один и тот же тип данных; Однако если типы данных не совпадают, они должны быть совместимыми или типами, которые SQL Server может неявно преобразовать. Если типы данных не могут быть неявно преобразованы, условие должно явно преобразовать тип данных с помощью функции CONVERT.

Могут существовать предикаты, использующие в предложении ON только одну из соединяемых таблиц. Такие предикаты также могут присутствовать в предложении WHERE запроса. Хотя размещение таких предикатов не имеет разницы для внутренних соединений, они могут привести к другому результату при использовании внешних соединений. Это происходит потому, что предикаты в предложении ON применяются к таблице до соединения, в то время как предложение WHERE семантически применяется к результату соединения.

Дополнительные сведения об условиях поиска и предикатах см. в статье Условие поиска (Transact-SQL).

CROSS JOIN

Указывает перекрестное произведение двух таблиц. Возвращает те же строки, что и соединение без предложения WHERE в старом режиме, не совместимом с SQL-92.

left_table_source { CROSS | OUTER } APPLY right_table_source

Указывает, что right_table_source оператора APPLY определяется для каждой строки left_table_source. Данная функциональность полезна в том случае, когда right_table_source содержит функцию с табличным значением, которая принимает значения столбцов left_table_source в качестве одного из своих аргументов.

Вместе с ключевым словом APPLY должно быть указано либо CROSS, либо OUTER. Если указано CROSS, то при вычислении right_table_source для определенной строки left_table_source не создается ни одной строки и возвращается пустой результирующий набор.

При указании OUTER для каждой строки left_table_source создается одна строка, даже когда right_table_source вычисляется для этой строки и возвращается пустой результирующий набор.

Дополнительные сведения см. в разделе с примечаниями.

left_table_source

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

right_table_source

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

Предложение PIVOT

table_source PIVOT <pivot_clause>

Указывает, что значение table_source основано на pivot_column. table_source представляет собой таблицу или табличное выражение. Выходными данными является таблица, содержащая все столбцы table_source, за исключением pivot_column и value_column. Столбцы table_source, кроме pivot_column и value_column, называются столбцами группирования оператора PIVOT. Дополнительные сведения о PIVOT и UNPIVOT см. в разделе Использование операторов PIVOT и UNPIVOT.

Оператор PIVOT применяет операцию группирования к входной таблице по отношению к столбцам группирования и возвращает одну строку для каждой группы. Кроме того, вывод содержит один столбец для каждого значения, указанного в column_list, который отображается в pivot_column в input_table.

Дополнительные сведения см. в разделе «Замечания» далее.

aggregate_function

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

Системная агрегатная функция COUNT(*) не разрешена.

value_column

Столбец значений оператора PIVOT. При использовании с UNPIVOT value_column не может быть именем существующего столбца в входных table_source.

FOR pivot_column

Столбец сводной таблицы оператора PIVOT. Аргумент pivot_column должен иметь тип данных, который может быть явно или неявно преобразован в тип данных nvarchar(). Этот столбец не может быть изображением или строкой.

При использовании оператора UNPIVOT аргумент pivot_column является именем выходного столбца, полученного из table_source. Нет существующего столбца в table_source с таким именем.

IN ( column_list )

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

В предложении UNPIVOT перечислены столбцы в table_source , которые сузились до одной pivot_column.

table_alias

Имя псевдонима выходной таблицы. Аргумент pivot_table_alias должен быть указан.

UNPIVOT <unpivot_clause>

Указывает, что входная таблица сведена из нескольких столбцов в column_list в один столбец под названием pivot_column. Дополнительные сведения о PIVOT и UNPIVOT см. в разделе Использование операторов PIVOT и UNPIVOT.

AS OF <дата_время>

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL.

Возвращает таблицу с одной записью для каждой строки, содержащей значения, которые были фактическими (текущими) в указанный момент времени в прошлом. На внутреннем уровне объединение выполняется между темпоральной таблицей и соответствующей таблицей журнала, и результаты отфильтровываются так, чтобы возвращались значения в строке, которая была действительной на момент времени, определяемый параметром <дата_время>. Значение для строки считается действительным, если значение system_start_time_column_name меньше или равно значению параметра <дата_время>, а значение system_end_time_column_name больше значения параметра <дата_время>.

FROM <start_date_time TO <END_DATE_TIME>>

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL.

Возвращает таблицу, содержащую значения для всех версий строк, которые были активны в течение указанного интервала времени независимо от того, стали ли они активными до значения параметра <дата_время_начала> аргумента FROM или перестали быть активными после значения параметра <дата_время_окончания> аргумента TO. На внутреннем уровне объединение выполняется между темпоральной таблицей и соответствующей таблицей журнала и результаты отфильтровываются так, чтобы возвращать значения для всех версий строк, которые были активными в течение указанного временного диапазона. Строки, которые стали активными точно на нижней границе, определенной конечной точкой FROM, включаются и строки, которые стали активными точно на верхней границе, определенной конечной точкой TO, не включаются.

BETWEEN <дата_время_начала> AND <дата_время_окончания>

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL.

Аналогично приведенному выше описанию для FROM <дата_время_начала> TO <дата_время_окончания> за исключением того, что таблица возвращаемых строк включает строки, которые стали активными точно в верхнюю границу периода времени, определяемую конечной точкой <end_date_time>.

CONTAINED IN (<дата_время_начала> , <дата_время_окончания>)

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL.

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

ВСЕ

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

Замечания

Предложение FROM поддерживает синтаксис SQL-92 для присоединенных таблиц и производных таблиц. Синтаксис SQL-92 предусматривает операторы соединения INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS.

UNION и JOIN в предложении FROM поддерживаются в представлениях и в производных таблицах и вложенных запросах.

Самосоединение — это таблица, соединенная сама с собой. Операции вставки или обновления, основанные на самосоединении, следуют порядку, указанному в предложении FROM.

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

Использование APPLY

Как левый, так и правый операнды оператора APPLY являются табличными выражениями. Главное различие между этими операндами состоит в том, что right_table_source может использовать функцию с табличным значением, которая принимает столбец из left_table_source в качестве одного из аргументов функции. Left_table_source может включать табличное значение функции, но не может содержать аргументы, которые являются столбцами из right_table_source.

Для предоставления табличного источника для предложения FROM оператор APPLY выполняет следующее:

  1. Оценивает right_table_source для каждой строки а left_table_source для создания наборов строк.

    Значения в right_table_source зависят от left_table_source. right_table_source может быть представлено примерно в следующем виде: TVF(left_table_source.row), где TVF является функцией с табличным значением.

  2. Объединяет результирующие наборы, предоставляемые для каждой строки при оценке right_table_source с left_table_sourceleft_table_source, посредством выполнения операции UNION ALL.

    Список столбцов, полученный в результате выполнения оператора APPLY, представляет собой набор столбцов из left_table_source, объединенный со списком столбцов из right_table_source.

Использование PIVOT и UNPIVOT

Аргументы pivot_column и value_column являются столбцами группирования, используемыми оператором PIVOT. Для получения выходного результирующего набора оператор PIVOT выполняет следующее:

  1. Применяет GROUP BY к input_table к столбцам группирования и предоставляет одну выходную строку для каждой группы.

    Столбцы группирования в выходной строке получают соответствующие значения столбцов этой группы в input_table.

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

    1. Дополнительно группирует строки, созданные в GROUP BY для аргумента pivot_column в предыдущем шаге.

      Для каждого выходного столбца в column_list выбирает подгруппу, которая удовлетворяет следующим условиям:

      pivot_column = CONVERT(<data type of pivot_column>, 'output_column')

    2. aggregate_function вычисляется по отношению к value_column в этой подгруппе, и этот результат возвращается как значение соответствующего output_column. Если подгруппа пуста, SQL Server создает значение NULL для этого output_column. Если используется агрегатная функция COUNT, а подгруппа пуста, то возвращается значение (0).

Примечание.

Идентификаторы столбцов в предложении UNPIVOT следуют параметрам сортировки каталога. Для База данных SQL всегда выполняется SQL_Latin1_General_CP1_CI_ASсортировка. Для частично содержащихся баз данных SQL Server параметры сортировки всегда Latin1_General_100_CI_AS_KS_WS_SCсовпадают. Если столбец используется в сочетании с другими столбцами, для предотвращения конфликтов требуется предложение collate (COLLATE DATABASE_DEFAULT).

Дополнительные сведения о PIVOT и UNPIVOT, включая примеры, см. в разделе Использование операторов PIVOT и UNPIVOT.

Разрешения

Требует разрешения для инструкции DELETE, SELECT или UPDATE.

Примеры

А. Использование предложения FROM

В следующем примере извлекаются TerritoryID столбцы и Name столбцы из SalesTerritory таблицы в примере базы данных AdventureWorks2022.

SELECT TerritoryID,
    Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID;

Вот результирующий набор.

TerritoryID Name
----------- ------------------------------
1           Northwest
2           Northeast
3           Central
4           Southwest
5           Southeast
6           Canada
7           France
8           Germany
9           Australia
10          United Kingdom
(10 row(s) affected)

B. Использование подсказок оптимизатора TABLOCK и HOLDLOCK

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

BEGIN TRANSACTION

SELECT COUNT(*)
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK);

В. Использование синтаксиса SQL-92 CROSS JOIN

В следующем примере возвращается кросс-продукт двух таблиц Employee и Department в базе данных AdventureWorks2022. Возвращается список всех возможных сочетаний строк BusinessEntityID и все строки имен Department.

SELECT e.BusinessEntityID,
    d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.BusinessEntityID,
    d.Name;

D. Использование синтаксиса ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ SQL-92

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

-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name,
    sod.SalesOrderID
FROM Production.Product AS p
FULL JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
ORDER BY p.Name;

Е. Использование синтаксиса SQL-92 LEFT OUTER JOIN

Следующий пример соединяет две таблицы по столбцу ProductID и сохраняет несовпадающие строки из левой таблицы. Таблица Product сопоставляется с таблицей SalesOrderDetail по столбцам ProductID в каждой таблице. В результирующем наборе отражаются все продукты (как входящие в заказы, так и не входящие).

SELECT p.Name,
    sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
ORDER BY p.Name;

F. Использование синтаксиса ВНУТРЕННЕГО СОЕДИНЕНИЯ SQL-92

Следующий пример возвращает все названия продуктов и идентификаторы заказов.

-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name,
    sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID
ORDER BY p.Name;

G. Использование синтаксиса SQL-92 RIGHT OUTER JOIN

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

SELECT st.Name AS Territory,
    sp.BusinessEntityID
FROM Sales.SalesTerritory AS st
RIGHT OUTER JOIN Sales.SalesPerson AS sp
    ON st.TerritoryID = sp.TerritoryID;

H. Использование подсказок соединения HASH и MERGE

Следующий пример выполняет соединение трех таблиц — Product, ProductVendor и Vendor — для формирования списка продуктов и их поставщиков. Оптимизатор запросов соединяет таблицы Product и ProductVendor (p и pv) с помощью соединения слиянием (MERGE). Затем результаты соединения слиянием таблиц Product и ProductVendor (p и pv) соединяются при помощи HASH в таблицу Vendor для формирования (p и pv) и v.

Внимание

После того как задано указание соединения, ключевое слово INNER более не является необязательным и должно быть задано в явном виде для выполнения INNER JOIN.

SELECT p.Name AS ProductName,
    v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
    ON pv.BusinessEntityID = v.BusinessEntityID
ORDER BY p.Name,
    v.Name;

I. Используйте производную таблицу

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

SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name,
    d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e
    ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN (
    SELECT bea.BusinessEntityID,
        a.City
    FROM Person.Address AS a
    INNER JOIN Person.BusinessEntityAddress AS bea
        ON a.AddressID = bea.AddressID
    ) AS d
    ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName,
    p.FirstName;

J. Использование TABLEAMPLE для чтения данных из примера строк в таблице

В следующем примере используется TABLESAMPLE в предложении FROM для возврата около 10 процентов всех строк из таблицы Customer.

SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM(10 PERCENT);

K. Использование APPLY

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

Имя объекта Имена столбцов
Отделы DeptID, DivisionID, DeptName, DeptMgrID
EmpMgr MgrID, EmpID
Сотрудники EmpID, EmpLastName, EmpFirstName, EmpSalary
GetReports(MgrID) EmpID, EmpLastName, EmpSalary

Функция с табличным значением GetReports возвращает список всех сотрудников, которые находятся в прямом или косвенном подчинении указанного менеджера MgrID.

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

SELECT DeptID,
    DeptName,
    DeptMgrID,
    EmpID,
    EmpLastName,
    EmpSalary
FROM Departments d
CROSS APPLY dbo.GetReports(d.DeptMgrID);

Если необходимо, чтобы запрос предоставил строки для тех отделов без сотрудников, в которых будут выданы значения NULL для столбцов EmpID, EmpLastName и EmpSalary, нужно вместо APPLY применить OUTER APPLY.

SELECT DeptID,
    DeptName,
    DeptMgrID,
    EmpID,
    EmpLastName,
    EmpSalary
FROM Departments d
OUTER APPLY dbo.GetReports(d.DeptMgrID);

L. Использование CROSS APPLY

В следующем примере показано получение моментального снимка всех планов запросов, находящихся в кэше планов, путем получения дескрипторов планов для всех планов запросов в кэше запросом динамического административного представления sys.dm_exec_cached_plans. Затем оператор CROSS APPLY передает дескрипторы планов в sys.dm_exec_query_plan. Вывод инструкции Showplan в формате XML для каждого плана, находящегося в кэше планов, находится в столбце query_plan возвращаемой таблицы.

USE master;
GO

SELECT dbid,
    object_id,
    query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO

M. Использование FOR SYSTEM_TIME

Область применения: SQL Server 2016 (13.x) и более поздних версий и База данных SQL.

В следующем примере используется аргумент FOR SYSTEM_TIME AS OF date_time_literal_or_variable для возврата строк таблицы, которые были фактическими (текущими) по состоянию на 1 января 2014 года.

SELECT DepartmentNumber,
    DepartmentName,
    ManagerID,
    ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME AS OF '2014-01-01'
WHERE ManagerID = 5;

В следующем примере аргумент FOR SYSTEM_TIME FROM date_time_literal_or_variable TO date_time_literal_or_variable возвращает все строки, активные в течение периода, определенного с 1 января 2013 г. и заканчивая 1 января 2014 г., исключающим верхнюю границу.

SELECT DepartmentNumber,
    DepartmentName,
    ManagerID,
    ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME FROM '2013-01-01' TO '2014-01-01'
WHERE ManagerID = 5;

В следующем примере используется аргумент FOR SYSTEM_TIME BETWEEN date_time_literal_or_variable AND date_time_literal_or_variable для возврата всех строк, активных в период, определенный с 1 января 2013 г. и заканчивая 1 января 2014 г., включительно с верхней границой.

SELECT DepartmentNumber,
    DepartmentName,
    ManagerID,
    ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME BETWEEN '2013-01-01' AND '2014-01-01'
WHERE ManagerID = 5;

В следующем примере аргумент FOR SYSTEM_TIME CONTAINED IN (date_time_literal_or_variable, date_time_literal_or_variable) используется для возврата всех открытых и закрытых строк в течение периода, определенного с 1 января 2013 г. и заканчивая 1 января 2014 г.

SELECT DepartmentNumber,
    DepartmentName,
    ManagerID,
    ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME CONTAINED IN ('2013-01-01', '2014-01-01')
WHERE ManagerID = 5;

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

DECLARE @AsOfFrom DATETIME2 = DATEADD(month, -12, SYSUTCDATETIME());
DECLARE @AsOfTo DATETIME2 = DATEADD(month, -6, SYSUTCDATETIME());

SELECT DepartmentNumber,
    DepartmentName,
    ManagerID,
    ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME
FROM @AsOfFrom TO @AsOfTo
WHERE ManagerID = 5;

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

N. Использование синтаксиса INNER JOIN

В следующем примере возвращаются столбцы SalesOrderNumber, ProductKey и EnglishProductName из таблиц FactInternetSales и DimProduct с одинаковым ключом соединения ProductKey в обеих таблицах. EnglishProductName Столбцы SalesOrderNumber и столбцы существуют только в одной из таблиц, поэтому не нужно указывать псевдоним таблицы с этими столбцами, как показано ниже. Эти псевдонимы включены для удобства чтения. Слово AS перед именем псевдонима не требуется, но рекомендуется для удобочитаемости и соответствия стандарту ANSI.

-- Uses AdventureWorks
  
SELECT fis.SalesOrderNumber,
    dp.ProductKey,
    dp.EnglishProductName
FROM FactInternetSales AS fis
INNER JOIN DimProduct AS dp
    ON dp.ProductKey = fis.ProductKey;

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

-- Uses AdventureWorks
  
SELECT fis.SalesOrderNumber,
    dp.ProductKey,
    dp.EnglishProductName
FROM FactInternetSales AS fis
INNER JOIN DimProduct AS dp
    ON dp.ProductKey = fis.ProductKey;

Для ограничения результатов в этом запросе также можно использовать предложение WHERE. В этом примере результаты ограничиваются SalesOrderNumber значениями, выше чем "SO5000":

-- Uses AdventureWorks
  
SELECT fis.SalesOrderNumber,
    dp.ProductKey,
    dp.EnglishProductName
FROM FactInternetSales AS fis
INNER JOIN DimProduct AS dp
    ON dp.ProductKey = fis.ProductKey
WHERE fis.SalesOrderNumber > 'SO50000'
ORDER BY fis.SalesOrderNumber;

O. Использование синтаксиса LEFT OUTER JOIN и RIGHT OUTER JOIN

В следующем примере соединяются таблицы FactInternetSales и DimProduct по столбцам ProductKey. Синтаксис левого внешнего соединения сохраняет несовпадающие строки из левой (FactInternetSales) таблицы. FactInternetSales Так как таблица не содержит никаких ProductKey значений, которые не соответствуют DimProduct таблице, этот запрос возвращает те же строки, что и первый пример внутреннего соединения, приведенный выше в этой статье.

-- Uses AdventureWorks
  
SELECT fis.SalesOrderNumber,
    dp.ProductKey,
    dp.EnglishProductName
FROM FactInternetSales AS fis
LEFT OUTER JOIN DimProduct AS dp
    ON dp.ProductKey = fis.ProductKey;

Этот запрос можно написать без ключевого слова OUTER.

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

-- Uses AdventureWorks
  
SELECT fis.SalesOrderNumber,
    dp.ProductKey,
    dp.EnglishProductName
FROM DimProduct AS dp
RIGHT OUTER JOIN FactInternetSales AS fis
    ON dp.ProductKey = fis.ProductKey;

Следующий запрос использует таблицу DimSalesTerritory в качестве левой таблицы в левом внешнем соединении. Он извлекает значения SalesOrderNumber из таблицы FactInternetSales. Если для определенной SalesTerritoryKeyстроки нет заказов, запрос возвращает значение NULL для SalesOrderNumber этой строки. Этот запрос упорядочен столбцом SalesOrderNumber , чтобы все NULLs в этом столбце отображались в верхней части результатов.

-- Uses AdventureWorks
  
SELECT dst.SalesTerritoryKey,
    dst.SalesTerritoryRegion,
    fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
LEFT OUTER JOIN FactInternetSales AS fis
    ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;

Этот запрос можно переписать с правым внешним соединением, чтобы получать те же результаты:

-- Uses AdventureWorks
  
SELECT dst.SalesTerritoryKey,
    dst.SalesTerritoryRegion,
    fis.SalesOrderNumber
FROM FactInternetSales AS fis
RIGHT OUTER JOIN DimSalesTerritory AS dst
    ON fis.SalesTerritoryKey = dst.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;

P. Использование синтаксиса FULL OUTER JOIN

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

-- Uses AdventureWorks
  
SELECT dst.SalesTerritoryKey,
    dst.SalesTerritoryRegion,
    fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
FULL JOIN FactInternetSales AS fis
    ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;

Этот запрос можно написать без ключевого слова OUTER.

-- Uses AdventureWorks
  
SELECT dst.SalesTerritoryKey,
    dst.SalesTerritoryRegion,
    fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
FULL JOIN FactInternetSales AS fis
    ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;

В. Использование синтаксиса CROSS JOIN

В следующем примере возвращается векторное произведение двух таблиц FactInternetSales и DimSalesTerritory. Возвращается список всех возможных сочетаний SalesOrderNumber и SalesTerritoryKey. Обратите внимание на отсутствие предложения ON в запросе перекрестного соединения.

-- Uses AdventureWorks
  
SELECT dst.SalesTerritoryKey,
    fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
CROSS JOIN FactInternetSales AS fis
ORDER BY fis.SalesOrderNumber;

R. Используйте производную таблицу

В следующем примере используется производная таблица (инструкция SELECTпосле предложения FROM) для возврата столбцов CustomerKey и LastName всех клиентов в таблице DimCustomer со значениями BirthDate позже 1 января 1970 г. и фамилией Smith.

-- Uses AdventureWorks
  
SELECT CustomerKey,
    LastName
FROM (
    SELECT *
    FROM DimCustomer
    WHERE BirthDate > '01/01/1970'
    ) AS DimCustomerDerivedTable
WHERE LastName = 'Smith'
ORDER BY LastName;

S. Пример указания соединения REDUCE

В следующем примере используется указание соединения REDUCE для изменения обработки производной таблицы в запросе. При использовании указания соединения REDUCE в этом запросе выполняется проецирование, репликация и разделение fis.ProductKey и последующее соединение с DimProduct во время случайного перемещения DimProduct в ProductKey. Результирующая производная таблица распространяется на fis.ProductKey.

-- Uses AdventureWorks
  
SELECT SalesOrderNumber
FROM (
    SELECT fis.SalesOrderNumber,
        dp.ProductKey,
        dp.EnglishProductName
    FROM DimProduct AS dp
    INNER REDUCE JOIN FactInternetSales AS fis
        ON dp.ProductKey = fis.ProductKey
    ) AS dTable
ORDER BY SalesOrderNumber;

T. Пример указания соединения REPLICATE

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

-- Uses AdventureWorks

SELECT SalesOrderNumber
FROM (
    SELECT fis.SalesOrderNumber,
        dp.ProductKey,
        dp.EnglishProductName
    FROM DimProduct AS dp
    INNER REPLICATE JOIN FactInternetSales AS fis
        ON dp.ProductKey = fis.ProductKey
    ) AS dTable
ORDER BY SalesOrderNumber;

Ф. Используйте указание DISTRIBUTIONE, чтобы гарантировать перемещение shuffle для несовместимого соединения распределения

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

В следующем примере указание REDISTRIBUTE заставляет перемещение Shuffle в таблицу FactInternetSales, так как ProductKey является столбцом распространения для DimProduct и не является столбцом распространения для FactInternetSales.

-- Uses AdventureWorks
  
SELECT dp.ProductKey,
    fis.SalesOrderNumber,
    fis.TotalProductCost
FROM DimProduct AS dp
INNER REDISTRIBUTE JOIN FactInternetSales AS fis
    ON dp.ProductKey = fis.ProductKey;

V. Использование TABLEAMPLE для чтения данных из примера строк в таблице

В следующем примере используется TABLESAMPLE в предложении FROM для возврата около 10 процентов всех строк из таблицы Customer.

SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM(10 PERCENT);