title: Is there any difference between CROSS APPLY and OUTER APPLY when creating a cartesian product?
body: <p>When creating a cartesian product between two tables, is there any difference between <code>CROSS APPLY</code> and <code>OUTER APPLY</code>?</p>
  
  <p>This may seem like a silly question given that without a relationship expressed between the tables, the right-hand table can't fail to satisfy the relation, but I'm respectful of what I don't know.</p>
  
  <p>When I look at the execution plans with a simple test setup, they're identical [two index seeks feeding into Nested Loops (Inner Join)], but simple test setups can be deceptive.</p>
  
  <p>Here's an example of what I mean (<a href="http://sqlfiddle.com/#!18/71053/1" rel="noreferrer"><strong>SQL Fiddle</strong></a>). The setup:</p>
  
  <pre><code>CREATE TABLE dbo.First (
      Id      INT IDENTITY(1, 1) PRIMARY KEY,
      Name    NVARCHAR(100)
  );
  GO
  DECLARE @n INT = 1;
  WHILE @n &lt; 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 &lt; 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
  </code></pre>
  
  <p>Using <code>CROSS APPLY</code>:</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>Using <code>OUTER APPLY</code>:</p>
  
  <pre><code>SELECT      First.Id AS FirstId, Second.Id AS SecondId
  FROM        First
  OUTER APPLY Second                                 -- &lt;== Only change is here
  WHERE       First.Name IN ('First253', 'First3304')
  AND         Second.Name IN ('Second6543', 'Second517');
  </code></pre>
  
  <p>...both of which give me the expected four rows.</p>
  
  <p>Plus various variations where either, or both, <code>IN</code> clauses return no matches:</p>
  
  <pre><code>-- 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');
  </code></pre>
  
  <p>...all of which give me the expected zero rows.</p>