1

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.

  • 1
    Why avoid a UNION? Seems the most reasonable way to do this (and use UNION ALL, it should be more efficient.) – ypercubeᵀᴹ Jul 10 '15 at 15:47
  • If that is the best way, then that is the best way. I was wondering if there was something like an 'expanded left join' or such that was how this is done. If there isn't and unioning (or unioning all) is the best way, then that is the way to go. – Lawtonfogle Jul 10 '15 at 15:53
  • P.S. Is the UNION ALL better in the first example since duplicates are already guaranteed to be removed (The first select is an inner join, thus forcing the second table's columns to have some values while the second select, all of the second table columns are guaranteed null)? Does SQL Server pick up on this or does it still try to remove (the non-existing) duplicates? – Lawtonfogle Jul 10 '15 at 15:57
  • @Lawntonfogle, not sure if SQL-Server optimizer can pick up that key1 from the first part is not null and null in the 2nd part (so skip the merging/check for duplcaites) but we can check the plans. It doesn't hurt though to use UNION ALL. – ypercubeᵀᴹ Jul 10 '15 at 16:03
2

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.

1

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.