Wann sollte ich Cross anwenden über Inner Join verwenden?
Was ist der Hauptzweck der Verwendung von CROSS APPLY ?
Ich habe gelesen (vage durch Posts im Internet), dass cross apply bei der Auswahl von großen Datensätzen effizienter sein kann, wenn Sie partitionieren. (Paging kommt mir in den Sinn)
Ich weiß auch, dass CROSS APPLYkeine UDF als rechte Tabelle benötigt.
In den meisten INNER JOIN-Abfragen (Eins-zu-Viele-Beziehungen) könnte ich sie für die Verwendung von CROSS APPLY umschreiben, aber sie geben mir immer gleichwertige Ausführungspläne.
Kann mir jemand ein gutes Beispiel geben, wann CROSS APPLY in den Fällen einen Unterschied macht, in denen INNER JOIN auch funktioniert?
Bearbeiten:
Hier ein triviales Beispiel, bei dem die Ausführungspläne genau gleich sind. (Zeigen Sie mir, wo sie sich unterscheiden und wo cross apply schneller/effizienter ist)
create table Company (
companyId int identity(1,1)
, companyName varchar(100)
, zipcode varchar(10)
, constraint PK_Company primary key (companyId)
)
GO
create table Person (
personId int identity(1,1)
, personName varchar(100)
, companyId int
, constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
, constraint PK_Person primary key (personId)
)
GO
insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'
insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3
/* using CROSS APPLY */
select *
from Person p
cross apply (
select *
from Company c
where p.companyid = c.companyId
) Czip
/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId
Kann mir jemand ein gutes Beispiel geben, wann CROSS APPLY in den Fällen, in denen INNER JOIN ebenfalls funktioniert, einen Unterschied macht?
Einen ausführlichen Leistungsvergleich finden Sie in dem Artikel in meinem Blog:
CROSS APPLY funktioniert besser bei Dingen, die keine einfache JOIN-Bedingung haben.
Dieser wählt 3 letzte Datensätze aus t2 für jeden Datensatz aus t1 aus:
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
Es kann nicht leicht mit einer INNER JOIN-Bedingung formuliert werden.
Sie könnten wahrscheinlich so etwas mit CTE und der Fensterfunktion tun:
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT t1.*, t2o.*
FROM t1
INNER JOIN
t2o
ON t2o.t1_id = t1.id
AND t2o.rn <= 3
Dies ist jedoch weniger lesbar und wahrscheinlich weniger effizient.
Update:
Gerade nachgeguckt.
master ist eine Tabelle mit ungefähr 20,000,000-Datensätzen mit einem PRIMARY KEY für id.
Diese Abfrage:
WITH q AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM master
),
t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
JOIN q
ON q.rn <= t.id
läuft fast 30 Sekunden, während diese:
WITH t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
CROSS APPLY
(
SELECT TOP (t.id) m.*
FROM master m
ORDER BY
id
) q
ist sofort.
cross apply ermöglicht es Ihnen manchmal, Dinge zu tun, die Sie nicht mit inner join machen können.
Beispiel (ein Syntaxfehler):
select F.* from sys.objects O
inner join dbo.myTableFun(O.name) F
on F.schema_id= O.schema_id
Dies ist ein Syntaxfehler, da Tabellenfunktionen bei Verwendung mit inner join nur Variablen oder Konstanten als Parameter verwenden können. (Das heißt, der Tabellenfunktionsparameter kann nicht von der Spalte einer anderen Tabelle abhängen.)
Jedoch:
select F.* from sys.objects O
cross apply ( select * from dbo.myTableFun(O.name) ) F
where F.schema_id= O.schema_id
Das ist legal.
Edit: Oder alternativ kürzere Syntax: (von ErikE)
select F.* from sys.objects O
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id
Bearbeiten:
Hinweis: Informix 12.10 xC2 + hat Lateral abgeleitete Tabellen und Postgresql (9.3+) hat Lateral Subqueries , die ähnlich genutzt werden können.
Stellen Sie sich vor, Sie haben zwei Tische.
MASTER TABLE
x------x--------------------x
| Id | Name |
x------x--------------------x
| 1 | A |
| 2 | B |
| 3 | C |
x------x--------------------x
DETAILS TABELLE
x------x--------------------x-------x
| Id | PERIOD | QTY |
x------x--------------------x-------x
| 1 | 2014-01-13 | 10 |
| 1 | 2014-01-11 | 15 |
| 1 | 2014-01-12 | 20 |
| 2 | 2014-01-06 | 30 |
| 2 | 2014-01-08 | 40 |
x------x--------------------x-------x
In vielen Situationen müssen wir INNER JOIN durch CROSS APPLY ersetzen.
1. Verknüpfen Sie zwei Tabellen basierend auf TOP n Ergebnissen.
Überlegen Sie, ob wir Id und Name aus Master und die letzten beiden Daten für jede Id aus Details table auswählen müssen.
SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
SELECT TOP 2 ID, PERIOD,QTY
FROM DETAILS D
ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID
Die obige Abfrage erzeugt das folgende Ergebnis.
x------x---------x--------------x-------x
| Id | Name | PERIOD | QTY |
x------x---------x--------------x-------x
| 1 | A | 2014-01-13 | 10 |
| 1 | A | 2014-01-12 | 20 |
x------x---------x--------------x-------x
Sehen Sie, es wurden Ergebnisse für die letzten beiden Daten mit Id der letzten beiden Daten generiert und diese Datensätze dann nur in der äußeren Abfrage von Id verknüpft, was falsch ist. Um dies zu erreichen, müssen wir CROSS APPLY verwenden.
SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
SELECT TOP 2 ID, PERIOD,QTY
FROM DETAILS D
WHERE M.ID=D.ID
ORDER BY CAST(PERIOD AS DATE)DESC
)D
und bildet das folgende Ergebnis.
x------x---------x--------------x-------x
| Id | Name | PERIOD | QTY |
x------x---------x--------------x-------x
| 1 | A | 2014-01-13 | 10 |
| 1 | A | 2014-01-12 | 20 |
| 2 | B | 2014-01-08 | 40 |
| 2 | B | 2014-01-06 | 30 |
x------x---------x--------------x-------x
So funktioniert das. Die Abfrage in CROSS APPLY kann auf die äußere Tabelle verweisen, wobei INNER JOIN dies nicht tun kann (es wird ein Kompilierungsfehler ausgegeben). Wenn die letzten beiden Daten gefunden werden, erfolgt der Beitritt in CROSS APPLY, d. H. WHERE M.ID=D.ID.
2. Wenn wir INNER JOIN-Funktionalität mit Funktionen benötigen.
CROSS APPLY kann als Ersatz für INNER JOIN verwendet werden, wenn das Ergebnis aus Master-Tabelle und einer function abgerufen werden muss.
SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C
Und hier ist die Funktion
CREATE FUNCTION FnGetQty
(
@Id INT
)
RETURNS TABLE
AS
RETURN
(
SELECT ID,PERIOD,QTY
FROM DETAILS
WHERE [email protected]
)
was zu folgendem Ergebnis führte
x------x---------x--------------x-------x
| Id | Name | PERIOD | QTY |
x------x---------x--------------x-------x
| 1 | A | 2014-01-13 | 10 |
| 1 | A | 2014-01-11 | 15 |
| 1 | A | 2014-01-12 | 20 |
| 2 | B | 2014-01-06 | 30 |
| 2 | B | 2014-01-08 | 40 |
x------x---------x--------------x-------x
ZUSÄTZLICHER VORTEIL VON CROSS APPLY
APPLY kann als Ersatz für UNPIVOT verwendet werden. Hier kann entweder CROSS APPLY oder OUTER APPLY verwendet werden, die austauschbar sind.
Stellen Sie sich vor, Sie haben die untenstehende Tabelle (mit dem Namen MYTABLE).
x------x-------------x--------------x
| Id | FROMDATE | TODATE |
x------x-------------x--------------x
| 1 | 2014-01-11 | 2014-01-13 |
| 1 | 2014-02-23 | 2014-02-27 |
| 2 | 2014-05-06 | 2014-05-30 |
| 3 | NULL | NULL |
x------x-------------x--------------x
Die Abfrage ist unten.
SELECT DISTINCT ID,DATES
FROM MYTABLE
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)
das bringt Ihnen das Ergebnis
x------x-------------x
| Id | DATES |
x------x-------------x
| 1 | 2014-01-11 |
| 1 | 2014-01-13 |
| 1 | 2014-02-23 |
| 1 | 2014-02-27 |
| 2 | 2014-05-06 |
| 2 | 2014-05-30 |
| 3 | NULL |
x------x-------------x
hier ist ein Beispiel, wenn CROSS APPLY einen großen Unterschied bei der Leistung macht:
Verwenden von CROSS APPLY zum Optimieren von Verknüpfungen unter ZWEITEN Bedingungen
Beachten Sie, dass Sie nicht nur innere Verknüpfungen ersetzen, sondern auch Code wiederverwenden können, z. B. das Abschneiden von Datumsangaben, ohne dass Sie eine Leistungsstrafe zahlen müssen, wenn Sie skalare UDFs verwenden. Beispiel: Berechnung des dritten Mittwochs des Monats mit Inline-UDFs
Mir scheint, dass CROSS APPLY bei der Arbeit mit berechneten Feldern in komplexen/verschachtelten Abfragen eine gewisse Lücke füllen und diese einfacher und lesbarer machen kann.
Einfaches Beispiel: Sie verfügen über ein DoB und möchten mehrere altersbezogene Felder darstellen, die sich auch auf andere Datenquellen (wie Beschäftigung) wie Alter, Altersgruppe, AlterAtHiring, MinimumRetirementDate usw. zur Verwendung in Ihrer Endbenutzeranwendung stützen (Excel PivotTables zum Beispiel).
Optionen sind begrenzt und selten elegant:
JOIN-Unterabfragen können auf der Grundlage der Daten in der übergeordneten Abfrage keine neuen Werte in das Dataset einfügen (es muss für sich alleine stehen).
UDFs sind ordentlich, aber langsam, da sie tendenziell Paralleloperationen verhindern. Und eine separate Entität zu sein kann eine gute Sache (weniger Code) oder eine schlechte Sache (wo ist der Code) sein.
Junction-Tabellen. Manchmal können sie funktionieren, aber schon bald nehmen Sie an Unterabfragen mit Tonnen von UNIONs teil. Großes Chaos.
Erstellen Sie eine weitere Ansicht für einen einzigen Zweck, vorausgesetzt, für Ihre Berechnungen sind keine Daten erforderlich, die in der Mitte Ihrer Hauptabfrage abgerufen werden.
Zwischentabellen. Ja ... das funktioniert normalerweise und ist oft eine gute Option, da sie indiziert und schnell sein können. Die Leistung kann jedoch auch dadurch beeinträchtigt werden, dass UPDATE-Anweisungen nicht parallel sind und es nicht zulässt, Formeln (Wiederverwendungsergebnisse) zu kaskadieren, um mehrere Felder innerhalb des Befehls zu aktualisieren gleiche Aussage. Und manchmal würden Sie es vorziehen, Dinge in einem Durchgang zu erledigen.
Verschachtelungsabfragen Ja, Sie können jederzeit Ihre gesamte Abfrage mit Klammern versehen und als Unterabfrage verwenden, auf der Sie Quelldaten und berechnete Felder gleichermaßen bearbeiten können. Aber du kannst das nur so oft machen, bevor es hässlich wird. Sehr hässlich.
Code wiederholen. Was ist der größte Wert von 3 langen (CASE ... ELSE ... END) Anweisungen? Das wird lesbar sein!
- Bitten Sie Ihre Kunden, die verdammten Dinge selbst zu berechnen.
Habe ich etwas verpasst? Wahrscheinlich, also zögern Sie nicht, zu kommentieren. Aber hey, CROSS APPLY ist in solchen Situationen wie ein Glücksfall: Sie fügen einfach eine einfache CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl und voilà hinzu! Ihr neues Feld ist jetzt praktisch einsatzbereit, so wie es in Ihren Quelldaten immer vorhanden war.
Mit CROSS APPLY eingeführte Werte können ...
- sie können dazu verwendet werden, ein oder mehrere berechnete Felder zu erstellen, ohne dass sich der Mix auf Leistungs-, Komplexitäts- oder Lesbarkeitsprobleme auswirkt
- wie bei JOINs können sich mehrere nachfolgende CROSS APPLY-Anweisungen auf sich selbst beziehen:
CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2 - sie können Werte verwenden, die von CROSS APPLY in nachfolgenden JOIN-Bedingungen eingegeben wurden
- Als Bonus gibt es den funktionellen Aspekt der Tabelle
Dang, es gibt nichts, was sie nicht tun können!
Cross Apply funktioniert auch mit einem XML-Feld gut. Wenn Sie Knotenwerte in Kombination mit anderen Feldern auswählen möchten.
Zum Beispiel, wenn Sie eine Tabelle haben, die etwas XML enthält
<root> <subnode1> <some_node value="1" /> <some_node value="2" /> <some_node value="3" /> <some_node value="4" /> </subnode1> </root>
Abfrage verwenden
SELECT
id as [xt_id]
,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
,node_attribute_value = [some_node].value('@value', 'int')
,lt.lt_name
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id
Gibt ein Ergebnis zurück
xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1 test1 1 Benefits
1 test1 4 FINRPTCOMPANY
Kreuzanwendung kann verwendet werden, um Unterabfragen zu ersetzen, wenn Sie eine Spalte der Unterabfrage benötigen
unterabfrage
select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')
hier kann ich die Spalten der Firmentabelle nicht auswählen
select P.*,T.CompanyName
from Person p
cross apply (
select *
from Company C
where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T
Ich denke es sollte Lesbarkeit sein;)
CROSS APPLY ist für Leser, die lesen, etwas Einzigartiges, um ihnen mitzuteilen, dass eine UDF verwendet wird, die auf jede Zeile der Tabelle links angewendet wird.
Natürlich gibt es auch andere Einschränkungen, bei denen eine CROSS APPLY besser als JOIN verwendet wird, die andere Freunde oben geschrieben haben.
Hier ist ein Artikel, der alles erklärt, was die Performance und die Verwendung von JOINS betrifft.
SQL Server CROSS APPLY und OUTER APPLY über JOINS
Wie in diesem Artikel vorgeschlagen, gibt es keinen Leistungsunterschied zwischen ihnen für normale Join-Vorgänge (INNER AND CROSS).
Der Nutzungsunterschied tritt auf, wenn Sie eine Abfrage wie folgt ausführen müssen:
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Employee E
WHERE E.DepartmentID = @DeptID
)
GO
SELECT * FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
Das heißt, wenn Sie sich auf die Funktion beziehen müssen. Dies kann nicht mit INNER JOIN durchgeführt werden, wodurch der Fehler "Die mehrteilige Kennung" D.DepartmentID "konnte nicht gebunden werden." Hier wird der Wert beim Lesen jeder Zeile an die Funktion übergeben. Hört sich cool an für mich. :)
Dies wurde technisch bereits sehr gut beantwortet, aber lassen Sie mich ein konkretes Beispiel dafür geben, wie äußerst nützlich es ist:
Nehmen wir an, Sie haben zwei Tabellen, Kunden und Auftrag. Kunden haben viele Bestellungen.
Ich möchte eine Ansicht erstellen, die mir Informationen über die Kunden und die letzte Bestellung gibt, die sie gemacht haben. Nur mit JOINS würde dies einige Selbstverknüpfungen und Aggregationen erfordern, was nicht schön ist. Aber mit Cross Apply ist das ganz einfach:
SELECT *
FROM Customer
CROSS APPLY (
SELECT TOP 1 *
FROM Order
WHERE Order.CustomerId = Customer.CustomerId
ORDER BY OrderDate DESC
) T
Nun, ich bin nicht sicher, ob dies als Grund für die Verwendung von Cross Apply und Inner Join gilt, aber diese Frage wurde in einem Forum-Beitrag mit Cross Apply für mich beantwortet.
Create PROCEDURE [dbo].[Message_FindHighestMatches]
-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Create table #temp
(
MessageID int,
Subjects nchar(255),
SubjectsCount int
)
Insert into #temp Select MessageID, Subjects, SubjectsCount From Message
Select Top 20 MessageID, Subjects, SubjectsCount,
(t.cnt * 100)/t3.inputvalues as MatchPercentage
From #temp
cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
join dbo.Split(@TopicalNeighborhood,',') as t2
on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3
Order By MatchPercentage desc
drop table #temp
ENDE
Der APPLY-Operator besteht im Wesentlichen darin, die Korrelation zwischen der linken und rechten Seite des Operators in der FROM-Klausel zuzulassen.
Im Gegensatz zu JOIN ist die Korrelation zwischen Eingaben nicht zulässig.
In Bezug auf die Korrelation im APPLY-Operator meine ich auf der rechten Seite:
- eine abgeleitete Tabelle - als korrelierte Unterabfrage mit einem Alias
- eine Tabellenwertfunktion - eine konzeptionelle Ansicht mit Parametern, wobei sich der Parameter auf die linke Seite beziehen kann
Beide können mehrere Spalten und Zeilen zurückgeben.
Dies ist vielleicht eine alte Frage, aber ich liebe immer noch die Macht von CROSS APPLY, um die Wiederverwendung von Logik zu vereinfachen und einen "Kettenmechanismus" für Ergebnisse bereitzustellen.
Ich habe unten eine SQL Fiddle zur Verfügung gestellt, die ein einfaches Beispiel zeigt, wie Sie CROSS APPLY verwenden können, um komplexe logische Operationen an Ihrem Datensatz durchzuführen, ohne dass die Dinge durcheinander geraten. Es ist nicht schwer, hier komplexere Berechnungen zu extrapolieren.