Sql-server – Doing a left join and having every match include an extra null row

join;sql server

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.

Best Answer

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.

Related Question