Condividi tramite

Clausola FROM con JOIN, APPLY, PIVOT (Transact-SQL)

  • Articolo

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azureendpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft FabricWarehouse nel database SQL di Microsoft Fabricin Microsoft Fabric

In Transact-SQL, la clausola FROM è disponibile per le istruzioni seguenti:

La clausola FROM è in genere obbligatoria per l'istruzione SELECT. L'eccezione è quando non viene elencata alcuna colonna di tabella e gli unici elementi elencati sono valori letterali o variabili o espressioni aritmetiche.

Questo articolo illustra anche le parole chiave seguenti che possono essere usate nella clausola FROM:

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi per SQL Server, database SQL di Azure e database SQL dell'infrastruttura:

[ 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

Sintassi per 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

Sintassi per 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

Argomenti

<table_source>

Specifica una tabella, una vista, una variabile di tabella o un'origine di tabella derivata con o senza alias, da usare nell'istruzione Transact-SQL. In un'istruzione sono consentite fino a 256 origini di tabella. Il limite varia tuttavia in base alla memoria disponibile e alla complessità delle altre espressioni nella query, ovvero alcune query specifiche potrebbero non supportare 256 origini di tabella.

Nota

Nelle query con riferimenti a molte tabelle le prestazioni di esecuzione potrebbero essere ridotte. I tempi di compilazione e ottimizzazione vengono influenzati anche da fattori aggiuntivi, ad esempio la presenza di indici e visualizzazioni indicizzate in ogni <table_source> e le dimensioni di <select_list> nell'istruzione SELECT.

L'ordine delle origini di tabella dopo la parola chiave FROM non influisce sul set di risultati restituito. Quando la clausola FROM include nomi duplicati, SQL Server restituisce un errore.

table_or_view_name

Nome di una tabella o di una vista.

Se la tabella o la visualizzazione è presente in un altro database della stessa istanza di SQL Server, specificare un nome completo nel formato database.schema.object_name.

Se la tabella o la visualizzazione è presente all'esterno dell'istanza di SQL Server, specificare un nome composto da quattro parti nel formato linked_server.catalog.schema.object. Per altre informazioni, vedere sp_addlinkedserver (Transact-SQL). Per specificare l'origine della tabella remota è anche possibile usare un nome composto da quattro parti formulato tramite la funzione OPENDATASOURCE come componente server del nome. Quando viene specificato OPENDATASOURCE, database_name e schema_name possono non essere validi per tutte le origini dati e non essere soggetti alle funzionalità del provider OLE DB che accede all'oggetto remoto.

[AS] table_alias

Alias per table_source che può essere utilizzato per praticità o per distinguere una tabella o una vista in una sottoquery o self-join. Un alias è spesso un nome di tabella abbreviato utilizzato per fare riferimento a colonne specifiche delle tabelle di un join. Se lo stesso nome di colonna esiste in più tabelle del join, SQL Server potrebbe richiedere che il nome della colonna sia qualificato da un nome di tabella, un nome di vista o un alias per distinguere queste colonne. Il nome della tabella non può essere usato se è definito un alias.

Quando viene usata una tabella derivata, una funzione con valori di tabella o per i set di righe oppure una clausola con operatori (ad esempio PIVOT o UNPIVOT), il parametro table_alias necessario alla fine della clausola è il nome della tabella associata per tutte le colonne restituite, comprese le colonne di raggruppamento.

WITH (<table_hint> )

Specifica che Query Optimizer usa una strategia di ottimizzazione o blocco con questa tabella e per questa istruzione. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).

rowset_function

Si applica a: SQL Server e database SQL.

Specifica una delle funzioni del set di righe, ad esempio OPENROWSET, che restituisce un oggetto che può essere utilizzato invece di un riferimento a una tabella. Per altre informazioni su un elenco di funzioni per i set di righe, vedere Funzioni per i set di righe (Transact-SQL).

L'utilizzo delle funzioni OPENROWSET e OPENQUERY per specificare un oggetto remoto dipende dalle funzionalità del provider OLE DB che accede all'oggetto.

bulk_column_alias

Si applica a: SQL Server e database SQL.

Alias facoltativo per sostituire un nome di colonna nel set di risultati. Gli alias di colonna sono consentiti solo nelle istruzioni SELECT che utilizzano la funzione OPENROWSET con l'opzione BULK. Quando si usa bulk_column_alias, specificare un alias per ogni colonna di tabella nello stesso ordine delle colonne nel file.

Nota

Questo alias esegue l'override dell'attributo NAME negli elementi COLUMN di un file in formato XML, se presente.

user_defined_function

Specifica una funzione con valori di tabella.

OPENXML <openxml_clause>

Si applica a: SQL Server e database SQL.

Consente di visualizzare un documento XML come set di righe. Per altre informazioni, vedere OPENXML (Transact-SQL).

derived_table

Sottoquery che recupera le righe dal database. derived_table viene usato come input per la query esterna.

derived_table consente di usare la funzionalità costruttore di valori di tabella Transact-SQL per la specifica di più righe. Ad esempio: SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. Per altre informazioni, vedere Costruttore di valori di tabella (Transact-SQL).

column_alias

Alias facoltativo per sostituire un nome di colonna nel set di risultati della tabella derivata. Includere un alias di colonna per ogni colonna nell'elenco di selezione e racchiudere tra parentesi l'intero elenco di alias di colonna.

table_or_view_name FOR SYSTEM_TIME system_time <>

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL.

Specifica che venga restituita una determinata versione dei dati della tabella temporale indicata e della relativa tabella di cronologia con controllo delle versioni di sistema collegata

Clausola TABLESAMPLE

Si applica a: pool SQL Server, database SQL e Azure Synapse Analytics dedicati

Specifica che vengono restituiti dati di esempio dalla tabella. I dati di esempio possono essere approssimativi. Questa clausola può essere usata in ogni tabella primaria o unita in join in un'istruzione SELECT o UPDATE. Non è possibile specificare TABLESAMPLE con le visualizzazioni.

Nota

Quando si utilizza TABLESAMPLE sui database aggiornati a SQL Server, il livello di compatibilità del database viene impostato su 110 o su un valore maggiore. L'operatore PIVOT non è consentito in una query per un'espressione di tabella comune (CTE) ricorsiva. Per altre informazioni, vedere Livello di compatibilità ALTER DATABASE (Transact-SQL).

SYSTEM

Metodo di campionamento dipendente dall'implementazione specificato dagli standard ISO. In SQL Server è l'unico metodo di campionamento disponibile e viene applicato per impostazione predefinita. SYSTEM applica un metodo di campionamento basato su pagine in cui come campione viene scelto un set di pagine casuale dalla tabella e tutte le righe di tali pagine vengono restituite come subset campione.

sample_number

Espressione numerica costante esatta o approssimativa che rappresenta la percentuale o il numero di righe. Quando viene specificato con PERCENT, sample_number viene implicitamente convertito in valore di tipo float. In caso contrario, viene convertito in bigint. PERCENT è l'impostazione predefinita.

PERCENT

Specifica che una percentuale sample_number di righe della tabella deve essere recuperata dalla tabella. Quando viene specificato PERCENT, SQL Server restituisce un valore approssimativo della percentuale specificata. Quando viene specificato PERCENT l'espressione sample_number deve restituire un valore compreso tra 0 e 100.

ROWS

Specifica che vengono recuperati circa sample_number di righe. Quando viene specificato ROWS, SQL Server restituisce un'approssimazione del numero di righe specificato. Quando viene specificato ROWS, l'espressione sample_number deve restituire un valore integer maggiore di zero.

REPEATABLE

Indica che il campione selezionato può essere restituito nuovamente. Se specificato con lo stesso valore repeat_seed , SQL Server restituisce lo stesso subset di righe purché non siano state apportate modifiche alle righe della tabella. Se specificato con un valore repeat_seed diverso, probabilmente SQL Server restituirà un campione di righe della tabella diverso. Le azioni seguenti nella tabella vengono considerate modifiche: inserimento, aggiornamento, eliminazione, ricompilazione o deframmentazione dell'indice e ripristino o collegamento del database.

repeat_seed

Espressione integer costante utilizzata da SQL Server per generare un numero casuale. repeat_seed è bigint. Se repeat_seed non viene specificato, SQL Server assegna un valore in modo casuale. Per un valore repeat_seed specifico, il risultato del campionamento è sempre lo stesso se non sono state applicate modifiche alla tabella. L'espressione repeat_seed deve restituire un valore integer maggiore di zero.

Tabella unita in join

Un tabella unita in join è un set di risultati che rappresenta il prodotto di due o più tabelle. In caso di più join, utilizzare le parentesi per modificarne l'ordine standard.

Tipo di join

Specifica il tipo di operazione di join.

INNER

Specifica che vengono restituite tutte le coppie di righe corrispondenti. Le righe senza corrispondenza vengono eliminate da entrambe le tabelle. Corrisponde al valore predefinito se non viene specificato alcun tipo di join.

FULL [OUTER]

Specifica che una riga della tabella a sinistra o a destra che non soddisfa la condizione di join viene inclusa nel set di risultati e le colonne di output corrispondenti all'altra tabella vengono impostate su NULL. Questa si aggiunge a tutte le righe normalmente restituite dall'INNER JOIN.

LEFT [OUTER]

Specifica che, oltre alle righe restituite dall'inner join, vengono incluse nel set di risultati tutte le righe della tabella sinistra che non rispettano la condizione di join e le colonne di output dell'altra tabella sono impostate su NULL.

RIGHT [ OUTER ]

Specifica che, oltre alle righe restituite dall'inner join, vengono incluse nel set di risultati tutte le righe della tabella destra che non rispettano le condizioni di join e le colonne di output che corrispondono all'altra tabella sono impostate su NULL.

Hint per il join

Per SQL Server e database SQL, specifica che Query Optimizer di SQL Server usa un hint di join o un algoritmo di esecuzione, per join specificato nella clausola QUERY FROM. Per altre informazioni, vedere Hint di join (Transact-SQL).

Per Azure Synapse Analytics e per la piattaforma di strumenti analitici (PDW), questi hint di join si applicano ai join INNER in due colonne incompatibili di distribuzione. Possono migliorare le prestazioni delle query limitando lo spostamento dei dati che si verifica durante l'elaborazione delle query. Gli hint di join consentiti per Azure Synapse Analytics e per la piattaforma di strumenti analitici (PDW) sono i seguenti:

REDUCE

Riduce il numero di righe da spostare per la tabella sul lato destro del join per rendere compatibili due tabelle non compatibili di distribuzione. L'hint REDUCE è chiamato anche hint di semi-join.

REPLICA

Fa sì che i valori nella colonna di join della tabella a destra del join vengano replicati in tutti i nodi. La tabella a sinistra viene unita alla versione replicata di tali colonne.

REDISTRIBUTE

Forza la distribuzione di due origini dati nelle colonne specificate nella clausola JOIN. Per una tabella distribuita, Analytics Platform System (PDW) esegue uno spostamento casuale. Per una tabella replicata, Analytics Platform System (PDW) esegue uno spostamento di taglio. Per comprendere questi tipi di spostamento, vedere la sezione "Operazioni del piano di query DMS" nell'articolo "Informazioni sui piani di query" nella documentazione del prodotto Analytics Platform System (PDW). Questo hint può migliorare le prestazioni quando il piano di query usa uno spostamento dei dati trasmessi per risolvere un join non compatibile di distribuzione.

JOIN

Indica che l'operazione di join specificata deve essere eseguita tra le viste o le origini di tabella specificate.

ON <search_condition>

Specifica la condizione su cui è basato il join. La condizione può includere qualsiasi predicato, ma vengono in genere utilizzati nomi di colonne e operatori di confronto, ad esempio:

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

Quando la condizione specifica le colonne, le colonne non devono avere lo stesso nome o lo stesso tipo di dati; Tuttavia, se i tipi di dati non sono uguali, devono essere compatibili o tipi che SQL Server può convertire in modo implicito. Se i tipi di dati non possono essere convertiti in modo implicito, la condizione deve convertire in modo esplicito il tipo di dati usando la funzione CONVERT.

È possibile che alcuni predicati includano una sola delle tabelle unite in join nella clausola ON. Tali predicati potrebbero essere presenti inoltre nella clausola WHERE della query. Anche se la posizione di tali predicati non fa differenza per i inner join, potrebbe causare un risultato diverso quando vengono coinvolti outer join. I predicati inclusi nella clausola ON infatti vengono applicati alla tabella prima dell'esecuzione del join, mentre la clausola WHERE viene applicata semanticamente ai risultati del join.

Per altre informazioni sulle condizioni di ricerca e i predicati, vedere Condizioni di ricerca (Transact-SQL).

CROSS JOIN

Specifica il prodotto incrociato di due tabelle. Restituisce le righe che verrebbero restituite se non fosse specificata alcuna clausola WHERE in un join obsoleto, non SQL-92.

left_table_source { CROSS | OUTER } APPLY right_table_source

Specifica che right_table_source dell'operatore APPLY viene valutato rispetto a ogni riga di left_table_source. Questa funzionalità risulta utile quando right_table_source include una funzione con valori di tabella che accetta i valori di colonna da left_table_source come uno dei relativi argomenti.

È necessario specificare CROSS o OUTER con APPLY. Se si specifica CROSS, non vengono restituite righe quando right_table_source viene valutato rispetto a ogni riga specificata di left_table_source e viene restituito un set di risultati vuoto.

Se si specifica OUTER, viene restituita una riga per ogni riga di left_table_source anche quando right_table_source viene valutato rispetto a tale riga e viene restituito un set di risultati vuoto.

Per altre informazioni, vedere la sezione Osservazioni.

left_table_source

Origine tabella definita nell'argomento precedente. Per altre informazioni, vedere la sezione Osservazioni.

right_table_source

Origine tabella definita nell'argomento precedente. Per altre informazioni, vedere la sezione Osservazioni.

Clausola PIVOT

table_source PIVOT <pivot_clause>

Specifica che table_source venga trasformato tramite Pivot in base a pivot_column. table_source è una tabella o un'espressione di tabella. L'output è una tabella che contiene tutte le colonne di table_source ad eccezione di pivot_column e value_column. Le colonne di table_source, eccetto pivot_column e value_column, vengono definite colonne di raggruppamento dell'operatore PIVOT. Per altre informazioni su PIVOT e UNPIVOT, vedere Uso di PIVOT e UNPIVOT.

PIVOT esegue un'operazione di raggruppamento sulla tabella di input relativamente alle colonne di raggruppamento e restituisce una riga per ogni gruppo. L'output contiene anche una colonna per ogni valore specificato in column_list visualizzato in pivot_column in input_table.

Per ulteriori informazioni, vedere la sezione Osservazioni riportata di seguito.

aggregate_function

Funzione di aggregazione definita dall'utente o di sistema che accetta uno o più input. La funzione di aggregazione deve essere invariante rispetto ai valori Null. Una funzione di aggregazione invariante a valori Null non considera i valori Null nel gruppo durante la valutazione del valore di aggregazione.

La funzione di aggregazione di sistema COUNT(*) non è consentita.

value_column

Colonna value dell'operatore PIVOT. Se usato con UNPIVOT, value_column non può essere il nome di una colonna esistente nella table_source di input.

FOR pivot_column

Colonna pivot dell'operatore PIVOT. pivot_column deve essere di un tipo di dati che è possibile convertire in modo implicito o esplicito nel tipo nvarchar(). Questa colonna non può essere image o rowversion.

Quando viene usato UNPIVOT, pivot_column indica il nome della colonna di output risultante dal raggruppamento delle colonne di table_source. Non è possibile includere una colonna esistente in table_source con tale nome.

IN ( column_list )

Nella clausola PIVOT elenca i valori nella pivot_column che diventano i nomi di colonna della tabella di output. L'elenco non può specificare nomi di colonna già esistenti nell'table_source di input da modificare.

Nella clausola UNPIVOT elenca le colonne in table_source ristrette in un singolo pivot_column.

table_alias

Nome alias della tabella di output. È necessario specificare pivot_table_alias.

UNPIVOT <unpivot_clause>

Indica che la tabella di input viene ridotta da più colonne specificate in column_list a una singola colonna denominata pivot_column. Per altre informazioni su PIVOT e UNPIVOT, vedere Uso di PIVOT e UNPIVOT.

AS OF <date_time>

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL.

Restituisce una tabella con un singolo record per ogni riga contenente i valori che erano effettivi (correnti) in un momento specificato nel passato. Internamente, viene eseguita un'unione tra la tabella temporale e la relativa tabella di cronologia. I risultati vengono filtrati in modo da restituire i valori nella riga valida alla data e all'ora specificate nel parametro <date_time>. Il valore di una riga viene considerato valido se il valore system_start_time_column_name è minore o uguale al valore del parametro <date_time> e il valore system_end_time_column_name è maggiore del valore del parametro <date_time>.

FROM <start_date_time> TO <end_date_time>

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL.

Restituisce una tabella con i valori relativi a tutte le versioni di record attive nell'intervallo di tempo specificato, sia le versioni diventate attive prima della data e l'ora specificate nel parametro <start_date_time> per l'argomento FROM, sia le versioni che hanno cessato di essere attive dopo la data e l'ora indicate dal parametro <end_date_time> per l'argomento TO. Internamente, viene eseguita un'unione tra la tabella temporale e la relativa tabella di cronologia e i risultati vengono filtrati in modo da restituire i valori per tutte le versioni di riga che erano attive in qualsiasi momento durante l'intervallo di tempo specificato. Le righe che sono diventate attive esattamente sul limite inferiore definito dall'endpoint FROM sono incluse e le righe che sono diventate attive esattamente sul limite superiore definito dall'endpoint TO non sono incluse.

BETWEEN <start_date_time> AND <end_date_time>

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL.

Vale la descrizione riportata sopra per FROM <start_date_time> TO <end_date_time>, ma in questo caso vengono incluse le righe diventate attive in corrispondenza del limite superiore definito dall'endpoint <end_date_time>.

CONTAINED IN (<start_date_time> , <end_date_time>)

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL.

Restituisce una tabella con i valori per tutte le versioni di record che sono state aperte e chiuse nell'intervallo di tempo specificato, definito dai due valori datetime per l'argomento CONTAINED IN. Sono incluse le righe diventate attive esattamente in corrispondenza del limite inferiore o che non sono più state attive esattamente in corrispondenza del limite superiore.

ALL

Restituisce una tabella con i valori di tutte le righe della tabella corrente e della tabella di cronologia.

Osservazioni:

La clausola FROM supporta la sintassi SQL-92 per le tabelle unite in join e le tabelle derivate. Nella sintassi SQL-92 sono disponibili gli operatori di join INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER e CROSS.

In una clausola FROM le istruzioni UNION e JOIN sono supportate sia nelle viste, sia nelle tabelle derivate e nelle sottoquery.

Un self join è una tabella unita in join con se stessa. Nelle operazioni di inserimento e aggiornamento basate su un self join viene seguito l'ordine indicato nella clausola FROM.

Poiché SQL Server considera le statistiche di distribuzione e cardinalità dei server collegati che forniscono statistiche di distribuzione delle colonne, l'hint di join REMOTE non è necessario per forzare la valutazione di un join in modalità remota. La funzionalità Query Processor di SQL Server analizza le statistiche remote e determina se è appropriato adottare una strategia di join remoto. L'hint remote join è utile per i provider che non forniscono statistiche sulla distribuzione delle colonne.

Usare APPLY

Entrambi gli operandi sinistro e destro dell'operatore APPLY sono espressioni di tabella. La differenza principale tra questi operandi è rappresentata dal fatto che right_table_source può usare una funzione con valori di tabella in cui una colonna di left_table_source è considerata uno degli argomenti. Il left_table_source può includere funzioni con valori di tabella, ma non può contenere argomenti che sono colonne del right_table_source.

L'operatore APPLY funziona nel modo seguente per restituire l'origine di tabella per la clausola FROM:

  1. Valuta right_table_source rispetto a ogni riga di left_table_source per produrre set di righe.

    I valori di right_table_source dipendono da left_table_source. right_table_source può essere rappresentato approssimativamente in questo modo: TVF(left_table_source.row), dove TVF è una funzione con valori di tabella.

  2. Combina i set di risultati restituiti per ogni riga nella valutazione di right_table_source con left_table_source tramite un'operazione UNION ALL.

    L'elenco di colonne restituito dal risultato dell'operatore APPLY corrisponde al set di colonne di left_table_source combinato con l'elenco di colonne di right_table_source.

Usare PIVOT e UNPIVOT

pivot_column e value_column sono colonne di raggruppamento usate dall'operatore PIVOT. PIVOT segue il processo seguente per ottenere il set di risultati di output:

  1. Esegue GROUP BY in input_table sulle colonne di raggruppamento e restituisce una riga di output per ogni gruppo.

    Le colonne di raggruppamento nella riga di output ottengono i valori delle colonne corrispondenti per tale gruppo in input_table.

  2. Genera valori per le colonne nell'elenco delle colonne per ogni riga di output eseguendo le operazioni seguenti:

    1. Raggruppando le righe restituite in GROUP BY nel passaggio precedente rispetto a pivot_column.

      Selezionando per ogni colonna di output in column_list un sottogruppo che soddisfa la condizione:

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

    2. aggregate_function viene valutata rispetto a value_column in questo sottogruppo e il risultato viene restituito come valore per la colonna output_column corrispondente. Se il sottogruppo è vuoto, per la colonna output_column SQL Server genera un valore Null. Se la funzione di aggregazione è COUNT e il sottogruppo è vuoto, viene restituito zero (0).

Nota

Gli identificatori di colonna nella clausola UNPIVOT seguono le regole di confronto dei cataloghi. Per il database SQL, le regole di confronto sono sempre SQL_Latin1_General_CP1_CI_AS. Per i database di SQL Server parzialmente indipendenti, le regole di confronto sono sempre Latin1_General_100_CI_AS_KS_WS_SC. Se la colonna è combinata con altre colonne, sarà necessaria una clausola COLLATE, ovvero COLLATE DATABASE_DEFAULT, per evitare conflitti.

Per altre informazioni su PIVOT e UNPIVOT ed esempi, vedere Uso di PIVOT e UNPIVOT.

Autorizzazioni

Sono richieste le autorizzazioni per l'istruzione DELETE, SELECT o UPDATE.

Esempi

R. Usare una clausola FROM

Nell'esempio seguente vengono recuperate le TerritoryID colonne e Name dalla SalesTerritory tabella nel database di esempio AdventureWorks2022.

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

Il set di risultati è il seguente.

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. Usare gli hint per l'utilità di ottimizzazione TABLOCK e HOLDLOCK

Nella transazione parziale seguente viene illustrato come impostare un blocco di tabella condiviso esplicito in Employee e come leggere l'indice. Il blocco viene mantenuto attivo fino al termine della transazione.

BEGIN TRANSACTION

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

C. Usare la sintassi CROSS JOIN di SQL-92

Nell'esempio seguente viene restituito il prodotto incrociato delle due tabelle Employee e Department nel database AdventureWorks2022. Vengono restituiti inoltre un elenco di tutte le possibili combinazioni delle righe di BusinessEntityID e tutte le righe di nome 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. Usare la sintassi SQL-92 FULL OUTER JOIN

Nell'esempio seguente vengono restituiti il nome del prodotto e gli eventuali ordini di vendita corrispondenti nella SalesOrderDetail tabella del database AdventureWorks2022. Vengono inoltre restituiti gli ordini di vendita per cui non è elencato alcun prodotto nella tabella Product e tutti i prodotti con un ordine di vendita diverso da quello elencato nella tabella 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. Usare la sintassi SQL-92 LEFT OUTER JOIN

Nell'esempio seguente vengono unite in join due tabelle tramite la colonna ProductID. Le righe della tabella sinistra prive di corrispondenza vengono mantenute. La tabella Product viene confrontata con la tabella SalesOrderDetail nelle colonne ProductID in ogni tabella. Tutti i prodotti, ordinati e non ordinati, vengono visualizzati nel set dei risultati.

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. Usare la sintassi INNER JOIN di SQL-92

Nell'esempio seguente vengono restituiti tutti i nomi di prodotti e gli ID degli ordini di vendita.

-- 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. Usare la sintassi SQL-92 RIGHT OUTER JOIN

Nell'esempio seguente vengono unite in join due tabelle tramite la colonna TerritoryID. Le righe della tabella destra prive di corrispondenza vengono mantenute. La tabella SalesTerritory viene confrontata con la tabella SalesPerson nella colonna TerritoryID in ogni tabella. Tutti i venditori vengono visualizzati nel set dei risultati, a prescindere dal fatto che siano assegnati a un'area o meno.

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. Usare hint di join HASH e MERGE

Nell'esempio seguente viene eseguito un join delle tre tabelle Product, ProductVendor e Vendor per ottenere un elenco di prodotti e dei relativi fornitori. Query Optimizer unisce in join le tabelle Product e ProductVendor (p e pv) tramite un join di tipo MERGE. I risultati del join di tipo MERGE delle tabelle Product e ProductVendor (p e pv) vengono quindi uniti tramite join di tipo HASH alla tabella Vendor per ottenere (p e pv) e v.

Importante

Dopo avere specificato un hint di join, la parola chiave INNER non è più facoltativa e deve essere indicata in modo esplicito per l'esecuzione di un 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. Utilizzare una tabella derivata

Nell'esempio seguente viene utilizzata una tabella derivata, con un'istruzione SELECT dopo la clausola FROM, per restituire nome e cognome di tutti i dipendenti e le città in cui abitano.

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. Usare TABLESAMPLE per leggere i dati da un esempio di righe in una tabella

Nell'esempio seguente viene utilizzata l'opzione TABLESAMPLE nella clausola FROM per restituire approssimativamente il 10 percento di tutte le righe della tabella Customer.

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

K. Usare APPLY

Nell'esempio seguente si presuppone che nel database siano presenti le tabelle e la funzione con valori di tabella seguenti:

Nome oggetto Nomi colonne
Reparti DeptID, DivisionID, DeptName, DeptMgrID
EmpMgr MgrID, EmpID
Dipendenti EmpID, EmpSalary EmpLastName, EmpFirstName
GetReports(MgrID) EmpSalary EmpID, EmpLastName

La funzione con valori di tabella GetReports restituisce un elenco di tutti i dipendenti che sono subordinati direttamente o indirettamente all'elemento MgrID specificato.

In questo esempio viene utilizzato APPLY per restituire tutti i reparti e tutti i dipendenti in ogni reparto. Se un determinato reparto non ha dipendenti, non verranno restituite righe per tale reparto.

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

Se si desidera che la query restituisca righe per i reparti senza dipendenti, restituendo valori Null per le colonne EmpID, EmpLastName e EmpSalary, utilizzare invece OUTER APPLY.

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

.L Usare CROSS APPLY

Nell'esempio seguente viene recuperato uno snapshot di tutti i piani di query disponibili nella cache dei piani, eseguendo una query sulla DMV sys.dm_exec_cached_plans per recuperare gli handle per tutti i piani di query nella cache. Successivamente viene specificato l'operatore CROSS APPLY per passare gli handle del piano a sys.dm_exec_query_plan. L'output Showplan XML per ogni piano disponibile nella cache dei piani viene indicato nella colonna query_plan della tabella restituita.

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

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL.

Nell'esempio seguente viene utilizzato l'argomento FOR SYSTEM_TIME AS OF date_time_literal_or_variable per restituire righe di tabella effettive (correnti) a partire dal 1° gennaio 2014.

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

Nell'esempio seguente viene utilizzato l'argomento FOR SYSTEM_TIME FROM date_time_literal_or_variable TO date_time_literal_or_variable per restituire tutte le righe attive durante il periodo definito a partire dal 1° gennaio 2013 e che terminano con il 1° gennaio 2014, esclusivo del limite superiore.

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

Nell'esempio seguente viene utilizzato l'argomento FOR SYSTEM_TIME BETWEEN date_time_literal_or_variable AND date_time_literal_or_variable per restituire tutte le righe attive durante il periodo definito a partire dal 1° gennaio 2013 e che terminano con il 1° gennaio 2014, incluso il limite superiore.

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

Nell'esempio seguente viene utilizzato l'argomento FOR SYSTEM_TIME CONTAINED IN (date_time_literal_or_variable, date_time_literal_or_variable) per restituire tutte le righe aperte e chiuse durante il periodo definito a partire dal 1° gennaio 2013 e che termina con il 1° gennaio 2014.

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

L'esempio seguente usa una variabile anziché un letterale per specificare i valori limite di data per la query.

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;

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

N. Usare la sintassi INNER JOIN

L'esempio seguente restituisce le colonne SalesOrderNumber, ProductKey e EnglishProductName delle tabelle FactInternetSales e DimProduct in cui la chiave di join ProductKey corrisponde in entrambe le tabelle. Le SalesOrderNumber colonne e EnglishProductName sono presenti solo in una delle tabelle, pertanto non è necessario specificare l'alias di tabella con queste colonne, come illustrato. Questi alias sono inclusi per la leggibilità. La parola AS prima di un nome alias non è obbligatoria, ma è consigliata per la leggibilità e la conformità allo standard ANSI.

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

Poiché la INNER parola chiave non è necessaria per i inner join, è possibile scrivere la stessa query come segue:

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

È anche possibile usare una clausola WHERE con questa query per limitare i risultati. Questo esempio limita i risultati ai valori SalesOrderNumber maggiori di '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 Usare la sintassi LEFT OUTER JOIN e RIGHT OUTER JOIN

L'esempio seguente unisce le tabelle FactInternetSales e DimProduct nelle colonne ProductKey. La sintassi LEFT OUTER JOIN mantiene le righe senza corrispondenza della tabella di sinistra (FactInternetSales). Poiché la FactInternetSales tabella non contiene valori ProductKey che non corrispondono alla DimProduct tabella, questa query restituisce le stesse righe del primo esempio di inner join riportato in precedenza in questo articolo.

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

Questa query può essere scritta anche senza la parola chiave OUTER.

Nei right outer join le righe senza corrispondenza della tabella di destra vengono mantenute. L'esempio seguente restituisce le stesse righe dell'esempio di left outer join precedente.

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

La query seguente usa la tabella DimSalesTerritory come tabella di sinistra in un left outer join. Recupera i valori SalesOrderNumber della tabella FactInternetSales. Se non sono presenti ordini per un particolare SalesTerritoryKey, la query restituisce un valore NULL per l'oggetto SalesOrderNumber per tale riga. Questa query viene ordinata in base alla SalesOrderNumber colonna, in modo che tutti gli NULL in questa colonna vengano visualizzati nella parte superiore dei risultati.

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

Questa query potrebbe essere riscritta con un right outer join per recuperare gli stessi risultati:

-- 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. Usare la sintassi FULL OUTER JOIN

Nell'esempio seguente viene illustrato un outer join completo, che restituisce tutte le righe di entrambe le tabelle unite, ma restituisce NULL per i valori che non corrispondono dall'altra tabella.

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

Questa query può essere scritta anche senza la parola chiave 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;

D. Usare la sintassi CROSS JOIN

L'esempio seguente restituisce il prodotto presente in entrambe le tabelle FactInternetSales e DimSalesTerritory. Viene restituito un elenco di tutte le combinazioni possibili di SalesOrderNumber e SalesTerritoryKey. Si noti l'assenza della clausola ON della query di cross join.

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

.R Utilizzare una tabella derivata

L'esempio seguente usa una tabella derivata (un'istruzione SELECT dopo la clausola FROM) per restituire le colonne CustomerKey e LastName di tutti i clienti della tabella DimCustomer con valori BirthDate successivi all'1 gennaio 1970 e il cognome 'Smith'.

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

S. Esempio di hint di join REDUCE

L'esempio seguente usa l'hint di join REDUCE per modificare l'elaborazione della tabella derivata all'interno della query. Quando si usa l'hint di join REDUCE in questa query, fis.ProductKey viene proiettato, replicato e differenziato e quindi unito a DimProduct durante lo spostamento casuale di DimProduct in ProductKey. La tabella derivata risultante viene distribuita in 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. Esempio di hint di join REPLICATE

L'esempio successivo illustra la stessa query dell'esempio precedente in cui viene usato un hint di join REPLICATE anziché l'hint di join REDUCE. L'uso dell'hint REPLICATE causa la replica dei valori della colonna ProductKey (di join) della tabella FactInternetSales in tutti i nodi. La tabella DimProduct viene unita alla versione replicata dei valori.

-- 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. Usare l'hint REDISTRIBUTE per garantire uno spostamento casuale per un join non compatibile con la distribuzione

La query seguente usa l'hint di query REDISTRIBUTE in un join non compatibile di distribuzione. Ciò garantisce che Query Optimizer usi uno spostamento casuale nel piano di query. Ciò garantisce anche che il piano di query non usi uno spostamento broadcast, che sposta una tabella distribuita in una tabella replicata.

Nell'esempio seguente l'hint REDISTRIBUTE forza lo spostamento casuale nella tabella FactInternetSales perché ProductKey è la colonna di distribuzione per DimProduct e non è la colonna di distribuzione per 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. Usare TABLESAMPLE per leggere i dati da un esempio di righe in una tabella

Nell'esempio seguente viene utilizzata l'opzione TABLESAMPLE nella clausola FROM per restituire approssimativamente il 10 percento di tutte le righe della tabella Customer.

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

Contenuto correlato