sql 예제 - Cross Join Apply to Inner Join은 언제 사용해야합니까?




종류 (12)

교차 적용은 하위 쿼리의 열이 필요한 하위 쿼리를 대체하는 데 사용할 수 있습니다.

하위 쿼리

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

여기서는 회사 테이블의 열을 선택할 수 없으므로 교차 적용을 사용합니다.

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

CROSS APPLY 사용의 주 목적은 무엇입니까?

필자는 파티션 된 경우 큰 데이터 세트를 선택할 때 cross apply 이 더 효율적일 수 있다는 것을 (막연하게 인터넷상의 게시물을 통해) 읽었습니다. (페이징이 떠오른다)

또한 CROSS APPLY 는 UDF를 오른쪽 테이블로 요구하지 않는다는 것을 알고 있습니다.

대부분의 INNER JOIN 쿼리 (일대 다 관계)에서 CROSS APPLY 를 사용하도록 다시 작성할 수 있지만 항상 동일한 실행 계획을 제공합니다.

누구도 CROSS APPLYINNER JOIN 이 잘 작동하는 경우에 차이를 만드는 좋은 예를 들어 줄 수 있습니까?

편집하다:

실행 계획이 정확히 동일한 간단한 예제가 있습니다. (서로 다른 위치와 cross apply 이 더 효율적 /보다 효율적인 위치를 보여주십시오)

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

두 개의 테이블이 있다고 가정하십시오.

마스터 테이블

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

세부 테이블

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

INNER JOINCROSS APPLY 로 교체해야하는 경우가 많습니다.

1. TOP n 결과를 기반으로 두 테이블 결합

Master IdName 을 선택하고 Details tableId 에 대해 마지막 두 날짜를 선택 Id 고려하십시오.

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

위의 쿼리는 다음 결과를 생성합니다.

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

보세요, 마지막 두 날짜의 마지막 두 날짜에 대한 결과를 생성 한 다음 Id 에 대한 외부 쿼리에서만 이러한 레코드를 결합했습니다. 이는 잘못된 것입니다. 이를 위해 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

다음 결과를 형성합니다.

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

여기 그것이 작동하는 방법입니다. CROSS APPLY 내부의 쿼리는 INNER JOIN 이 수행 할 수없는 외부 테이블을 참조 할 수 있습니다 (컴파일 오류가 발생 함). 마지막 두 날짜를 찾을 때 CROSS APPLY 즉, WHERE M.ID=D.ID ?

2. 함수를 사용하여 INNER JOIN 기능이 필요한 경우.

CROSS APPLYMaster 테이블과 function 에서 결과를 얻어야 할 때 INNER JOIN 대신하여 사용할 수 있습니다.

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

그리고 여기에 함수가 있습니다.

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

다음과 같은 결과가 나왔다.

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

교차 적용의 추가 이점

APPLYUNPIVOT 의 대체품으로 사용될 수 있습니다. CROSS APPLY 또는 OUTER APPLY 중 하나를 교환 할 수 있습니다.

아래 테이블 ( MYTABLE )이 있다고 가정 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

쿼리는 아래와 같습니다.

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

당신에게 결과를 가져 오는

  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

글쎄, 이것이 Cross Apply 대 Inner Join을 사용할 수있는 이유인지는 모르겠지만이 쿼리는 Cross Apply를 사용하여 Forum Post에서 저에게 답변되었습니다. 그래서 Inner Join을 사용하는 equalivent 메소드가 있는지 확실하지 않습니다.

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

종료



JOINS를 능가하는 성능 차이 및 사용법을 모두 설명하는 기사가 있습니다.

SQL Server Cross는 JOINS에 적용됩니다.

이 기사에서 제안한대로 일반적인 조인 작업 (내부 및 크로스)에서는 성능 차이가 없습니다.

다음과 같은 쿼리를 수행해야 할 때 사용량 차이가 발생합니다.

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)

즉, 기능과 관련이 있어야합니다. INNER JOIN을 사용하면 "다중 파트 식별자"D.DepartmentID "를 바인딩 할 수 없습니다."라는 오류 메시지가 나타납니다. 여기에서 값은 각 행을 읽을 때 함수에 전달됩니다. 나는 시원하게 들린다. :)


APPLY 연산자의 본질은 FROM 절에서 연산자의 왼쪽과 오른쪽 사이의 상관 관계를 허용하는 것입니다.

JOIN과 달리 입력 간의 상관 관계는 허용되지 않습니다.

APPLY 연산자의 상관 관계에 대해 말하면, 오른쪽에 다음과 같이 입력 할 수 있습니다.

  • 파생 테이블 - 별칭이있는 상관 된 하위 쿼리
  • 테이블 값 함수 - 매개 변수가있는 개념적보기. 여기서 매개 변수는 왼쪽을 참조 할 수 있습니다.

둘 다 여러 열과 행을 반환 할 수 있습니다.


cross apply 때때로 당신이 inner join 할 수없는 일들을 할 수있게 해줍니다.

예 (구문 오류) :

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

inner join 과 함 2 사용될 때 테이블 함수는 변수 또는 상수 를 매개 변수로 취할 수 있기 때 문에 구문 오류 입니다. (즉, 테이블 함수 매개 변수는 다른 테이블의 열에 종속 될 수 없습니다.)

하나:

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

이것은 합법적입니다.

편집 : 또는 짧은 구문 : (ErikE)

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

편집하다:

참고 : Informix 12.10 xC2 +에는 Lateral Derived Tables가 있고 Postgresql (9.3+)에는 비슷한 효과를 낼 수있는 Lateral Subqueries 가 있습니다.


누구도 CROSS APPLY가 INNER JOIN이 잘 작동하는 경우에 차이를 만드는 좋은 예를 들어 줄 수 있습니까?

자세한 성능 비교는 내 블로그의 기사를 참조하십시오.

CROSS APPLY 는 간단한 JOIN 조건이없는 항목에 대해 더 잘 작동합니다.

이것은 t1 각 레코드에 대해 t2 에서 3 마지막 레코드를 선택합니다.

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

INNER JOIN 조건으로 쉽게 공식화 될 수 없습니다.

아마도 CTE 와 윈도우 함수를 사용하여 다음과 같이 할 수 있습니다.

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

, 그러나 이것은 덜 읽기 쉽고 아마도 덜 효율적입니다.

최신 정보:

방금 확인 했어.

masterid 에 대한 PRIMARY KEY 가있는 약 20,000,000 레코드 테이블입니다.

이 쿼리 :

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

이 동안 거의 30 초 동안 실행됩니다.

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

즉시입니다.


나는 그것이 가독성이 있어야한다고 생각한다;)

CROSS APPLY는 UDF가 사용 중임을 알려주는 사람들에게 독창적입니다.이 UDF는 왼쪽의 표에서 각 행에 적용됩니다.

물론 다른 친구가 위에 게시 한 JOIN보다 CROSS APPLY를 사용하는 것이 다른 제한 사항이 있습니다.


CROSS APPLY는 복잡한 / 중첩 된 쿼리에서 계산 된 필드로 작업 할 때 특정 간격을 채울 수 있으며 더 간단하고 읽기 쉽게 만듭니다.

간단한 예제 : DoB가 있고 최종 사용자 응용 프로그램에서 사용하기 위해 Age, AgeGroup, AgeAtHiring, MinimumRetirementDate 등과 같은 다른 데이터 소스 (예 : 고용)에 의존하는 여러 연령 관련 필드를 표시하려고합니다. (예 : Excel 피벗 테이블).

옵션은 제한적이고 드물게 우아합니다.

  • JOIN 하위 쿼리는 상위 쿼리의 데이터를 기반으로 새 값을 데이터 집합에 도입 할 수 없습니다.

  • UDF는 깔끔하지만 병렬 작업을 방해하는 경향이 있으므로 느립니다. 그리고 별도의 엔티티가되는 것은 좋은 (코드가 적음) 또는 나쁜 (코드가 어디에 있을까요) 일이 될 수 있습니다.

  • 접합 테이블. 때로는 작업 할 수 있지만 조만간 여러 UNION과 하위 쿼리를 조인합니다. 큰 혼란.

  • 계산에서 메인 쿼리의 중간 단계에서 데이터를 필요로하지 않는다고 가정하고 또 다른 단일 목적보기를 생성하십시오.

  • 중개 테이블. 예 ... 일반적으로 잘 작동하며 종종 색인을 생성하고 빠를 수 있기 때문에 좋은 옵션이지만 UPDATE 문이 병렬이 아니기 때문에 성능이 떨어지며 수식을 계단식으로 재사용 (결과 재사용) 할 수 없어서 같은 진술. 때로는 한 번에 여러 가지 일을하는 것을 선호하기도합니다.

  • 중첩 쿼리. 네, 전체 쿼리에 괄호를 넣고 원본 데이터와 계산 된 필드를 비슷하게 조작 할 수있는 하위 쿼리로 사용할 수 있습니다. 그러나 추악 해지기 전에 당신은이 일을 그렇게 많이 할 수 있습니다. 아주 못생긴.

  • 반복 코드. 3 long (CASE ... ELSE ... END) 문장 중 가장 큰 가치는 무엇입니까? 그게 읽을 수있을거야!

    • 귀하의 고객에게 그 빌어 먹을 것들을 계산하도록하십시오.

내가 뭘 놓 쳤니? 아마도, 자유롭게 의견을 말할 수 있습니다. 하지만, CROSS APPLY는 이러한 상황에서 신의 CROSS APPLY (select tbl.value + 1 as someFormula) as crossTblCROSS APPLY (select tbl.value + 1 as someFormula) as crossTblCROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl 간단한 CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl 추가하기 만하면 CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl . 새 필드는 소스 데이터에 항상 있었던 것처럼 실제로 사용할 준비가되었습니다.

CROSS APPLY를 통해 도입 된 가치는 ...

  • 복잡성 또는 가독성 문제를 믹스에 추가하지 않고 하나 이상의 계산 된 필드를 만드는 데 사용
  • JOIN과 마찬가지로 여러 개의 CROSS APPLY 문은 CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2 참조 할 수 있습니다.
  • 후속 JOIN 조건에서 CROSS APPLY에 의해 도입 된 값을 사용할 수 있습니다
  • 보너스로, 테이블 값 함수 측면이 있습니다.

Dang, 할 수 없는게 아무것도 없어!


이것은 아마도 오래된 질문이지만, 나는 논리의 재사용을 단순화하고 결과를위한 "연결"메커니즘을 제공하기 위해 CROSS APPLY의 힘을 여전히 좋아한다.

필자는 CROSS APPLY를 사용하여 데이터 세트에서 복잡한 논리 연산을 수행하는 방법에 대한 간단한 예를 보여주는 SQL Fiddle을 제공했습니다. 여기에서 더 복잡한 계산을 외삽하는 것은 어렵지 않습니다.

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


삭제할 테이블을 지정해야합니다. 여기에는 별칭이있는 버전이 있습니다.

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'




sql sql-server performance tsql cross-apply