Sql-server – JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

join;mergenullsql serverunion

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas.

Basically, what I want to do seems like a UNION where records are "collapsed" in a sense to remove NULL values (where say, the ID and DATE are the same). I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to produce the desired result. It seems like maybe MSSQL's MERGE is maybe what I'm looking for? It seems like that operation is meant for table creation, however.

My end goal is basically to create a view from the result. All I really need is the SELECT statement that allows me to do further manipulation.

To help visualize the problem, let's say I have the below tables:

+----+--------+-------------+---------------+
| ID | Date   | FloorRating | CeilingRating |
+----+--------+-------------+---------------+
| 1A | 1/1/18 | 9           | 6             |
+----+--------+-------------+---------------+
| 1A | 1/1/19 | 9           | 7             |
+----+--------+-------------+---------------+
| 3D | 2/2/19 | 5           | 8             |
+----+--------+-------------+---------------+
| 3D | 3/3/19 | 4           | 9             |
+----+--------+-------------+---------------+


+----+--------+--------------+-----------+
| ID | Date   | WindowRating | FanRating |
+----+--------+--------------+-----------+
| 1A | 1/1/18 | 4            | 7         |
+----+--------+--------------+-----------+
| 1A | 2/2/19 | 3            | 7         |
+----+--------+--------------+-----------+
| 3D | 2/2/19 | 9            | 4         |
+----+--------+--------------+-----------+
| 3D | 8/8/19 | 1            | 3         |
+----+--------+--------------+-----------+

What I want to end up with is something like this, where all possible ID-Date combinations are accounted for and field values are filled in where possible:

+----+--------+-------------+---------------+--------------+-----------+
| ID | Date   | FloorRating | CeilingRating | WindowRating | FanRating |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/18 | 9           | 6             | 4            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 1/1/19 | 9           | 7             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 1A | 2/2/19 |             |               | 3            | 7         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 2/2/19 | 5           | 8             | 9            | 4         |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 3/3/19 | 4           | 9             |              |           |
+----+--------+-------------+---------------+--------------+-----------+
| 3D | 8/8/19 |             |               | 1            | 3         |
+----+--------+-------------+---------------+--------------+-----------+

I appreciate any help or tips you can provide!

Best Answer

I've tried a UNION, FULL JOIN, and SELF JOIN but none of those seem to produce the desired result

FULL JOIN works fine. Maybe you were joining on the wrong predicate or didn't coalesce the results from the ID and Date columns (done with ISNULL below)?

db <> fiddle

SELECT ISNULL(T1.[ID], T2.[ID]) AS [ID],
       ISNULL(T1.Date, T2.Date) AS Date,
       [FloorRating],
       [CeilingRating],
       [WindowRating],
       [FanRating]
FROM   Table1 T1
       FULL JOIN Table2 T2
              ON T1.[ID] = T2.[ID]
                 AND T1.[Date] = T2.[Date]
ORDER  BY [ID],
          Date

Related Solutions

Sql-server – SQL Server: join two tables with “valid-to” dates

DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)

INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')

INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')

SELECT
   T1.*, T2x.*
FROM
   @t1 T1
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t2 
   WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
   ORDER BY T2_ValidUntil
   ) T2x
UNION
SELECT
   T1x.*, T2.*
FROM
   @t2 T2
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t1
   WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
   ORDER BY T1_ValidUntil
   ) T1x

Sql-server – Finding distinct rows across two tables: Full Outer Join more efficient than Union

The semantics of the two queries are not the same - UNION removes duplicates, whereas the FULL OUTER JOIN will not:

DECLARE @T1 AS table (id bigint NULL, val integer NULL);
DECLARE @T2 AS table (id bigint NULL, val integer NULL);

INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);

SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
FROM @t1 t1
FULL OUTER JOIN @t2 t2
    ON t2.id = t1.id
    AND t2.val = t1.val;

SELECT t1.id, t1.val
FROM @t1 t1
UNION 
SELECT t2.id, t2.val
FROM @t2 t2;

Output:

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
╚════╩═════╝

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
╚════╩═════╝

That said, the optimizer does not know many FOJN tricks, so it is always possible that there is a better way to express the query than the natural UNION. Only commonly-useful and always-correct transformations are implemented.

Note that with a unique constraint only on the larger table, the optimizer chooses a hash union, without expensive duplicate-removal on the probe input, that makes it choose Concat Union All in the question example:

ALTER TABLE #t2 
ADD CONSTRAINT UQ2 
UNIQUE CLUSTERED (id);

SELECT COUNT(*), AVG(x.id), AVG(x.val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) AS x;

The FOJN rewrite may well be a useful one in cases where you know there cannot be duplicates within each input set, but this condition is not enforced with a unique constraint or index (particularly on the large input).

If such a uniqueness guarantee does exist, and yet the optimizer does not select a Hash Union, you might try an OPTION (HASH UNION) hint, to see how it compares.

Best Answer

Related Solutions

Sql-server – SQL Server: join two tables with “valid-to” dates

Sql-server – Finding distinct rows across two tables: Full Outer Join more efficient than Union

Related Question