title: LEFT JOIN With Redundant Predicate Performs Better Than a CROSS JOIN?
body: <p>I'm looking at the execution plans for two of these statements and am kind of stumped on why the <code>LEFT JOIN</code> statement performs better than the <code>CROSS JOIN</code> statement:</p>
  <p>Table Definitions:</p>
  <pre><code>CREATE TABLE [Employee] (
      [ID]                int             NOT NULL    IDENTITY(1,1),
      [FirstName]         varchar(40)     NOT NULL,
      CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([ID] ASC)
  );
  
  CREATE TABLE [dbo].[Numbers] (
      [N] INT IDENTITY (1, 1) NOT NULL,
      CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED ([N] ASC)
  ); --The Numbers table contains numbers 0 to 100,000.
  </code></pre>
  <p>Queries in Question where I join one 'day' to each Employee:</p>
  <pre><code>DECLARE @PeriodStart AS date = '2019-11-05';
  DECLARE @PeriodEnd AS date = '2019-11-05';
  
  SELECT E.FirstName, CD.ClockDate
  FROM Employee E
      CROSS JOIN (SELECT DATEADD(day, N.N, @PeriodStart) AS ClockDate 
                  FROM Numbers N 
                  WHERE N.N &lt;= DATEDIFF(day, @PeriodStart, @PeriodEnd)
          ) CD
  WHERE E.ID &gt; 2000;
  
  SELECT E.FirstName, CD.ClockDate
  FROM Employee E
      LEFT JOIN (SELECT DATEADD(day, N.N, @PeriodStart) AS ClockDate 
                  FROM Numbers N 
                  WHERE N.N &lt;= DATEDIFF(day, @PeriodStart, @PeriodEnd)
          ) CD ON CD.ClockDate = CD.ClockDate
  WHERE E.ID &gt; 2000;
  </code></pre>
  <p>The Execution Plans:
  <a href="https://www.brentozar.com/pastetheplan/?id=B139JjPKK" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=B139JjPKK</a></p>
  <p><a href="https://i.stack.imgur.com/8Okdk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8Okdk.png" alt="CROSS JOIN and LEFT JOIN Plan" /></a></p>
  <p>As you can see, according to the optimizer the second (left join) query with the seemingly redundant predicate seems to cost way less than the first (cross join) query. This is also the case when the period dates span multiple days.</p>
  <p>What's weird is if I change the LEFT JOIN's predicate to something different like <code>1 = 1</code> it'll perform like the CROSS APPLY. I also tried changing the SELECT portion of the LEFT JOIN to <code>SELECT N</code> and joined on <code>CD.N = CD.N</code> ... but that also seems to perform poorly.</p>
  <p>According to the execution plan, the second query has an index seek that only reads 3000 rows from the Numbers table while the first query is reading 10 times as many. The second query's index seek also has this predicate (which I assume comes from the LEFT JOIN):</p>
  <p><code>dateadd(day,[Numbers].[N] as [N].[N],[@PeriodStart])=dateadd(day,[Numbers].[N] as [N].[N],[@PeriodStart])</code></p>
  <p>I would like to understand why the second query seems to perform so much better even though I wouldn't except it to? Does it have something to do with the fact I'm joining the results of the DATEADD function? Is SQL evaluating the results of DATEADD before joining?</p>