Dela via

FROM-sats plus JOIN, APPLY, PIVOT (Transact-SQL)

  • Artikel

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft FabricSQL-databas i Microsoft Fabric

I Transact-SQL är FROM-satsen tillgänglig på följande instruktioner:

FROM-satsen krävs vanligtvis för SELECT-instruktionen. Undantaget är när inga tabellkolumner visas och de enda objekt som visas är literaler eller variabler eller aritmetiska uttryck.

I den här artikeln beskrivs även följande nyckelord som kan användas i FROM-satsen:

Transact-SQL syntaxkonventioner

Syntax

Syntax för SQL Server, Azure SQL Database och Fabric SQL Database:

[ 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

Syntax för 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

Syntax för 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

Argument

<table_source>

Anger en tabell, vy, tabellvariabel eller härledd tabellkälla, med eller utan alias, som ska användas i Transact-SQL-instruktionen. Upp till 256 tabellkällor kan användas i en -instruktion, även om gränsen varierar beroende på tillgängligt minne och komplexiteten i andra uttryck i frågan. Enskilda frågor kanske inte stöder upp till 256 tabellkällor.

Not

Frågeprestanda kan drabbas av många tabeller som refereras i en fråga. Kompilerings- och optimeringstiden påverkas också av ytterligare faktorer. Dessa inkluderar förekomsten av index och indexerade vyer på varje <table_source> och storleken på <select_list> i SELECT-instruktionen.

Ordningen på tabellkällor efter nyckelordet FROM påverkar inte den resultatuppsättning som returneras. SQL Server returnerar fel när dubbletter av namn visas i FROM-satsen.

table_or_view_name

Namnet på en tabell eller vy.

Om tabellen eller vyn finns i en annan databas på samma instans av SQL Server använder du ett fullständigt kvalificerat namn i formuläret databas.schema.object_name.

Om tabellen eller vyn finns utanför SQL Serverl-instansen använder du ett namn i fyra delar i formuläret linked_server.katalog.schema.objekt. Mer information finns i sp_addlinkedserver (Transact-SQL). Ett namn i fyra delar som skapas med hjälp av funktionen OPENDATASOURCE som serverdel av namnet kan också användas för att ange fjärrtabellkällan. När OPENDATASOURCE har angetts kanske database_name och schema_name inte gäller för alla datakällor och omfattas av funktionerna hos OLE DB-providern som har åtkomst till fjärrobjektet.

[AS] table_alias

Ett alias för table_source som kan användas antingen för enkelhetens skull eller för att särskilja en tabell eller vy i en självkoppling eller underfråga. Ett alias är ofta ett förkortat tabellnamn som används för att referera till specifika kolumner i tabellerna i en koppling. Om samma kolumnnamn finns i mer än en tabell i kopplingen kan SQL Server kräva att kolumnnamnet kvalificeras av ett tabellnamn, vynamn eller alias för att särskilja dessa kolumner. Tabellnamnet kan inte användas om ett alias har definierats.

När en härledd tabell, raduppsättning eller tabellvärdesfunktion eller operatorsats (till exempel PIVOT eller UNPIVOT) används, är den obligatoriska table_alias i slutet av satsen det associerade tabellnamnet för alla kolumner, inklusive grupperingskolumner, som returneras.

MED (<table_hint> )

Anger att frågeoptimeraren använder en optimerings- eller låsningsstrategi med den här tabellen och för den här instruktionen. Mer information finns i Tabelltips (Transact-SQL).

rowset_function

gäller för: SQL Server och SQL Database.

Anger en av raduppsättningsfunktionerna, till exempel OPENROWSET, som returnerar ett objekt som kan användas i stället för en tabellreferens. Mer information om en lista över raduppsättningsfunktioner finns i Rowset Functions (Transact-SQL).

Om du använder funktionerna OPENROWSET och OPENQUERY för att ange ett fjärrobjekt beror det på funktionerna i OLE DB-providern som har åtkomst till objektet.

bulk_column_alias

gäller för: SQL Server och SQL Database.

Ett valfritt alias för att ersätta ett kolumnnamn i resultatuppsättningen. Kolumnalias tillåts endast i SELECT-instruktioner som använder funktionen OPENROWSET med alternativet BULK. När du använder bulk_column_aliasanger du ett alias för varje tabellkolumn i samma ordning som kolumnerna i filen.

Not

Det här aliaset åsidosätter attributet NAME i KOLUMN-elementen i en XML-formatfil, om det finns.

user_defined_function

Anger en tabellvärdesfunktion.

OPENXML-<openxml_clause>

gäller för: SQL Server och SQL Database.

Ger en raduppsättningsvy över ett XML-dokument. Mer information finns i OPENXML (Transact-SQL).

derived_table

En underfråga som hämtar rader från databasen. derived_table används som indata till den yttre frågan.

derived_table kan använda funktionen Transact-SQL tabellvärdekonstruktor för att ange flera rader. Till exempel SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. Mer information finns i Table Value Constructor (Transact-SQL).

column_alias

Ett valfritt alias för att ersätta ett kolumnnamn i resultatuppsättningen för den härledda tabellen. Inkludera ett kolumnalias för varje kolumn i urvalslistan och omslut den fullständiga listan med kolumnalias inom parenteser.

table_or_view_name FÖR SYSTEM_TIME <system_time>

gäller för: SQL Server 2016 (13.x) och senare versioner och SQL Database.

Anger att en specifik version av data returneras från den angivna tidstabellen och dess länkade systemversionshistoriktabell

TABLESAMPLE-sats

gäller för: SQL Server, SQL Database och Azure Synapse Analytics dedikerade SQL-pooler

Anger att ett exempel på data från tabellen returneras. Exemplet kan vara ungefärligt. Den här satsen kan användas i valfri primär eller ansluten tabell i en SELECT- eller UPDATE-instruktion. TABLESAMPLE kan inte anges med vyer.

Not

När du använder TABLESAMPLE mot databaser som uppgraderas till SQL Server, är databasens kompatibilitetsnivå inställd på 110 eller högre. PIVOT tillåts inte i en rekursiv CTE-fråga (Common Table Expression). Mer information finns i ALTER DATABASE Compatibility Level (Transact-SQL).

SYSTEM

En implementeringsberoende samplingsmetod som anges av ISO-standarder. I SQL Server är detta den enda tillgängliga samplingsmetoden och tillämpas som standard. SYSTEM tillämpar en sidbaserad samplingsmetod där en slumpmässig uppsättning sidor från tabellen väljs för exemplet, och alla rader på dessa sidor returneras som exempeldeluppsättning.

sample_number

Ett exakt eller ungefärligt konstant numeriskt uttryck som representerar procent eller antal rader. När det anges med PERCENT konverteras sample_number implicit till ett flyttal värde. annars konverteras den till bigint. PERCENT är standardvärdet.

PROCENT

Anger att en sample_number procent av raderna i tabellen ska hämtas från tabellen. När PERCENT har angetts returnerar SQL Server ungefär den procent som angetts. När PERCENT anges måste sample_number-uttrycket utvärderas till ett värde från 0 till 100.

RADER

Anger att cirka sample_number rader hämtas. När RADER har angetts returnerar SQL Server en uppskattning av antalet angivna rader. När RADER anges måste uttrycket sample_number utvärderas till ett heltalsvärde som är större än noll.

REPETERBAR

Anger att det valda exemplet kan returneras igen. När det anges med samma repeat_seed värde returnerar SQL Server samma delmängd av rader så länge inga ändringar har gjorts i några rader i tabellen. När det anges med ett annat repeat_seed värde returnerar SQL Server troligen ett annat exempel på raderna i tabellen. Följande åtgärder i tabellen betraktas som ändringar: infoga, uppdatera, ta bort, återskapa index eller defragmentera index och återställa eller koppla databasen.

repeat_seed

Ett konstant heltalsuttryck som används av SQL Server för att generera ett slumpmässigt tal. repeat_seed är bigint. Om repeat_seed inte anges tilldelar SQL Server ett slumpmässigt värde. För ett specifikt repeat_seed värde är samplingsresultatet alltid detsamma om inga ändringar har tillämpats på tabellen. Uttrycket repeat_seed måste utvärderas till ett heltal som är större än noll.

Ansluten tabell

En ansluten tabell är en resultatuppsättning som är produkten av två eller flera tabeller. För flera kopplingar använder du parenteser för att ändra kopplingarnas naturliga ordning.

Kopplingstyp

Anger typen av kopplingsåtgärd.

INRE

Anger att alla matchande par med rader returneras. Tar bort omatchade rader från båda tabellerna. När ingen kopplingstyp har angetts är detta standardvärdet.

FULL [ YTTRE ]

Anger att en rad från antingen den vänstra eller högra tabellen som inte uppfyller kopplingsvillkoret ingår i resultatuppsättningen, och utdatakolumner som motsvarar den andra tabellen är inställda på NULL. Detta är utöver alla rader som vanligtvis returneras av den inre kopplingen.

VÄNSTER [ YTTRE ]

Anger att alla rader från den vänstra tabellen som inte uppfyller kopplingsvillkoret ingår i resultatuppsättningen, och utdatakolumner från den andra tabellen är inställda på NULL utöver alla rader som returneras av den inre kopplingen.

HÖGER [ YTTRE ]

Anger att alla rader från den högra tabellen som inte uppfyller kopplingsvillkoret ingår i resultatuppsättningen, och utdatakolumner som motsvarar den andra tabellen är inställda på NULL, förutom alla rader som returneras av den inre kopplingen.

Kopplingstips

För SQL Server och SQL Database anger att SQL Server-frågeoptimeraren använder ett kopplingstips eller en körningsalgoritm per koppling som anges i frågan FROM-satsen. Mer information finns i Join Hints (Transact-SQL).

För Azure Synapse Analytics and Analytics Platform System (PDW) gäller dessa kopplingstips för INRE kopplingar i två distributionskompatibla kolumner. De kan förbättra frågeprestanda genom att begränsa mängden dataförflyttning som inträffar under frågebearbetningen. De tillåtna kopplingstipsen för Azure Synapse Analytics and Analytics Platform System (PDW) är följande:

REDUCERA

Minskar antalet rader som ska flyttas för tabellen till höger om kopplingen för att göra två distributionskompatibla tabeller kompatibla. REDUCE-tipset kallas även för ett tips om halvkoppling.

REPLIKERA

Gör att värdena i kopplingskolumnen från tabellen till höger om kopplingen replikeras till alla noder. Tabellen till vänster är ansluten till den replikerade versionen av dessa kolumner.

OMFÖRDELA

Tvingar två datakällor att distribueras på kolumner som anges i JOIN-satsen. För en distribuerad tabell utför Analytics Platform System (PDW) en shuffle-flytt. För en replikerad tabell utför Analytics Platform System (PDW) en trimningsflytt. Information om dessa flytttyper finns i avsnittet "DMS Query Plan Operations" i artikeln "Understanding Query Plans" (Förstå frågeplaner) i produktdokumentationen för Analytics Platform System (PDW). Det här tipset kan förbättra prestanda när frågeplanen använder en sändningsflytt för att lösa en distributionskompatibel koppling.

ANSLUTA

Anger att den angivna kopplingsåtgärden ska ske mellan de angivna tabellkällorna eller vyerna.

PÅ <search_condition>

Anger det villkor som kopplingen baseras på. Villkoret kan ange valfritt predikat, även om kolumner och jämförelseoperatorer används ofta, till exempel:

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

När villkoret anger kolumner behöver kolumnerna inte ha samma namn eller samma datatyp. Men om datatyperna inte är samma måste de antingen vara kompatibla eller typer som SQL Server implicit kan konvertera. Om datatyperna inte kan konverteras implicit måste villkoret explicit konvertera datatypen med hjälp av funktionen CONVERT.

Det kan finnas predikat som bara omfattar en av de anslutna tabellerna i ON-satsen. Sådana predikat kan också finnas i WHERE-satsen i frågan. Även om placeringen av sådana predikat inte gör någon skillnad för INRE kopplingar, kan de orsaka ett annat resultat när YTTRE kopplingar är inblandade. Det beror på att predikaten i ON-satsen tillämpas på tabellen före kopplingen, medan WHERE-satsen tillämpas semantiskt på resultatet av kopplingen.

Mer information om sökvillkor och predikat finns i Sökvillkor (Transact-SQL).

KORSKOPPLING

Anger korsprodukten för två tabeller. Returnerar samma rader som om ingen WHERE-sats angavs i en gammal stil, icke-SQL-92-liknande koppling.

left_table_source { CROSS | OUTER } APPLY right_table_source

Anger att right_table_source för APPLY-operatorn utvärderas mot varje rad i left_table_source. Den här funktionen är användbar när right_table_source innehåller en tabellvärdesfunktion som tar kolumnvärden från left_table_source som ett av argumenten.

Antingen CROSS eller OUTER måste anges med APPLY. När CROSS anges skapas inga rader när right_table_source utvärderas mot en angiven rad i left_table_source och returnerar en tom resultatuppsättning.

När OUTER anges skapas en rad för varje rad i left_table_source även när right_table_source utvärderas mot den raden och returnerar en tom resultatuppsättning.

Mer information finns i avsnittet Kommentarer.

left_table_source

En tabellkälla enligt definitionen i föregående argument. Mer information finns i avsnittet Anmärkningar.

right_table_source

En tabellkälla enligt definitionen i föregående argument. Mer information finns i avsnittet Anmärkningar.

PIVOT-sats

table_source PIVOT-<pivot_clause>

Anger att table_source pivoteras baserat på pivot_column. table_source är ett tabell- eller tabelluttryck. Utdata är en tabell som innehåller alla kolumner i table_source förutom pivot_column och value_column. Kolumnerna i table_source, förutom pivot_column och value_column, kallas grupperingskolumner för pivotoperatorn. Mer information om PIVOT och UNPIVOT finns i Using PIVOT and UNPIVOT.

PIVOT utför en grupperingsåtgärd i indatatabellen med avseende på grupperingskolumnerna och returnerar en rad för varje grupp. Dessutom innehåller utdata en kolumn för varje värde som anges i column_list som visas i pivot_column för input_table.

Mer information finns i avsnittet Anmärkningar som följer.

aggregate_function

En system- eller användardefinierad aggregeringsfunktion som accepterar en eller flera indata. Aggregeringsfunktionen ska vara invariant för null-värden. En aggregeringsfunktion som är invariant för null-värden tar inte hänsyn till nullvärden i gruppen medan den utvärderar det aggregerade värdet.

Systemaggregeringsfunktionen COUNT(*) är inte tillåten.

value_column

Värdekolumnen för PIVOT-operatorn. När den används med UNPIVOT kan value_column inte vara namnet på en befintlig kolumn i indata table_source.

FÖR pivot_column

PIVOT-operatorns pivotkolumn. pivot_column måste vara av en typ som implicit eller explicit kan konverteras till nvarchar(). Den här kolumnen kan inte bild eller rowversion.

När UNPIVOT används är pivot_column namnet på den utdatakolumn som begränsas från table_source. Det kan inte finnas någon befintlig kolumn i table_source med det namnet.

IN ( column_list )

I PIVOT-satsen listar du värdena i pivot_column som blir kolumnnamnen för utdatatabellen. Listan kan inte ange några kolumnnamn som redan finns i indata table_source som pivoteras.

I UNPIVOT-satsen visas kolumnerna i table_source som är begränsade till en enda pivot_column.

table_alias

Aliasnamnet för utdatatabellen. pivot_table_alias måste anges.

UNPIVOT-<unpivot_clause>

Anger att indatatabellen begränsas från flera kolumner i column_list till en enda kolumn med namnet pivot_column. Mer information om PIVOT och UNPIVOT finns i Using PIVOT and UNPIVOT.

FRÅN och med <date_time>

gäller för: SQL Server 2016 (13.x) och senare versioner och SQL Database.

Returnerar en tabell med en post för varje rad som innehåller de värden som var faktiska (aktuella) vid den angivna tidpunkten tidigare. Internt utförs en union mellan den tidsmässiga tabellen och dess historiktabell och resultaten filtreras för att returnera värdena på raden som var giltig vid den tidpunkt som anges av parametern <date_time>. Värdet för en rad anses vara giltigt om värdet för system_start_time_column_name är mindre än eller lika med parametervärdet <date_time> och system_end_time_column_name värdet är större än parametervärdet <date_time>.

FRÅN <start_date_time> till <end_date_time>

gäller för: SQL Server 2016 (13.x) och senare versioner och SQL Database.

Returnerar en tabell med värdena för alla postversioner som var aktiva inom det angivna tidsintervallet, oavsett om de började vara aktiva innan parametervärdet <start_date_time> för FROM-argumentet eller upphörde att vara aktivt efter <end_date_time> parametervärdet för TO-argumentet. Internt utförs en union mellan den temporala tabellen och dess historiktabell och resultaten filtreras för att returnera värdena för alla radversioner som var aktiva när som helst under det angivna tidsintervallet. Rader som blev aktiva exakt på den nedre gränsen som definierats av FROM-slutpunkten inkluderas och rader som blev aktiva exakt på den övre gränsen som definierats av TO-slutpunkten inkluderas inte.

MELLAN <start_date_time> OCH <end_date_time>

gäller för: SQL Server 2016 (13.x) och senare versioner och SQL Database.

Samma som ovan i FROM <start_date_time> TO <end_date_time> beskrivning, förutom att den innehåller rader som blev aktiva på den övre gränsen som definierats av <end_date_time> slutpunkten.

INNESLUTEN I (<start_date_time> , <end_date_time>)

gäller för: SQL Server 2016 (13.x) och senare versioner och SQL Database.

Returnerar en tabell med värdena för alla postversioner som öppnades och stängdes inom det angivna tidsintervallet som definierats av de två datetime-värdena för argumentet CONTAINED IN. Rader som blev aktiva exakt på den nedre gränsen eller upphörde att vara aktiva exakt på den övre gränsen inkluderas.

ALLA

Returnerar en tabell med värdena från alla rader från både den aktuella tabellen och historiktabellen.

Anmärkningar

FROM-satsen stöder SQL-92-syntaxen för anslutna tabeller och härledda tabeller. SQL-92-syntaxen innehåller operatorerna INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER och CROSS join.

UNION och JOIN i en FROM-sats stöds i vyer och i härledda tabeller och underfrågor.

En självkoppling är en tabell som är ansluten till sig själv. Infoga eller uppdatera åtgärder som baseras på en självkoppling följer ordningen i FROM-satsen.

Eftersom SQL Server tar hänsyn till distributions- och kardinalitetsstatistik från länkade servrar som tillhandahåller kolumndistributionsstatistik, krävs inte tipset fjärranslutning för att tvinga fram utvärdering av en koppling via fjärranslutning. SQL Server-frågeprocessorn tar hänsyn till fjärrstatistik och avgör om en fjärranslutningsstrategi är lämplig. Tips för fjärranslutning är användbart för leverantörer som inte tillhandahåller kolumndistributionsstatistik.

Använda APPLY

Både de vänstra och högra operanderna för APPLY-operatorn är tabelluttryck. Den största skillnaden mellan dessa operander är att right_table_source kan använda en tabellvärdesfunktion som tar en kolumn från left_table_source som ett av argumenten i funktionen. left_table_source kan innehålla tabellvärdesfunktioner, men den kan inte innehålla argument som är kolumner från right_table_source.

APPLY-operatorn fungerar på följande sätt för att skapa tabellkällan för FROM-satsen:

  1. Utvärderar right_table_source mot varje rad i left_table_source för att skapa rader.

    Värdena i right_table_source beror på left_table_source. right_table_source kan representeras ungefär så här: TVF(left_table_source.row), där TVF är en tabellvärdesfunktion.

  2. Kombinerar de resultatuppsättningar som skapas för varje rad i utvärderingen av right_table_source med left_table_source genom att utföra en UNION ALL-åtgärd.

    Listan över kolumner som skapas av resultatet av APPLY-operatorn är den uppsättning kolumner från left_table_source som kombineras med listan över kolumner från right_table_source.

Använda PIVOT och UNPIVOT

pivot_column och value_column är grupperingskolumner som används av PIVOT-operatorn. PIVOT följer följande process för att hämta resultatuppsättningen för utdata:

  1. Utför en GROUP BY på sin input_table mot grupperingskolumnerna och skapar en utdatarad för varje grupp.

    Grupperingskolumnerna i utdataraden hämtar motsvarande kolumnvärden för den gruppen i input_table.

  2. Genererar värden för kolumnerna i kolumnlistan för varje utdatarad genom att utföra följande:

    1. Gruppera dessutom de rader som genererades i GROUP BY i föregående steg mot pivot_column.

      För varje utdatakolumn i column_listväljer du en undergrupp som uppfyller villkoret:

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

    2. aggregate_function utvärderas mot value_column i den här undergruppen och resultatet returneras som värdet för motsvarande output_column. Om undergruppen är tom genererar SQL Server ett null-värde för den output_column. Om aggregeringsfunktionen är COUNT och undergruppen är tom returneras noll (0).

Not

Kolumnidentifierarna i UNPIVOT-satsen följer katalogsortering. För SQL Database är sorteringen alltid SQL_Latin1_General_CP1_CI_AS. För SQL Server-delvis inneslutna databaser är sorteringen alltid Latin1_General_100_CI_AS_KS_WS_SC. Om kolumnen kombineras med andra kolumner krävs en sorteringssats (COLLATE DATABASE_DEFAULT) för att undvika konflikter.

Mer information om pivotering och UNPIVOT inklusive exempel finns i Using PIVOT and UNPIVOT.

Behörigheter

Kräver behörigheter för instruktionen DELETE, SELECT eller UPDATE.

Exempel

A. Använda en FROM-sats

I följande exempel hämtas kolumnerna TerritoryID och Name från SalesTerritory-tabellen i exempeldatabasen AdventureWorks2022.

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

Här är resultatuppsättningen.

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. Använd tipsen för TABLOCK- och HOLDLOCK-optimeraren

Följande partiella transaktion visar hur du placerar ett explicit delat tabelllås på Employee och hur du läser indexet. Låset hålls under hela transaktionen.

BEGIN TRANSACTION

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

C. Använda SQL-92 CROSS JOIN-syntaxen

I följande exempel returneras korsprodukten för de två tabellerna Employee och Department i databasen AdventureWorks2022. En lista över alla möjliga kombinationer av BusinessEntityID rader och alla Department namnrader returneras.

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. Använd SQL-92 FULL OUTER JOIN-syntaxen

I följande exempel returneras produktnamnet och eventuella motsvarande försäljningsorder i tabellen SalesOrderDetail i databasen AdventureWorks2022. Den returnerar även alla försäljningsorder som inte har någon produkt i tabellen Product och alla produkter med en annan försäljningsorder än den som anges i tabellen 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. Använd syntaxen för SQL-92 LEFT OUTER JOIN

I följande exempel kopplas två tabeller till ProductID och de omatchade raderna bevaras från den vänstra tabellen. Tabellen Product matchas med tabellen SalesOrderDetail på de ProductID kolumnerna i varje tabell. Alla produkter, beställda och inte beställda, visas i resultatuppsättningen.

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. Använd SYNTAXEN FÖR INRE KOPPLING I SQL-92

I följande exempel returneras alla produktnamn och försäljningsorder-ID:t.

-- 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. Använda SQL-92 RIGHT OUTER JOIN-syntaxen

I följande exempel kopplas två tabeller till TerritoryID och de omatchade raderna bevaras från den högra tabellen. Tabellen SalesTerritory matchas med tabellen SalesPerson i kolumnen TerritoryID i varje tabell. Alla säljare visas i resultatuppsättningen, oavsett om de har tilldelats ett område eller inte.

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. Använda HASH- och MERGE-kopplingstips

I följande exempel utförs en tretabellskoppling mellan tabellerna Product, ProductVendoroch Vendor för att skapa en lista över produkter och deras leverantörer. Frågeoptimeraren ansluter Product och ProductVendor (p och pv) med hjälp av en MERGE-koppling. Därefter är resultatet av Product- och ProductVendor MERGE-kopplingen (p och pv) HASH-kopplat till tabellen Vendor för att producera (p och pv) och v.

Viktig

När ett kopplingstips har angetts är det inre nyckelordet inte längre valfritt och måste uttryckligen anges för att en INRE KOPPLING ska utföras.

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;

Jag. Använda en härledd tabell

I följande exempel används en härledd tabell, en SELECT-instruktion efter FROM-satsen, för att returnera för- och efternamnen för alla anställda och de städer där de bor.

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. Använda TABLESAMPLE för att läsa data från ett exempel på rader i en tabell

I följande exempel används TABLESAMPLE i FROM-satsen för att returnera ungefär 10 procent av alla rader i tabellen Customer.

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

K. Använda APPLY

I följande exempel förutsätts att följande tabeller och tabellvärdesfunktion finns i databasen:

Objektnamn Kolumnnamn
Avdelningar DeptID, DivisionID, DeptName, DeptMgrID
EmpMgr MgrID, EmpID
Anställda EmpID, EmpLastName, EmpFirstName, EmpSalary
GetReports(MgrID) EmpID, EmpLastName, EmpSalary

Funktionen GetReports tabellvärde returnerar listan över alla anställda som rapporterar direkt eller indirekt till den angivna MgrID.

I exemplet används APPLY för att returnera alla avdelningar och alla anställda på den avdelningen. Om en viss avdelning inte har några anställda returneras inga rader för den avdelningen.

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

Om du vill att frågan ska skapa rader för dessa avdelningar utan anställda, vilket ger null-värden för kolumnerna EmpID, EmpLastName och EmpSalary använder du i stället OUTER APPLY.

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

L. Använda CROSS APPLY

I följande exempel hämtas en ögonblicksbild av alla frågeplaner som finns i plancachen genom att fråga sys.dm_exec_cached_plans dynamisk hanteringsvy för att hämta planhandtagen för alla frågeplaner i cacheminnet. Sedan anges CROSS APPLY-operatorn för att skicka planhandtagen till sys.dm_exec_query_plan. XML Showplan-utdata för varje plan som för närvarande finns i plancachen finns i kolumnen query_plan i tabellen som returneras.

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. Använd FÖR SYSTEM_TIME

gäller för: SQL Server 2016 (13.x) och senare versioner och SQL Database.

I följande exempel används argumentet FOR SYSTEM_TIME AS OF date_time_literal_or_variable för att returnera tabellrader som var faktiska (aktuella) från och med den 1 januari 2014.

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

I följande exempel används argumentet FOR SYSTEM_TIME FROM date_time_literal_or_variable TO date_time_literal_or_variable för att returnera alla rader som var aktiva under perioden som definierats som från och med den 1 januari 2013 och slutar med den 1 januari 2014, exklusive den övre gränsen.

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

I följande exempel används argumentet FOR SYSTEM_TIME BETWEEN date_time_literal_or_variable AND date_time_literal_or_variable för att returnera alla rader som var aktiva under perioden som definierats som från och med den 1 januari 2013 och slutar med den 1 januari 2014, inklusive den övre gränsen.

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

I följande exempel används argumentet FOR SYSTEM_TIME CONTAINED IN (date_time_literal_or_variable, date_time_literal_or_variable) för att returnera alla rader som öppnades och stängdes under perioden som definierades som från och med 1 januari 2013 och slutar med 1 januari 2014.

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

I följande exempel används en variabel i stället för en literal för att ange datumgränsvärdena för frågan.

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;

Exempel: Azure Synapse Analytics and Analytics Platform System (PDW)

N. Använd syntaxen för INRE KOPPLING

I följande exempel returneras kolumnerna SalesOrderNumber, ProductKeyoch EnglishProductName från tabellerna FactInternetSales och DimProduct där kopplingsnyckeln, ProductKey, matchar i båda tabellerna. Kolumnerna SalesOrderNumber och EnglishProductName finns endast i en av tabellerna, så det är inte nödvändigt att ange tabellaliaset med dessa kolumner, som visas. dessa alias ingår för läsbarhet. Ordet AS- innan ett aliasnamn krävs inte, men rekommenderas för läsbarhet och för att följa ANSI-standarden.

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

Eftersom nyckelordet INNER inte krävs för inre kopplingar kan samma fråga skrivas som:

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

En WHERE-sats kan också användas med den här frågan för att begränsa resultatet. I det här exemplet begränsas resultatet till SalesOrderNumber värden som är högre än "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. Använd syntaxen VÄNSTER YTTRE KOPPLING OCH HÖGER YTTRE KOPPLING

I följande exempel kopplas tabellerna FactInternetSales och DimProduct till de ProductKey kolumnerna. Den vänstra yttre kopplingssyntaxen bevarar de omatchade raderna från den vänstra tabellen (FactInternetSales). Eftersom den FactInternetSales tabellen inte innehåller några ProductKey värden som inte matchar tabellen DimProduct returnerar den här frågan samma rader som det första inre kopplingsexemplet tidigare i den här artikeln.

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

Den här frågan kan också skrivas utan nyckelordet OUTER.

I högra yttre kopplingar bevaras de omatchade raderna från den högra tabellen. I följande exempel returneras samma rader som det vänstra yttre kopplingsexemplet ovan.

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

Följande fråga använder den DimSalesTerritory tabellen som den vänstra tabellen i en vänster yttre koppling. Den hämtar SalesOrderNumber värden från tabellen FactInternetSales. Om det inte finns några beställningar för en viss SalesTerritoryKeyreturnerar frågan en NULL för SalesOrderNumber för den raden. Den här frågan sorteras efter kolumnen SalesOrderNumber, så att alla NULL:er i den här kolumnen visas överst i resultatet.

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

Den här frågan kan skrivas om med en höger yttre koppling för att hämta samma resultat:

-- 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. Använd syntaxen FÖR FULLSTÄNDIG YTTRE KOPPLING

I följande exempel visas en fullständig yttre koppling som returnerar alla rader från båda kopplade tabellerna men returnerar NULL för värden som inte matchar från den andra tabellen.

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

Den här frågan kan också skrivas utan nyckelordet 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. Använda CROSS JOIN-syntaxen

I följande exempel returneras korsprodukten för tabellerna FactInternetSales och DimSalesTerritory. En lista över alla möjliga kombinationer av SalesOrderNumber och SalesTerritoryKey returneras. Observera avsaknaden av ON-satsen i korskopplingsfrågan.

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

R. Använda en härledd tabell

I följande exempel används en härledd tabell (en SELECT-instruktion efter FROM-satsen) för att returnera kolumnerna CustomerKey och LastName för alla kunder i tabellen DimCustomer med BirthDate värden senare än den 1 januari 1970 och efternamnet "Smith".

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

S. Exempel på REDUCE-kopplingstips

I följande exempel används REDUCE kopplingstips för att ändra bearbetningen av den härledda tabellen i frågan. När du använder REDUCE kopplingstipset i den här frågan projiceras, replikeras och görs fis.ProductKey distinkt och kopplas sedan till DimProduct under blandning av DimProduct på ProductKey. Den resulterande härledda tabellen distribueras på 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. Exempel på REPLIKERingskopplingstips

I nästa exempel visas samma fråga som i föregående exempel, förutom att en REPLICATE kopplingstips används i stället för REDUCE kopplingstips. Användningen av REPLICATE tips gör att värdena i kolumnen ProductKey (sammanfoga) från FactInternetSales-tabellen replikeras till alla noder. Tabellen DimProduct är kopplad till den replikerade versionen av dessa värden.

-- 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. Använd REDISTRIBUTE-tipset för att garantera en shuffle-flytt för en distributionskompatibel koppling

Följande fråga använder REDISTRIBUTE-frågetipset för en distributionskompatibel koppling. Detta garanterar att frågeoptimeraren använder en Shuffle-flytt i frågeplanen. Detta garanterar också att frågeplanen inte använder en Broadcast-flytt, vilket flyttar en distribuerad tabell till en replikerad tabell.

I följande exempel tvingar REDISTRIBUTE-tipset en Shuffle-flytt i tabellen FactInternetSales eftersom ProductKey är distributionskolumnen för DimProduct och inte är distributionskolumnen för 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. Använda TABLESAMPLE för att läsa data från ett exempel på rader i en tabell

I följande exempel används TABLESAMPLE i FROM-satsen för att returnera ungefär 10 procent av alla rader i tabellen Customer.

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

Relaterat innehåll