Doing a left join and having every match include an extra null row

Question

So I want to do a left join and have every row on the left table include a row of nulls on the right, even when there is a match.

Is there a name for this type of join?

And is there a better way to do it than how I do it below (namely something avoiding a union)?

SELECT
    T1.C1,
    ...
    T1.CN
    T2.C1,
    ...
    T2.CM
FROM
    Table1 T1
    INNER JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION

SELECT
    T1.C1,
    ...
    T1.CN,
    NULL, --NULL 1
    ...
    NULL, --NULL M
FROM
    Table1 T1

(I could also start with a left join and then union a select like the second select above filtered to only the tables that have a matching ID, but that is still complex way to do it).

I was wondering if there is something like

SELECT
    T1.C1,
    ...
    T1.CN
    T2.C1,
    ...
    T2.CM
FROM
    Table1 T1
    INNER JOIN Table2 T2 INCLUDE_NULL_ROW_WITH_MATCH
        ON T1.Key1 = T2.Key1

This is specifically for SQL Server 2008 if that is relevant.

Answer 1

I see 3 ways to do this but all involve a UNION ALL:

• your version`:

  SELECT
      T1.C1, ....., T1.CN,
      T2.C1, ..., T2.CM
  FROM
      Table1 T1 JOIN Table2 T2
          ON T1.Key1 = T2.Key1
  
  UNION ALL
  
  SELECT
      T1.C1, ..., T1.CN,
      NULL, ... NULL
  FROM
      Table1 T1 ;
  

• slightly changing the second part:

  SELECT
      T1.C1, ....., T1.CN,
      T2.C1, ..., T2.CM
  FROM
      Table1 T1 JOIN Table2 T2
          ON T1.Key1 = T2.Key1
  
  UNION ALL
  
  SELECT
      T1.C1, ....., T1.CN,
      T2.C1, ..., T2.CM
  FROM
      Table1 T1 LEFT JOIN Table2 T2
          ON 0 = 1 ;                   -- FALSE
  

• first a UNION, then join:

  SELECT
      T1.C1, ....., T1.CN,
      T2.C1, ..., T2.CM
  FROM
      Table1 T1 JOIN  
        ( SELECT * FROM T2
          UNION ALL
          SELECT NULL, ..., NULL
         ) AS T2
          ON T1.Key1 = T2.Key1 
          OR T2.Key1 IS NULL ;
  

I don't think there will be much difference in execution plans and efficiency but the first one seems more simple.

Answer 2

Here is another option that only takes a single pass over the data by performing the left join and then, for each resulting row, tacking on the NULL row if necessary.

SELECT Results.*
FROM #Table1 T1
LEFT OUTER JOIN #Table2 T2
    ON T1.Key1 = T2.Key1
CROSS APPLY (
    SELECT T1.C1, T1.C2, T2.C1 AS T2_C1, T2.C2 AS T2_C2
    UNION ALL
    SELECT T1.C1, T1.C2, NULL, NULL
    WHERE T2.Key1 IS NOT NULL
) Results

If we use the following test script, we can see that we get the correct results and and that the execution plan takes a single pass over both #Table1 and #Table2. At least on this 10,000 row data set, performance in terms of run time and IO is improved when compared to the previous solutions that were presented.

-- Create fake data with 10,000 rows each, 5,000 of which match
CREATE TABLE #Table1 (Key1 INT NOT NULL IDENTITY(1,1) PRIMARY KEY, C1 INT NOT NULL, C2 INT NOT NULL)
GO
CREATE TABLE #Table2 (Key1 INT NOT NULL IDENTITY(1,2) PRIMARY KEY, C1 INT NOT NULL, C2 INT NOT NULL)
GO
INSERT INTO #Table1 VALUES (1,2)
GO 10000
INSERT INTO #Table2 VALUES (3,4)
GO 10000

SET STATISTICS TIME, IO ON 

-- The proposed solution
-- CPU time = 16 ms,  elapsed time = 113 ms.
--Table '#Table1'. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table '#Table2'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT Results.*
FROM #Table1 T1
LEFT OUTER JOIN #Table2 T2
    ON T1.Key1 = T2.Key1
CROSS APPLY (
    SELECT T1.C1, T1.C2, T2.C1 AS T2_C1, T2.C2 AS T2_C2
    UNION ALL
    -- Add the extra row iff the LEFT JOIN matched, but don't require
    -- another read of the #Table1 table in order to do so
    SELECT T1.C1, T1.C2, NULL, NULL
    WHERE T2.Key1 IS NOT NULL
) Results
GO

-- ypercube #1: Basically the same performance, but twice as many reads on #Table1
-- CPU time = 16 ms,  elapsed time = 115 ms.
--Table '#Table1'. Scan count 2, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table '#Table2'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT T1.C1, T1.C2, T2.C1, T2.C2 
FROM
    #Table1 T1 JOIN #Table2 T2
        ON T1.Key1 = T2.Key1
UNION ALL
SELECT T1.C1, T1.C2, NULL, NULL
FROM #Table1 T1 ;
GO

-- ypercube #3: We end up with 10,000 seeks into #Table2, a much less efficient plan
-- Note that SQL Server chooses the same plan (but with scan instead of seek) if there is no primary key as well
-- This results in over 30,000ms of CPU time for the query.  SQL does not seem to be processing this query very well!
-- CPU time = CPU time = 94 ms,  elapsed time = 140 ms.
--Table '#Table1'. Scan count 2, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table '#Table2'. Scan count 10000, logical reads 20024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  --Table 'Worktable'. Scan count 8, logical reads 531141, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT T1.C1, T1.C2, T2.C1, T2.C2 
FROM #Table1 T1 JOIN  
      ( SELECT * FROM #Table2
        UNION ALL
        SELECT NULL, NULL, NULL
       ) AS T2
        ON T1.Key1 = T2.Key1 
        OR T2.Key1 IS NULL;
GO

Here is the plan: