Partilhar via

DA cláusula mais JOIN, APPLY, PIVOT (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)ponto de extremidade de análise SQL no Microsoft FabricWarehouse no Microsoft Fabricbanco de dados SQL no Microsoft Fabric

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

A cláusula FROM geralmente é exigida 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 discute as seguintes palavras-chave que podem ser usadas na cláusula FROM:

Transact-SQL convenções de sintaxe

Sintaxe

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

[ 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 do Parallel Data Warehouse, 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 do 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 fonte de tabela derivada, com ou sem um alias, a ser usada na instrução Transact-SQL. Até 256 fontes de tabela podem ser usadas em uma instrução, embora o limite varie dependendo da memória disponível e da complexidade de outras expressões na consulta. As consultas individuais podem não suportar até 256 fontes de tabela.

Observação

O desempenho da consulta pode sofrer com muitas tabelas referenciadas em uma consulta. O tempo de compilação e otimização também é afetado por fatores adicionais. Isso inclui a presença de índices e exibições indexadas em cada <table_source> e o tamanho do <select_list> na instrução SELECT.

A ordem das fontes da tabela após a palavra-chave FROM não afeta o conjunto de resultados retornado. O SQL Server retorna erros quando nomes duplicados aparecem 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 banco de dados.esquema.object_name.

Se a tabela ou exibição existir fora da instância do SQL Serverl, use um nome de quatro partes no formato linked_server.catálogo.esquema.objeto. Para obter mais informações, consulte sp_addlinkedserver (Transact-SQL). Um nome de quatro partes que é construído usando a função OPENDATASOURCE como a parte do servidor do nome também pode ser usado para especificar a fonte de tabela remota. Quando OPENDATASOURCE é especificado, database_name e schema_name podem não se aplicar a todas as fontes de dados e estão sujeitas aos recursos 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 autoassociação ou subconsulta. Um alias é frequentemente um nome de tabela abreviado usado para se referir a colunas específicas das tabelas em uma associação. Se o mesmo nome de coluna existir em mais de uma tabela na associação, o SQL Server pode exigir que o nome da coluna seja qualificado por um nome de tabela, nome de exibição ou alias para distinguir essas colunas. O nome da tabela não pode ser usado se um alias estiver definido.

Quando uma tabela derivada, um conjunto de linhas ou uma função com valor de tabela ou cláusula de operador (como PIVOT ou UNPIVOT) é usada, a table_alias necessária no final da cláusula é o nome da tabela associada para todas as colunas, incluindo o agrupamento de colunas, retornadas.

COM (<table_hint> )

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

rowset_function

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

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

Usar as funções OPENROWSET e OPENQUERY para especificar 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 MASS. Ao usar bulk_column_alias, especifique um alias para cada coluna da tabela na mesma ordem das colunas no arquivo.

Observação

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

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 sobre um documento XML. Para obter mais informações, consulte OPENXML (Transact-SQL).

derived_table

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

derived_table pode usar o recurso Transact-SQL construtor de valor de tabela 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, consulte Table Value Constructor (Transact-SQL).

column_alias

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

table_or_view_name PARA SYSTEM_TIME <system_time>

Aplica-se a: SQL Server 2016 (13.x) e versões 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 de versão do sistema vinculada

Cláusula TABLESAMPLE

Aplica-se a: SQL Server, Banco de Dados SQL e pools SQL dedicados 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 modos de exibição.

Observação

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

SISTEMA

Um método de amostragem dependente da implementação especificado pelas normas 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ágina no qual um conjunto aleatório de páginas da tabela é escolhido para a amostra, e todas as linhas nessas páginas são retornadas como o subconjunto de amostra.

sample_number

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

PERCENTAGEM

Especifica que um sample_number por cento das linhas da tabela deve ser recuperado da tabela. Quando PERCENT é especificado, o SQL Server retorna uma porcentagem aproximada da especificada. Quando PERCENT é especificado, a expressão sample_number deve ser avaliada para um valor de 0 a 100.

LINHAS

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 especificadas. Quando ROWS é especificado, a expressão sample_number deve ser avaliada para um valor inteiro maior que zero.

REPETÍVEL

Indica que a amostra selecionada pode ser retornada novamente. Quando especificado com o mesmo valor repeat_seed, o SQL Server retorna o mesmo subconjunto de linhas, desde que nenhuma alteração tenha sido feita em nenhuma linha na tabela. Quando especificado com um valor de repeat_seed diferente, o SQL Server provavelmente retornará algum exemplo diferente das linhas na tabela. As seguintes ações na tabela são consideradas alterações: inserir, atualizar, excluir, reconstruir ou desfragmentar o índice e restaurar ou anexar o banco de dados.

repeat_seed

Uma expressão inteira constante usada pelo SQL Server para gerar um número aleatório. repeat_seed é bigint. Se repeat_seed não for especificado, o SQL Server atribuirá um valor aleatoriamente. Para um valor de repeat_seed específico, o resultado da amostragem é sempre o mesmo se não tiverem sido aplicadas alterações ao quadro. A expressão repeat_seed deve ser avaliada para um número inteiro maior que zero.

Mesa 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 adesão

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

INTERIOR

Especifica que todos os pares de linhas correspondentes são retornados. Descarta linhas incomparáveis de ambas as tabelas. Quando nenhum tipo de associação é especificado, esse é o padrão.

COMPLETO [ EXTERIOR ]

Especifica que uma linha da tabela esquerda ou direita que não atenda à condição de junção é incluída no conjunto de resultados e as colunas de saída que correspondem à outra tabela são definidas como NULL. Isso é além de todas as linhas normalmente retornadas pelo INNER JOIN.

ESQUERDA [ EXTERIOR ]

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

DIREITA [ EXTERIOR ]

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

Junte-se à dica

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

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

REDUZIR

Reduz o número de linhas a serem movidas para a tabela no lado direito da junção para tornar duas tabelas de distribuição incompatíveis compatíveis. A dica REDUCE também é chamada de dica semi-junte.

REPLICAR

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.

REDISTRIBUIR

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

ADERIR

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

EM <search_condition>

Especifica a condição na qual a junção se baseia. A condição pode especificar qualquer predicado, embora colunas e operadores de comparação sejam usados com freqüência, por exemplo:

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

Quando a condição especifica colunas, as colunas não precisam ter o mesmo nome ou mesmo tipo de dados; no entanto, se os tipos de dados não forem os mesmos, 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, a condição deverá converter explicitamente o tipo de dados usando a função CONVERTER.

Pode haver predicados que envolvem apenas uma das tabelas unidas na cláusula ON. Tais predicados também podem estar na cláusula WHERE na consulta. Embora a colocação de tais predicados não faça diferença para as junções INTERNAS, elas podem causar um resultado diferente quando as junções OUTER estão envolvidas. Isso ocorre porque os predicados na cláusula ON são aplicados à tabela antes da junção, enquanto a cláusula WHERE é semanticamente aplicada ao resultado da junção.

Para obter mais informações sobre condições e predicados de pesquisa, consulte Condição de pesquisa (Transact-SQL).

JUNÇÃO CRUZADA

Especifica o produto cruzado de duas tabelas. Retorna as mesmas linhas como se nenhuma cláusula WHERE fosse especificada em uma associação de estilo antigo, não no estilo SQL-92.

left_table_source { CRUZ | OUTER } APLICAR right_table_source

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

CROSS ou OUTER devem ser especificados com APPLY. Quando CROSS é especificado, nenhuma linha é produzida quando o right_table_source é avaliado em relação a uma linha especificada do left_table_source e retorna um conjunto de resultados vazio.

Quando OUTER é especificado, uma linha é produzida para cada linha do left_table_source mesmo quando o right_table_source é avaliado em relação a essa linha e retorna um conjunto de resultados vazio.

Para obter mais informações, consulte a seção Observações.

left_table_source

Uma fonte de tabela conforme definido no argumento anterior. Para obter mais informações, consulte a seção Observações.

right_table_source

Uma fonte de tabela conforme definido no argumento anterior. Para obter mais informações, consulte a seção Observações.

Cláusula PIVOT

table_source PIVOT <pivot_clause>

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

O 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 no column_list que aparece no pivot_column do input_table.

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

aggregate_function

Um sistema ou função agregada definida pelo usuário que aceita uma ou mais entradas. A função agregada deve ser invariante a valores nulos. Uma função agregada invariante a valores nulos não considera valores nulos no grupo enquanto está avaliando o valor agregado.

A função de agregação do 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 no table_sourcede entrada.

PARA pivot_column

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

Quando UNPIVOT é usado, pivot_column é o nome da coluna de saída que se torna reduzida a partir do table_source. Não pode haver uma coluna existente em table_source com esse nome.

EM ( column_list )

Na cláusula PIVOT, lista os valores na pivot_column que se torna os nomes das colunas da tabela de saída. A lista não pode especificar nomes de colunas que já existam no table_source de entrada que está sendo pivotado.

Na cláusula UNPIVOT, lista as colunas em table_source que é reduzida em um único 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 de várias colunas em column_list para uma única coluna chamada pivot_column. Para obter mais informações sobre PIVOT e UNPIVOT, consulte Usando PIVOT e UNPIVOT.

A PARTIR DE <date_time>

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de dados SQL.

Retorna uma tabela com registro único para cada linha contendo os valores que eram reais (atuais) no ponto no tempo especificado no passado. Internamente, uma união é realizada 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 momento especificado pelo parâmetro <date_time>. O valor de uma linha é considerado válido se o valor system_start_time_column_name for menor ou igual ao valor do parâmetro <date_time> e o valor system_end_time_column_name for maior que o valor do parâmetro <date_time>.

DE <start_date_time> A <end_date_time>

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de dados SQL.

Retorna uma tabela com os valores de todas as versões de registro que estavam ativas dentro do intervalo de tempo especificado, independentemente de terem começado a estar ativas antes do valor do parâmetro <start_date_time> para o argumento FROM ou terem deixado de estar ativas após o valor do parâmetro <end_date_time> para o argumento TO. Internamente, uma união é realizada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores de 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.

ENTRE <start_date_time> E <end_date_time>

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores e Banco de dados SQL.

O mesmo que acima no FROM <start_date_time> TO <end_date_time> descrição, exceto que inclui linhas que se tornaram ativas no limite superior definido pelo ponto de extremidade <end_date_time>.

CONTIDO EM (<start_date_time> , <end_date_time>)

Aplica-se a: SQL Server 2016 (13.x) e versões 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 pelos dois valores datetime para o argumento CONTAINED IN. As linhas que se tornaram ativas exatamente no limite inferior ou deixaram de estar ativas exatamente no limite superior estão incluídas.

TUDO

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 suporta a sintaxe SQL-92 para tabelas unidas e tabelas derivadas. A sintaxe SQL-92 fornece os operadores INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER e CROSS join.

UNION e JOIN dentro de uma cláusula FROM são suportados em visualizações e em tabelas e subconsultas derivadas.

Uma auto-junção é uma mesa que está unida a si mesma. Inserir ou atualizar operações baseadas em uma associação automática siga a ordem na cláusula FROM.

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

Use APPLY

Os operandos esquerdo e direito do operador APPLY são expressões de tabela. A principal diferença entre esses operandos é que o right_table_source pode usar uma função com valor de tabela que toma uma coluna do left_table_source como um dos argumentos da função. O left_table_source pode incluir funções com valor de tabela, mas não pode conter argumentos que sejam colunas do right_table_source.

O operador APPLY trabalha da seguinte maneira para produzir a fonte da tabela para a cláusula FROM:

  1. Avalia right_table_source em relação a cada linha do left_table_source para produzir conjuntos de linhas.

    Os valores no right_table_source dependem de left_table_source. right_table_source pode ser representado aproximadamente desta forma: TVF(left_table_source.row), onde 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 o left_table_source executando uma operação UNION ALL.

    A lista de colunas produzida pelo resultado do operador APPLY é o conjunto de colunas do left_table_source que é combinado com a lista de colunas do right_table_source.

Usar PIVOT e UNPIVOT

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

  1. Executa um GROUP BY em seu input_table em relação às 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 esse grupo no input_table.

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

    1. Agrupando adicionalmente as linhas geradas no GRUPO BY na etapa anterior em relação ao pivot_column.

      Para cada coluna de saída no column_list, selecionando um subgrupo que satisfaça a condição:

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

    2. aggregate_function é avaliada em relação ao value_column neste subgrupo e seu resultado é retornado como o valor da output_columncorrespondente. Se o subgrupo estiver vazio, o SQL Server gerará um valor nulo para esse output_column. Se a função agregada for COUNT e o subgrupo estiver vazio, zero (0) será retornado.

Observação

Os identificadores de coluna na cláusula UNPIVOT seguem o agrupamento de catálogo. Para o Banco de dados SQL, o agrupamento é sempre SQL_Latin1_General_CP1_CI_AS. Para bancos de dados parcialmente contidos do SQL Server, o agrupamento é sempre Latin1_General_100_CI_AS_KS_WS_SC. Se a coluna for combinada com outras colunas, será necessária uma cláusula de agrupamento (COLLATE DATABASE_DEFAULT) 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

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

Aqui está 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. Use as dicas do otimizador TABLOCK e HOLDLOCK

A transação parcial a seguir mostra como colocar um bloqueio de tabela compartilhada explícito no Employee e como ler o índice. O bloqueio é mantido durante toda a transação.

BEGIN TRANSACTION

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

C. Use a sintaxe SQL-92 CROSS JOIN

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

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 todas as ordens de venda correspondentes na tabela SalesOrderDetail no banco de dados AdventureWorks2022. Ele também retorna todas as ordens de venda que não têm nenhum produto listado na tabela Product e quaisquer produtos com uma ordem de venda diferente da 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 no ProductID e preserva as linhas incomparáveis da tabela esquerda. A tabela Product é correspondida com a tabela SalesOrderDetail nas colunas ProductID em cada tabela. Todos os produtos, encomendados e não encomendados, 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. Use a sintaxe SQL-92 INNER JOIN

O exemplo a seguir retorna todos os nomes de produtos e IDs de ordem 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. Use a sintaxe SQL-92 RIGHT OUTER JOIN

O exemplo a seguir une duas tabelas no TerritoryID e preserva as linhas incomparáveis da tabela direita. A tabela SalesTerritory é correspondida com a tabela SalesPerson na coluna TerritoryID em cada tabela. Todos os vendedores aparecem no conjunto de resultados, quer lhes seja ou não atribuído um território.

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

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

Importante

Depois que uma dica de junção é especificada, a palavra-chave INNER não é mais opcional e deve ser explicitamente declarada para que uma INNER JOIN seja executada.

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;

Eu. Usar uma tabela derivada

O exemplo a seguir usa uma tabela derivada, uma instrução SELECT após a cláusula FROM, para retornar os nomes e sobrenomes de todos os funcionários e as cidades em que vivem.

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. Use 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% de todas as linhas na tabela Customer.

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

K. Use APPLY

O exemplo a seguir pressupõe que as seguintes tabelas e função com valor de tabela existem no banco de dados:

Nome do objeto Nomes de colunas
Departamentos 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 MgrIDespecificado.

O exemplo usa APPLY para retornar todos os departamentos e todos os funcionários desse departamento. Se um determinado departamento não tiver funcionários, não haverá nenhuma linha retornada para esse departamento.

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

Se você quiser que a consulta produza linhas para esses departamentos sem funcionários, o que produzirá valores nulos para as colunas EmpID, EmpLastName e EmpSalary, use OUTER APPLY em vez disso.

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

L. Use CROSS APPLY

O exemplo a seguir recupera um instantâneo de todos os planos de consulta que residem no cache de planos, consultando a exibição de gerenciamento dinâmico de sys.dm_exec_cached_plans para recuperar os identificadores de plano de todos os planos de consulta no cache. Em seguida, o operador de CROSS APPLY é especificado para passar as alças do plano para sys.dm_exec_query_plan. A saída XML Showplan para cada plano atualmente no cache do plano está na coluna query_plan da tabela que é 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. Use PARA SYSTEM_TIME

Aplica-se a: SQL Server 2016 (13.x) e versões 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 de tabela que eram reais (atuais) em 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 como 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 como 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 foram abertas e fechadas durante o período definido como 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 and Analytics Platform System (PDW)

N. Use a sintaxe INNER JOIN

O exemplo a seguir retorna as colunas SalesOrderNumber, ProductKeye EnglishProductName das tabelas FactInternetSales e DimProduct onde a chave de junção, ProductKey, corresponde em ambas as tabelas. As colunas SalesOrderNumber e EnglishProductName existem apenas em uma das tabelas, portanto, não é necessário especificar o alias da tabela com essas colunas, como é mostrado; Esses aliases são incluídos para facilitar a leitura. A palavra AS antes de um nome de alias não é necessária, mas é recomendada para legibilidade e para 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 é necessá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 SalesOrderNumber valores superiores a '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. Use a sintaxe LEFT OUTER JOIN e RIGHT OUTER JOIN

O exemplo a seguir une as tabelas FactInternetSales e DimProduct nas colunas ProductKey. A sintaxe de junção externa esquerda preserva as linhas incomparáveis da tabela esquerda (FactInternetSales). Como a tabela FactInternetSales não contém valores de ProductKey que não correspondam à tabela DimProduct, essa consulta retorna as mesmas linhas que 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;

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

Nas junções externas direitas, as linhas incomparáveis 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. Ele recupera os valores de SalesOrderNumber da tabela FactInternetSales. Se não houver ordens para um SalesTerritoryKeyespecífico, a consulta retornará um NULL para o SalesOrderNumber dessa linha. Esta consulta é ordenada pela coluna SalesOrderNumber, para que quaisquer NULLs nesta coluna apareçam 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;

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

O exemplo a seguir demonstra uma junção externa completa, que retorna todas as linhas de ambas as tabelas associadas, mas retorna NULL para valores que não correspondem 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;

Esta 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. Use 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 na consulta de junção cruzada.

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

R. Usar 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 'Smith'.

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

O exemplo a seguir usa a dica de junção de REDUCE para alterar o processamento da tabela derivada dentro da consulta. Ao usar a dica de junção de REDUCE nesta consulta, o fis.ProductKey é projetado, replicado e distinto e, em seguida, unido a DimProduct durante a confusão de DimProduct em 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 REPLICATE

Este próximo exemplo mostra a mesma consulta que o exemplo anterior, exceto que uma dica de junção de REPLICATE é usada em vez da dica de junção de REDUCE. O uso da dica REPLICATE faz com que os valores na coluna ProductKey (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. Use a dica REDISTRIBUTE para garantir um movimento aleatório para uma associação incompatível com distribuição

A consulta a seguir usa a dica de consulta REDISTRIBUTE em uma associação de distribuição incompatível. Isso garante que o otimizador de consulta use uma movimentação 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 um movimento aleatório na tabela FactInternetSales porque ProductKey é a coluna de distribuição para 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. Use 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% de todas as linhas na tabela Customer.

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

Conteúdo relacionado