patternsqlMinor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

leftincludenullhavingdoingeveryjoinmatchandrow

Problem

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.

Solution

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.

Code Snippets

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 ;

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

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 ;

Context

StackExchange Database Administrators Q#106604, answer score: 4

Revisions (0)

No revisions yet.