Kiedy należy stosować Cross Apply na Złącze wewnętrzne?

cross apply czasami pozwala Ci robić rzeczy, których nie możesz zrobić z inner join.

Przykład (błąd składni):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

Jest to błąd składni , ponieważ, gdy jest używany z inner join, funkcje tabeli mogą przyjmować tylko zmienne lub stałe jako parametry. (Tzn. parametr funkcji tabeli nie może zależeć od Kolumny innej tabeli.)

Jednakże:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

Edit: Lub alternatywnie krótsza składnia: (przez ErikE)

select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id

Edit:

Uwaga: Informix 12.10 xC2 + ma boczne pochodne tabele , a Postgresql (9.3+) ma boczne zapytania subqueries , które mogą być użyte do podobnego efektu.

Weź pod uwagę, że masz dwa stoliki.

TABELA GŁÓWNA

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

SZCZEGÓŁY TABELA

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

Jest wiele sytuacji, w których musimy zastąpić INNER JOIN CROSS APPLY.

1. Połącz dwie tabele na podstawie wyników TOP n

Zastanów się, czy musimy wybrać Id i Name z Master i dwie ostatnie daty dla każdej Id z Details table.

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

• SQL FIDDLE

Powyższe zapytanie generuje następujący wynik.

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

Zobacz, wygenerował wyniki dla dwóch ostatnich dat z dwiema ostatnimi datami Id, a następnie dołączył te rekordy tylko w zewnętrznym zapytaniu na Id, co jest złe. Aby to osiągnąć, musimy użyć CROSS APPLY.

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

• SQL FIDDLE

I tworzy następujący wynik.

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

Oto jak to działa. Zapytanie wewnątrz CROSS APPLY może odwoływać się do zewnętrznej tabeli, gdzie {[12] } nie może tego zrobić (to wyrzuca błąd kompilacji). Po znalezieniu dwóch ostatnich dat, łączenie odbywa się wewnątrz CROSS APPLY tj. WHERE M.ID=D.ID.

2. Kiedy potrzebujemy INNER JOIN funkcjonalności za pomocą funkcji.

CROSS APPLY może być używany jako zamiennik z INNER JOIN, gdy musimy uzyskać wynik z Master tabeli i function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

A oto funkcja

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

• SQL FIDDLE

Który wygenerował następujące wynik

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

DODATKOWA ZALETA ZASTOSOWANIA KRZYŻOWEGO

APPLY może być stosowany jako zamiennik UNPIVOT. Można tu stosować zarówno CROSS APPLY, jak i OUTER APPLY, które są wymienne.

Rozważ, że masz poniższą tabelę (o nazwie 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

Zapytanie znajduje się poniżej.

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

• SQL FIDDLE

Co daje wynik

  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

Oto przykład, kiedy CROSS APPLY ma ogromne znaczenie dla wydajności:

Użycie CROSS APPLY do optymalizacji połączeń pomiędzy Warunkami

Zauważ, że oprócz zastąpienia łączników wewnętrznych możesz również ponownie użyć kodu, takiego jak obcinanie dat bez płacenia kary za zaangażowanie skalarnego UDFs, na przykład: Obliczanie trzeciej środy miesiąca za pomocą wbudowanego UDFs

Wydaje mi się, że CROSS APPLY może wypełnić pewną lukę podczas pracy z polami obliczeniowymi w złożonych/zagnieżdżonych zapytaniach i uczynić je prostszymi i bardziej czytelnymi.

Prosty przykład: masz DoB i chcesz przedstawić wiele pól związanych z wiekiem, które będą również opierać się na innych źródłach danych (takich jak zatrudnienie), takich jak Age, AgeGroup, AgeAtHiring, MinimumRetirementDate itp. do użytku w aplikacji użytkownika końcowego (na przykład w tabelach przestawnych Excel).

Opcje są ograniczone i rzadko "elegant": {]}

• Zapytania podrzędne JOIN nie mogą wprowadzać nowych wartości do zbioru danych na podstawie danych w zapytaniu nadrzędnym (musi być samodzielne).

• UDF są schludne, ale powolne, ponieważ zapobiegają operacjom równoległym. A bycie odrębnym podmiotem może być czymś dobrym (mniej kodu) lub złym(gdzie jest kod).

• Stoły połączeniowe. Czasami mogą zadziałać, ale wkrótce dołączysz do subqueries z mnóstwem związków. Wielki bałagan.

• Utwórz kolejny widok jednofunkcyjny, zakładając, że obliczenia nie wymagają danych uzyskanych w trakcie głównego zapytania.

• Tablice pośredniczące. Tak... to zwykle działa i często jest dobrą opcją, ponieważ mogą być indeksowane i szybkie, ale wydajność może również spadać z powodu aktualizacji instrukcji nie są równoległe i nie pozwalają na kaskadowe formuły (ponowne wykorzystanie wyników), aby zaktualizować kilka pól w ramach tej samej instrukcji. I czasami po prostu wolisz robić rzeczy w jednym pasuję.

• Zagnieżdżanie zapytań. Tak w dowolnym momencie możesz umieścić nawias na całym zapytaniu i użyć go jako zapytania podrzędnego, na którym możesz manipulować danymi źródłowymi i polami obliczeniowymi. Ale możesz to robić tylko tyle, zanim zrobi się brzydko. Bardzo brzydka.

• Powtarzający się kod. Jaka jest największa wartość 3 long (CASE...ELSE...Koniec) wypowiedzi? To będzie czytelne!

  • Powiedz swoim Klientom, aby obliczyli cholerne rzeczy siebie.

Wartości wprowadzone przez CROSS APPLY can...

• być używane do tworzenia jednego lub wielu pól obliczeniowych bez dodawania problemów z wydajnością, złożonością lub czytelnością mix
• podobnie jak w przypadku JOINs, kilka kolejnych wyrażeń CROSS APPLY może odnosić się do siebie: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
• Można używać wartości wprowadzonych przez krzyżyk APPLY w kolejnych warunkach JOIN
• jako bonus, jest aspekt funkcji wartej Tabeli

Cross apply działa również z polem XML. Jeśli chcesz wybrać wartości węzłów w połączeniu z innymi polami.

Na przykład, jeśli masz tabelę zawierającą jakiś xml

<root>
    <subnode1>
       <some_node value="1" />
       <some_node value="2" />
       <some_node value="3" />
       <some_node value="4" />
    </subnode1>
</root>

Korzystanie z zapytania

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

Zwróci wynik

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

Chyba powinna być czytelność;)

CROSS APPLY będzie nieco unikalny dla osób czytających, aby powiedzieć im, że używany jest UDF, który zostanie zastosowany do każdego wiersza z tabeli po lewej stronie.

Oczywiście, istnieją inne ograniczenia, w których krzyż APPLY jest lepiej używany niż dołączyć, które inni znajomi opublikowali powyżej.

Cross apply może być użyty do zastąpienia subquery tam, gdzie potrzebna jest kolumna subquery

Subquery

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

Tutaj Nie będę mógł wybrać kolumn tabeli firmy tak więc, używając cross apply

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

Oto artykuł, który wyjaśnia to wszystko, z ich różnicą wydajności i użycia nad połączeniami.

SQL Server CROSS APPLY and OUTER APPLY over JOINS

Jak sugerowano w tym artykule, nie ma różnicy wydajności między nimi dla normalnych operacji łączenia (wewnętrzne i krzyżowe).

Różnica użycia pojawia się, gdy musisz wykonać takie zapytanie:

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)

To znaczy, kiedy musisz odnosić się do funkcji. To nie może być wykonane za pomocą połączenia wewnętrznego, co daje błąd "wieloczęściowy identyfikator " D. DepartmentID" nie może być związany." tutaj wartość jest przekazywana do funkcji podczas odczytu każdego wiersza. Brzmi fajnie. :)

Cóż, nie jestem pewien, czy kwalifikuje się to jako powód, aby użyć Cross Apply versus Inner Join, ale to pytanie zostało udzielone mi w poście na Forum za pomocą Cross Apply, więc nie jestem pewien, czy istnieje equalivent metoda za pomocą Inner Join:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

Jako 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

END

To może Stare pytanie, ale nadal kocham moc Cross APPLY, aby uprościć ponowne użycie logiki i zapewnić mechanizm "łańcuchowania" wyników.

Podałem Skrzypek SQL poniżej, który pokazuje prosty przykład, jak można użyć CROSS APPLY do wykonywania złożonych operacji logicznych na zestawie danych bez rzeczy getting w ogóle bałagan. Nietrudno ekstrapolować stąd bardziej złożone obliczenia.

Http://sqlfiddle.com/#!3/23862/2

Istotą operatora APPLY jest umożliwienie korelacji pomiędzy lewą i prawą stroną operatora w klauzuli FROM.

W przeciwieństwie do JOIN, korelacja między wejściami nie jest dozwolona.

Mówiąc o korelacji w operatorze APPLY, to znaczy po prawej stronie możemy umieścić:

• pochodna tabela - jako skorelowane podquery z aliasem
• funkcja o wartości tabelarycznej - koncepcyjny widok z parametrami, w którym parametr może odnosić się do lewej Strona

Oba mogą zwracać wiele kolumn i wierszy.

ODPOWIEDŹ na to pytanie została już udzielona bardzo dobrze technicznie, ale podam konkretny przykład, jak bardzo jest to przydatne:

Powiedzmy, że masz dwa stoły, klienta i zamówienia. Klienci mają wiele zamówień.

Chcę stworzyć widok, który pokaże mi szczegóły o klientach i ostatnim zamówieniu, które złożyli. W przypadku just JOINS wymagałoby to pewnych samo-złączeń i agregacji, co nie jest ładne. Ale z Cross Apply, to bardzo proste:

SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T