Cláusula FROM mais JOIN, APPLY, PIVOT (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posterior Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics PDW (Analytics Platform System) Ponto de extremidade de análise de SQL no Microsoft Fabric Warehouse no Microsoft Fabric

No Transact-SQL, a cláusula FROM está disponível nas seguintes instruções:

A cláusula FROM geralmente é necessária na instrução SELECT. A exceção é quando nenhuma coluna de tabela é listada e os únicos itens listados são literais ou variáveis ou expressões aritméticas.

Este artigo também descreve as seguintes palavras-chave que podem ser usadas na cláusula FROM:

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe do SQL Server e do Banco de Dados SQL do Azure:

[ 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

Sintaxe para data warehouse paralelo, 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

Sintaxe para 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

Argumentos

<table_source>

Especifica uma tabela, exibição, variável de tabela ou origem de tabela derivada, com ou sem um alias, a ser usada na instrução Transact-SQL. Até 256 origens da tabela podem ser usadas em uma instrução, embora o limite varie de acordo com a memória disponível e a complexidade de outras expressões na consulta. Consultas individuais podem não aceitar até 256 origens de tabela.

Observação

O desempenho da consulta pode ser prejudicado com um grande número de tabelas referenciadas em uma consulta. O tempo de compilação e otimização também é afetado por outros fatores. Esses fatores incluem a presença de índices e exibições indexadas em cada <table_source> e o tamanho de <select_list> na instrução SELECT.

A ordem de origens de tabela após a palavra-chave FROM não afeta o conjunto de resultados retornado. O SQL Server retorna erros quando aparecem nomes duplicados na cláusula FROM.

table_or_view_name

O nome de uma tabela ou exibição.

Se a tabela ou exibição existir em outro banco de dados na mesma instância do SQL Server, use um nome totalmente qualificado no formato database.schema.object_name.

Se a tabela ou exibição existir fora da instância do SQL Server, use um nome de quatro partes no formato linked_server.catalog.schema.object. Para obter mais informações, confira sp_addlinkedserver (Transact-SQL). Um nome de quatro partes que é construído por meio da função OPENDATASOURCE como a parte do servidor no nome também pode ser usado para especificar a origem da tabela remota. Quando OPENDATASOURCE é especificado, database_name e schema_name podem não se aplicar a todas as fontes de dados e podem estar sujeitos às funcionalidades do Provedor OLE DB que acessa o objeto remoto.

[AS] table_alias

Um alias para table_source que pode ser usado por conveniência ou para distinguir uma tabela ou exibição em uma autojunção ou subconsulta. Em geral, um alias é um nome de tabela abreviado usado para referência a colunas específicas das tabelas em uma junção. Se o nome da coluna existir em mais de uma tabela na junção, o SQL Server poderá exigir que ele seja qualificado por um nome de tabela, nome de exibição ou alias para distinguir essas colunas. O nome da tabela não poderá ser usado se um alias estiver definido.

Quando uma tabela derivada, um conjunto de linhas, uma função com valor de tabela ou uma cláusula de operador (como PIVOT ou UNPIVOT) é usado, o table_alias obrigatório no final da cláusula é o nome da tabela associado para todas as colunas retornadas, incluindo colunas de agrupamento.

WITH (<table_hint> )

Especifica que o otimizador de consulta use uma estratégia de otimização ou bloqueio com esta tabela e para esta instrução. Para obter mais informações, confira Dicas de tabela (Transact-SQL).

rowset_function

Aplica-se a: SQL Server e Banco de Dados SQL.

Especifica uma das funções de conjunto de linhas, como OPENROWSET, a qual retorna um objeto que pode ser usado no lugar de uma referência de tabela. Para obter mais informações sobre uma lista de funções de conjunto de linhas, confira Funções de conjunto de linhas (Transact-SQL).

O uso das funções OPENROWSET e OPENQUERY para especificar que um objeto remoto depende dos recursos do provedor OLE DB que acessa o objeto.

bulk_column_alias

Aplica-se a: SQL Server e Banco de Dados SQL.

Um alias opcional para substituir um nome de coluna no conjunto de resultados. Os aliases de coluna são permitidos somente em instruções SELECT que usam a função OPENROWSET com a opção BULK. Ao usar bulk_column_alias, especifique um alias para cada coluna da tabela na mesma ordem que as colunas no arquivo.

Observação

Este alias substitui o atributo NAME nos elementos COLUMN de um arquivo de formato XML, se houver.

user_defined_function

Especifica uma função com valor de tabela.

OPENXML <openxml_clause>

Aplica-se a: SQL Server e Banco de Dados SQL.

Fornece uma exibição de conjunto de linhas em um documento XML. Para obter mais informações, confira OPENXML (Transact-SQL).

derived_table

Uma subconsulta que recupera linhas do banco de dados. derived_table é usada como entrada para a consulta externa.

derived_table pode usar o recurso do construtor de valor de tabela da Transact-SQL para especificar várias linhas. Por exemplo, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. Para obter mais informações, confira Construtor de valor de tabela (Transact-SQL).

column_alias

Um alias opcional para substituir um nome de coluna no conjunto de resultados da tabela derivada. Inclua um alias para cada coluna na lista de seleção e encerre a lista completa de aliases de coluna entre parênteses.

table_or_view_name FOR SYSTEM_TIME <system_time>

Aplica-se a: SQL Server 2016 (13.x) e posteriores e Banco de Dados SQL.

Especifica que uma versão específica dos dados é retornada da tabela temporal especificada e sua tabela de histórico vinculada com controle de versão do sistema

cláusula TABLESAMPLE

Aplica-se a: pools de SQL dedicados do SQL Server, do Banco de Dados SQL e do Azure Synapse Analytics

Especifica que uma amostra de dados da tabela é retornada. A amostra pode ser aproximada. Esta cláusula pode ser usada em qualquer tabela primária ou unida em uma instrução SELECT ou UPDATE. TABLESAMPLE não pode ser especificado com exibições.

Observação

Quando você usa TABLESAMPLE em bancos de dados atualizados para o SQL Server, o nível de compatibilidade do banco de dados é definido como 110 ou mais alto, PIVOT não é permitido em uma consulta CTE (expressão de tabela comum) recursiva. Para obter mais informações, veja Nível de compatibilidade de ALTER DATABASE (Transact-SQL).

SYSTEM

Um método de amostragem dependente de implementação especificado por padrões ISO. No SQL Server, esse é o único método de amostragem disponível e é aplicado por padrão. SYSTEM aplica um método de amostragem baseado em páginas em que um conjunto aleatório de páginas de uma tabela é escolhido para a amostra, e todas as linhas dessas páginas são retornadas como subconjunto da amostra.

sample_number

Uma expressão numérica constante exata ou aproximada que representa o percentual ou o número de linhas. Quando especificado com PERCENT, sample_number é convertido implicitamente em um valor float; caso contrário, é convertido em bigint. PERCENT é o padrão.

PERCENT

Especifica que um percentual sample_number das linhas da tabela deve ser recuperado da tabela. Quando PERCENT é especificado, o SQL Server retorna um valor aproximado do percentual especificado. Quando PERCENT é especificado, a expressão sample_number deve ser avaliada como um valor de 0 a 100.

ROWS

Especifica que, aproximadamente, sample_number de linhas são recuperadas. Quando ROWS é especificado, o SQL Server retorna uma aproximação do número de linhas especificado. Quando ROWS é especificado, a expressão sample_number deve ser avaliada como um valor inteiro maior que zero.

REPEATABLE

Indica que a amostra selecionada pode ser retornada novamente. Quando é especificado com o mesmo valor de repeat_seed, o SQL Server retorna o mesmo subconjunto de linhas, desde que não seja feita nenhuma alteração nas linhas da tabela. Quando for especificado com outro valor de repeat_seed, o SQL Server provavelmente retornará uma amostra diferente das linhas da tabela. As seguintes ações na tabela são consideradas alterações: inserir, atualizar, excluir, recompilação do índice ou desfragmentação e restauração ou anexação do banco de dados.

repeat_seed

Uma expressão de inteiro constante usada pelo SQL Server para gerar um número aleatório. repeat_seed é bigint. Se repeat_seed não for especificado, SQL Server atribuirá um valor aleatório. Para um valor repeat_seed específico, o resultado da amostragem será sempre o mesmo se nenhuma alteração tiver sido aplicada à tabela. A expressão repeat_seed deve ser avaliada como um inteiro maior que zero.

Tabela unida

Uma tabela unida é um conjunto de resultados que é o produto de duas ou mais tabelas. Para várias junções, use parênteses para alterar a ordem natural das junções.

Tipo de junção

Especifica o tipo de operação de junção.

INNER

Especifica todos os pares de linhas correspondentes retornados. Descarta as linhas não correspondentes de ambas as tabelas. Quando nenhum tipo de junção é especificado, este é o padrão.

FULL [ OUTER ]

Especifica que uma linha da tabela esquerda ou direita que não atende à condição de junção seja incluída no conjunto de resultados, e as colunas de saída correspondentes à outra tabela sejam definidas como NULL. Isso ocorre além de todas as linhas normalmente retornadas por INNER JOIN.

LEFT [ OUTER ]

Especifica que todas as linhas da tabela esquerda que não atendem à condição de junção sejam incluídas no conjunto de resultados, e as colunas de saída da outra tabela sejam definidas como NULL além de todas as linhas retornadas pela junção interna.

RIGHT [ OUTER ]

Especifica que todas as linhas da tabela direita que não atendem à condição de junção sejam incluídas no conjunto de resultados, e as colunas de saída que correspondem à outra tabela sejam definidas como NULL, além de todas as linhas retornadas pela junção interna.

Dica de junção

Para o SQL Server e Banco de Dados SQL, especifica que o otimizador de consulta do SQL Server usa uma dica de junção, ou um algoritmo de execução, por junção especificada na cláusula FROM da consulta. Para obter mais informações, confira Dicas de junção (Transact-SQL).

Para o Azure Synapse Analytics e PDW (Analytics Platform System), essas dicas de junção se aplicam a junções INNER em duas colunas incompatíveis com a distribuição. Elas podem melhorar o desempenho da consulta restringindo a quantidade de movimentação de dados que ocorre durante o processamento da consulta. As dicas de junção permitidas para o Azure Synapse Analytics e PDW (Analytics Platform System) são as seguintes:

REDUCE

Reduz o número de linhas a serem movidas para a tabela no lado direito da junção, a fim de tornar compatíveis as duas tabelas incompatíveis com a distribuição. A dica REDUCE também é chamada de uma dica de semijunção.

REPLICATE

Faz com que os valores na coluna de junção da tabela no lado direito da junção sejam replicados para todos os nós. A tabela à esquerda é unida à versão replicada dessas colunas.

REDISTRIBUTE

Força duas fontes de dados a serem distribuídas nas colunas especificadas na cláusula JOIN. Para uma tabela distribuída, o PDW (Analytics Platform System) executa uma movimentação de ordem aleatória. Para uma tabela replicada, o PDW (Analytics Platform System) executa uma movimentação de corte. Para entender esses tipos de movimentação, veja a seção "Operações de plano de consulta DMS" no artigo "Noções básicas sobre planos de consulta" na Documentação do produto PDW (Analytics Platform System). Essa dica pode melhorar o desempenho quando o plano de consulta usa uma movimentação de difusão para resolver uma junção incompatível com a distribuição.

JOIN

Indica que a operação de junção especificada deve acontecer entre as origens de tabela ou exibições especificadas.

ON <search_condition>

Especifica o critério no qual a junção se baseia. Os critérios podem especificar qualquer predicado, embora colunas e operadores de comparação sejam frequentemente usados, por exemplo:

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

Quando o critério especifica colunas, estas não precisam ter o mesmo nome ou o mesmo tipo de dados; no entanto, se os tipos de dados não forem iguais, eles deverão ser compatíveis ou tipos que o SQL Server possa converter implicitamente. Se os tipos de dados não puderem ser convertidos implicitamente, o critério deverá converter o tipo de dados explicitamente usando a função CONVERT.

Poderá haver predicados que envolvam somente uma das tabelas unidas na cláusula ON. Tais predicados também podem estar na cláusula WHERE da consulta. Embora a presença de tais predicados não faça diferença para junções INNER, eles podem gerar um resultado diferente quando junções OUTER estão envolvidas. Isso ocorre porque os predicados na cláusula ON são aplicados à tabela antes da junção, ao passo que a cláusula WHERE é semanticamente aplicada ao resultado da junção.

Para obter mais informações sobre critérios de pesquisa e predicados, confira Critério de pesquisa (Transact-SQL).

CROSS JOIN

Especifica o produto cruzado de duas tabelas. Retorna as mesmas linhas como se nenhuma cláusula WHERE estivesse especificada em uma junção em estilo antigo que não seja SQL-92.

left_table_source { CROSS | OUTER } APPLY right_table_source

Especifica que a right_table_source do operador APPLY é avaliada em cada linha da left_table_source. Essa funcionalidade é útil quando a right_table_source contém uma função com valor de tabela que usa valores de coluna da left_table_source como um de seus argumentos.

É necessário especificar CROSS ou OUTER com APPLY. Quando CROSS é especificado, nenhuma linha é produzida quando a right_table_source é avaliada em uma linha especificada da left_table_source e retorna um conjunto de resultados vazio.

Quando OUTER é especificado, uma linha é produzida para cada linha da left_table_source, mesmo quando a right_table_source é avaliada nessa linha e retorna um conjunto de resultados vazio.

Para obter mais informações, consulte a seção Comentários.

left_table_source

Uma origem de tabela conforme a definição no argumento anterior. Para obter mais informações, consulte a seção Comentários.

right_table_source

Uma origem de tabela conforme a definição no argumento anterior. Para obter mais informações, consulte a seção Comentários.

Cláusula PIVOT

table_source PIVOT <pivot_clause>

Especifica que a table_source é dinamizada com base na pivot_column. table_source é uma tabela ou uma expressão de tabela. A saída é uma tabela que contém todas as colunas da table_source, exceto a pivot_column e value_column. As colunas da table_source, exceto a pivot_column e a value_column, são chamadas as colunas de agrupamento do operador original. Para obter mais informações sobre PIVOT e UNPIVOT, consulte Usando PIVOT e UNPIVOT.

PIVOT executa uma operação de agrupamento na tabela de entrada em relação às colunas de agrupamento e retorna uma linha para cada grupo. Além disso, a saída contém uma coluna para cada valor especificado na column_list exibido na pivot_column da input_table.

Para obter mais informações, consulte a seção Comentários a seguir.

aggregate_function

Um sistema ou uma função de agregação definida pelo usuário que aceita uma ou mais entradas. A função de agregação deve ser invariável para valores nulos. Uma função de agregação invariável para valores nulos não considera valores nulos no grupo enquanto está avaliando o valor de agregação.

A função de agregação de sistema COUNT (*) não é permitida.

value_column

A coluna de valor do operador PIVOT. Quando usado com UNPIVOT, value_column não pode ser o nome de uma coluna existente na table_source de entrada.

FOR pivot_column

A coluna dinâmica do operador PIVOT. pivot_column deve ser de um tipo implícita ou explicitamente conversível em nvarchar() . Esta coluna não pode ser image ou rowversion.

Quando UNPIVOT é usado, pivot_column é o nome da coluna de saída que é reduzida com base na table_source. Não pode haver uma coluna em table_source com esse nome.

IN ( column_list )

Na cláusula PIVOT, lista os valores na pivot_column que se tornarão os nomes de coluna da tabela de saída. A lista não pode especificar nomes de coluna já existentes na table_source de entrada que está sendo dinamizada.

Na cláusula UNPIVOT, lista as colunas na table_source que são reduzidas a uma única pivot_column.

table_alias

O nome do alias da tabela de saída. pivot_table_alias deve ser especificado.

UNPIVOT <unpivot_clause>

Especifica que a tabela de entrada é reduzida com base em várias colunas na column_list a uma única coluna chamada pivot_column. Para obter mais informações sobre PIVOT e UNPIVOT, consulte Usando PIVOT e UNPIVOT.

AS OF <date_time>

Aplica-se a: SQL Server 2016 (13.x) e posteriores e Banco de Dados SQL.

Retorna uma tabela com um único registro para cada linha que contém os valores que foram reais (atuais) no momento passado especificado. Internamente, uma união é executada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores na linha que era válida no ponto no tempo especificado pelo parâmetro <date_time>. O valor de uma linha é considerado válido se o valor de system_start_time_column_name é menor ou igual ao valor do parâmetro <date_time> e o valor de system_end_time_column_name é maior que o valor do parâmetro <date_time>.

FROM <start_date_time> TO <end_date_time>

Aplica-se a: SQL Server 2016 (13.x) e posteriores e Banco de Dados SQL.

Retorna uma tabela com os valores para todas as versões de registro que estavam ativas no intervalo de tempo especificado, sem levar em conta se eles começaram a estar ativos antes do valor de parâmetro <start_date_time> para o argumento FROM ou deixaram de estar ativos após o valor de parâmetro <end_date_time> para o argumento TO. Internamente, uma união é executada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores para todas as versões de linha que estavam ativas a qualquer momento durante o intervalo de tempo especificado. As linhas que se tornaram ativas exatamente no limite inferior definido pelo ponto de extremidade FROM são incluídas e as linhas que se tornaram ativas exatamente no limite superior definido pelo ponto de extremidade TO não são incluídas.

BETWEEN <start_date_time> AND <end_date_time>

Aplica-se a: SQL Server 2016 (13.x) e posteriores e Banco de Dados SQL.

A mesma descrição acima para FROM <start_date_time> TO <end_date_time> é válida, exceto que ela inclui linhas que se tornaram ativas no limite superior definido pelo ponto de extremidade <end_date_time>.

CONTAINED IN (<start_date_time> , <end_date_time>)

Aplica-se a: SQL Server 2016 (13.x) e posteriores e Banco de Dados SQL.

Retorna uma tabela com os valores para todas as versões de registro que foram abertas e fechadas dentro do intervalo de tempo especificado definido por dois valores de data e hora para o argumento CONTAINED IN. As linhas que se tornaram ativas exatamente no limite inferior ou que deixaram de ser ativas exatamente no limite superior são incluídas.

ALL

Retorna uma tabela com os valores de todas as linhas da tabela atual e da tabela de histórico.

Comentários

A cláusula FROM aceita a sintaxe SQL-92 para tabelas unidas e derivadas. Sintaxe SQL-92 fornece os operadores de junção INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER e CROSS.

Há suporte para UNION e JOIN em uma cláusula FROM dentro de exibições e em tabelas derivadas e subconsultas.

Uma autojunção é uma tabela unida a ela mesma. As operações de inserção ou atualização que são baseadas em uma autojunção seguem a ordem da cláusula FROM.

Como o SQL Server considera as estatísticas de distribuição e cardinalidade de servidores vinculados que fornecem estatísticas de distribuição de coluna, a dica de junção REMOTE não é necessária para impor a avaliação de uma junção remotamente. O processador de consulta do SQL Server considera estatísticas remotas e determina se uma estratégia da junção remota é apropriada. A dica de junção REMOTE é útil para provedores que não fornecem estatísticas de distribuição de coluna.

Usar APPLY

Ambos os operandos à esquerda e à direita do operador APPLY são expressões de tabela. A principal diferença entre esses operandos é que right_table_source pode usar uma função com valor de tabela que usa uma coluna da left_table_source como um dos argumentos da função. A left_table_source pode incluir funções com valor de tabela, mas não pode conter argumentos que são colunas da right_table_source.

O operador APPLY funciona da seguinte maneira para criar a origem de tabela para a cláusula FROM:

  1. Avalia right_table_source em cada linha da left_table_source para produzir conjuntos de linhas.

    Os valores na right_table_source dependem de left_table_source. right_table_source pode ser representada aproximadamente da seguinte maneira: TVF(left_table_source.row), em que TVF é uma função com valor de tabela.

  2. Combina os conjuntos de resultados que são produzidos para cada linha na avaliação de right_table_source com a left_table_source executando uma operação UNION ALL.

    A lista de colunas produzida pelo resultado do operador APPLY é o conjunto de colunas da left_table_source combinado à lista de colunas da right_table_source.

Usar PIVOT e UNPIVOT

A pivot_column e a value_column são colunas de agrupamento usadas pelo operador PIVOT. Este segue o seguinte processo para obter o conjunto de resultados de saída:

  1. Executa um GROUP BY em sua input_table nas colunas de agrupamento e produz uma linha de saída para cada grupo.

    As colunas de agrupamento na linha de saída obtêm os valores de coluna correspondentes para o grupo na input_table.

  2. Gera valores para as colunas da lista de colunas para cada linha de saída da seguinte forma:

    1. Agrupando ainda as linhas geradas em GROUP BY na etapa anterior na pivot_column.

      Para cada coluna de saída na column_list, selecionando um subgrupo que atenda à condição:

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

    2. aggregate_function é avaliada na value_column desse subgrupo e seu resultado é retornado como o valor da output_column correspondente. Se o subgrupo estiver vazio, o SQL Server gerará um valor nulo para essa output_column. Se a função de agregação for COUNT e o subgrupo estiver vazio, será retornado zero (0).

Observação

Os identificadores de coluna na cláusula UNPIVOT seguem a ordenação de catálogo. Para o Banco de Dados SQL, a ordenação é sempre SQL_Latin1_General_CP1_CI_AS. Para bancos de dados parcialmente independentes do SQL Server, a ordenação é sempre Latin1_General_100_CI_AS_KS_WS_SC. Se a coluna for combinada com outras colunas, uma cláusula COLLATE (COLLATE DATABASE_DEFAULT) será necessária para evitar conflitos.

Para obter mais informações sobre PIVOT e UNPIVOT, incluindo exemplos, consulte Usando PIVOT e UNPIVOT.

Permissões

Requer as permissões para a instrução DELETE, SELECT ou UPDATE.

Exemplos

a. Usar uma cláusula FROM

O exemplo a seguir recupera as colunas TerritoryID e Name da tabela SalesTerritory no banco de dados de exemplo AdventureWorks2022.

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

Veja a seguir o conjunto de resultados.

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. Usar as dicas de otimizador TABLOCK e HOLDLOCK

A transação parcial a seguir mostra como posicionar um bloqueio de tabela compartilhado explícito em Employee e como ler o índice. O bloqueio é mantido ao longo de toda a transação.

BEGIN TRANSACTION

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

C. Usar a sintaxe SQL-92 CROSS JOIN

O exemplo a seguir retorna o produto cruzado das tabelas Employee e Department no banco de dados AdventureWorks2022. Uma lista de todas as possíveis combinações de linhas de BusinessEntityID e todas as linhas de nome de Department é retornada.

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. Usar a sintaxe SQL-92 FULL OUTER JOIN

O exemplo a seguir retorna o nome do produto e eventuais ordens de venda correspondentes na tabela SalesOrderDetail do banco de dados AdventureWorks2022. Ele também retorna as ordens de venda que não têm produtos listados na tabela Product, bem como produtos com uma ordem de venda diferente daquela listada na tabela 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;

E. Usar a sintaxe SQL-92 LEFT OUTER JOIN

O exemplo a seguir une duas tabelas em ProductID e preserva as linhas não correspondentes da tabela esquerda. É feita a correspondência da tabela Product com a tabela SalesOrderDetail nas colunas ProductID em cada tabela. Todos os produtos, ordenados ou não, aparecem no conjunto de resultados.

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. Usar a sintaxe SQL-92 INNER JOIN

O exemplo a seguir retorna todos os nomes de produtos e IDs de ordens de venda.

-- 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. Usar a sintaxe SQL-92 RIGHT OUTER JOIN

O exemplo a seguir une duas tabelas em TerritoryID e preserva as linhas não correspondentes da tabela direita. É feita a correspondência da tabela SalesTerritory com a tabela SalesPerson na coluna TerritoryID em cada tabela. Todos os vendedores aparecem no conjunto de resultados, tenham ou não um território atribuído.

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. Usar as dicas de junção HASH e MERGE

O exemplo a seguir executa uma junção de três tabelas entre as tabelas Product, ProductVendor e Vendor para criar uma lista de produtos e seus fornecedores. O otimizador de consulta une Product e ProductVendor (p e pv) usando uma junção MERGE. Em seguida, os resultados da junção MERGE de Product e ProductVendor (p e pv) são unidos por HASH à tabela Vendor para criar (p e pv) e v.

Importante

Após uma dica de junção ser especificada, a palavra-chave INNER não é mais opcional e deve ser explicitamente declarada para a execução de uma 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. Use uma tabela derivada

O exemplo a seguir usa uma tabela derivada, uma instrução SELECT após a clausula FROM, para retornar o nome e o sobrenome de todos os funcionários e as cidades em que moram.

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. Usar TABLESAMPLE para ler dados de uma amostra de linhas em uma tabela

O exemplo a seguir usa TABLESAMPLE na cláusula FROM para retornar aproximadamente 10 por cento de todas as linhas na tabela Customer.

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

K. Usar APPLY

O seguinte exemplo presume que as seguintes tabelas e função com valor de tabela estejam presentes no banco de dados:

Nome do Objeto Nomes de coluna
Departments DeptID, DivisionID, DeptName, DeptMgrID
EmpMgr MgrID, EmpID
funcionários EmpID, EmpLastName, EmpFirstName, EmpSalary
GetReports(MgrID) EmpID, EmpLastName, EmpSalary

A função com valor de tabela GetReports retorna a lista de todos os funcionários que se reportam direta ou indiretamente ao MgrID especificado.

O exemplo usa APPLY para retornar todos os departamentos e todos os funcionários do departamento. Se um departamento em particular não tiver funcionários, não haverá linhas retornadas para ele.

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

Se desejar que a consulta gere linhas para os departamentos sem funcionários, o que irá gerar valores nulos para as colunas EmpID, EmpLastName e EmpSalary, use então OUTER APPLY.

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

L. Usar CROSS APPLY

O exemplo a seguir recupera um instantâneo de todos os planos de consulta residindo no cache de plano, consultando a exibição de gerenciamento dinâmico sys.dm_exec_cached_plans para recuperar os identificadores de plano de todas as consultas no cache. Em seguida, o operador CROSS APPLY é especificado para transmitir o identificador de plano a sys.dm_exec_query_plan. A saída de plano de execução XML de cada plano atualmente no cache de plano está na coluna query_plan da tabela retornada.

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. Usar FOR SYSTEM_TIME

Aplica-se a: SQL Server 2016 (13.x) e posteriores e Banco de Dados SQL.

O exemplo a seguir usa o argumento FOR SYSTEM_TIME AS OF date_time_literal_or_variable para retornar linhas da tabela que eram reais (atuais) a partir de 1º de janeiro de 2014.

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

O exemplo a seguir usa o argumento FOR SYSTEM_TIME FROM date_time_literal_or_variable TO date_time_literal_or_variable para retornar todas as linhas que estavam ativas durante o período definido começando em 1º de janeiro de 2013 e terminando em 1º de janeiro de 2014, excluindo o limite superior.

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

O exemplo a seguir usa o argumento FOR SYSTEM_TIME BETWEEN date_time_literal_or_variable AND date_time_literal_or_variable para retornar todas as linhas que estavam ativas durante o período definido começando em 1º de janeiro de 2013 e terminando em 1º de janeiro de 2014, incluindo o limite superior.

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

O exemplo a seguir usa o argumento FOR SYSTEM_TIME CONTAINED IN (date_time_literal_or_variable, date_time_literal_or_variable) para retornar todas as linhas que estavam abertas e fechadas durante o período definido começando em 1º de janeiro de 2013 e terminando em 1º de janeiro de 2014.

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

O exemplo a seguir usa uma variável em vez de um literal para fornecer os valores de limite de data para a consulta.

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;

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

N. Usar a sintaxe de INNER JOIN

O exemplo a seguir retorna as colunas SalesOrderNumber, ProductKey e EnglishProductName das tabelas FactInternetSales e DimProduct, em que a chave de junção, ProductKey, é correspondente em ambas as tabelas. As colunas SalesOrderNumber e EnglishProductName existem em uma das tabelas e, portanto, não é necessário especificar o alias da tabela com essas colunas, conforme mostrado; esses aliases são incluídos para facilitar a leitura. A palavra AS antes de um alias de nome não é obrigatória, mas é recomendada para facilitar a leitura e estar em conformidade com o padrão ANSI.

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

Como a palavra-chave INNER não é obrigatória para junções internas, essa mesma consulta pode ser escrita como:

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

Uma cláusula WHERE também pode ser usada com essa consulta para limitar os resultados. Este exemplo limita os resultados a valores SalesOrderNumber maiores que '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. Usar a sintaxe de LEFT OUTER JOIN e RIGHT OUTER JOIN

O exemplo a seguir une as tabelas FactInternetSales e DimProduct nas colunas ProductKey. A sintaxe da junção externa esquerda preserva as linhas não correspondentes da tabela à esquerda (FactInternetSales). Como a tabela FactInternetSales não contém nenhum valor ProductKey não correspondente na tabela DimProduct, essa consulta retorna as mesmas linhas como o primeiro exemplo de junção interna, anteriormente neste artigo.

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

Essa consulta também pode ser escrita sem a palavra-chave OUTER.

Em junções externas direitas, as linhas não correspondentes da tabela à direita são preservadas. O exemplo a seguir retorna as mesmas linhas que o exemplo de junção externa esquerda acima.

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

A consulta a seguir usa a tabela DimSalesTerritory como a tabela à esquerda em uma junção externa esquerda. Recupera os valores SalesOrderNumber da tabela FactInternetSales. Se não há nenhuma ordem para determinada SalesTerritoryKey, a consulta retorna um valor NULL para o SalesOrderNumber nessa linha. Essa consulta é ordenada pela coluna SalesOrderNumber, de modo que os valores NULL dessa coluna são exibidos na parte superior dos resultados.

-- 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;

Essa consulta pode ser reescrita com uma junção externa direita para recuperar os mesmos resultados:

-- 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. Usar a sintaxe FULL OUTER JOIN

O exemplo a seguir demonstra uma junção externa completa, que retorna todas as linhas de ambas as tabelas unidas, mas retorna NULL para valores não correspondentes da outra tabela.

-- 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;

Essa consulta também pode ser escrita sem a palavra-chave 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;

Q. Usar a sintaxe CROSS JOIN

O exemplo a seguir retorna o produto cruzado das tabelas FactInternetSales e DimSalesTerritory. Uma lista de todas as combinações possíveis de SalesOrderNumber e SalesTerritoryKey é retornada. Observe a ausência da cláusula ON da consulta de união cruzada.

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

R. Use uma tabela derivada

O exemplo a seguir usa uma tabela derivada (uma instrução SELECT após a cláusula FROM) para retornar as colunas CustomerKey e LastName de todos os clientes na tabela DimCustomer com valores BirthDate posteriores a 1º de janeiro de 1970 e o sobrenome 'Fernandes'.

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

S. Exemplo de dica de junção de REDUCE

O exemplo a seguir usa a dica de junção REDUCE para alterar o processamento da tabela derivada dentro da consulta. Ao usar a dica de junção REDUCE nesta consulta, a fis.ProductKey é projetada, replicada e diferenciada e, em seguida, unida ao DimProduct durante a ordem aleatória de DimProduct no ProductKey. A tabela derivada resultante é distribuída em 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. Exemplo de dica de junção de REPLICATE

Este próximo exemplo mostra a mesma consulta como no exemplo anterior, exceto que uma dica de junção REPLICATE é usada, em vez da dica de junção REDUCE. O uso da dica REPLICATE faz com que os valores na coluna ProductKey (de junção) da tabela FactInternetSales sejam replicados para todos os nós. A tabela DimProduct é unida à versão replicada desses valores.

-- 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;

U. Usar a dica REDISTRIBUTE para assegurar uma movimentação de Ordem Aleatória para uma junção incompatível com a distribuição

A consulta a seguir usa a dica de consulta REDISTRIBUTE em uma junção incompatível com a distribuição. Isso garante que o otimizador de consulta use uma movimentação de Ordem Aleatória no plano de consulta. Isso também garante que o plano de consulta não usará uma movimentação de Difusão, que move uma tabela distribuída para uma tabela replicada.

No exemplo a seguir, a dica REDISTRIBUTE força uma movimentação de Ordem Aleatória na tabela FactInternetSales porque ProductKey é a coluna de distribuição de DimProduct e não é a coluna de distribuição para 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. Usar TABLESAMPLE para ler dados de uma amostra de linhas em uma tabela

O exemplo a seguir usa TABLESAMPLE na cláusula FROM para retornar aproximadamente 10 por cento de todas as linhas na tabela Customer.

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