Is there any difference between CROSS APPLY and OUTER APPLY when creating a Cartesian product?

Question

When creating a Cartesian product between two tables, is there a difference between CROSS APPLYand OUTER APPLY?

This might seem like a silly question, given that without the relationship expressed between the tables, the right table cannot fail to satisfy the relationship, but I respect what I don't know.

When I look at execution plans with a simple test setup, they are identical [two indexes refer to nested loops (Inner Join)], but simple test setups can be deceiving.

Here is an example of what I mean ( SQL Fiddle ). Setup:

CREATE TABLE dbo.First (
    Id      INT IDENTITY(1, 1) PRIMARY KEY,
    Name    NVARCHAR(100)
);
GO
DECLARE @n INT = 1;
WHILE @n < 10000
BEGIN
    INSERT INTO dbo.First (Name) VALUES ('First' + CONVERT(NVARCHAR(100), @n));
    SET @n = @n + 1;
END
GO
CREATE INDEX IX__First__Name ON dbo.First(Name);
GO
CREATE TABLE dbo.Second (
    Id      INT IDENTITY(1, 1) PRIMARY KEY,
    Name    NVARCHAR(100)
);
GO
DECLARE @n INT = 1;
WHILE @n < 10000
BEGIN
    INSERT INTO dbo.Second (Name) VALUES ('Second' + CONVERT(NVARCHAR(100), @n));
    SET @n = @n + 1;
END
GO
CREATE INDEX IX__Second__Name ON dbo.Second(Name);
GO

Usage CROSS APPLY:

SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
CROSS APPLY Second
WHERE       First.Name IN ('First253', 'First3304')
AND         Second.Name IN ('Second6543', 'Second517');

Usage OUTER APPLY:

SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
OUTER APPLY Second                                 -- <== Only change is here
WHERE       First.Name IN ('First253', 'First3304')
AND         Second.Name IN ('Second6543', 'Second517');

... both of which give me the expected four lines.

, IN :

-- No match in First
SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
CROSS APPLY Second
WHERE       First.Name IN ('no match')
AND         Second.Name IN ('Second6543', 'Second517');

SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
OUTER APPLY Second
WHERE       First.Name IN ('no match')
AND         Second.Name IN ('Second6543', 'Second517');

-- No match in Second
SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
CROSS APPLY Second
WHERE       First.Name IN ('First253', 'First3304')
AND         Second.Name IN ('no match');

SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
OUTER APPLY Second
WHERE       First.Name IN ('First253', 'First3304')
AND         Second.Name IN ('no match');

-- No match in either
SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
CROSS APPLY Second
WHERE       First.Name IN ('no match')
AND         Second.Name IN ('no match');

SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
OUTER APPLY Second
WHERE       First.Name IN ('no match')
AND         Second.Name IN ('no match');

... .

Answer 1

, :

SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
OUTER APPLY (SELECT * FROM Second WHERE Second.Id = -1) Second
WHERE       First.Name IN ('First253', 'First3304');

2 rows returned


SELECT      First.Id AS FirstId, Second.Id AS SecondId
FROM        First
CROSS APPLY (SELECT * FROM Second WHERE Second.Id = -1) Second
WHERE       First.Name IN ('First253', 'First3304');

0 rows returned

OP:

, , WHERE APPLY ( , ); , APPLY :

SELECT      First.Id AS FirstId, FilteredSecond.Id AS SecondId
FROM        First
CROSS APPLY (SELECT Id FROM Second WHERE Name IN ('xxx')) FilteredSecond 
WHERE       First.Name IN ('First253', 'First3304');

, NULL OUTER, CROSS.

Answer 2

CROSS APPLY INNER JOIN OUTER APPLY LEFT JOIN.

• CROSS/INNER ,
• OUTER/LEFT NULLs .

, JOINs , APPLY row-wise .

APPLY ( ) TVF . APPLY XMLTypedVariable.nodes().

(, ) . .