SQL Server Join Missing Null Values

SQL Server JOIN missing NULL values

You can be explicit about the joins:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
     Table2
      ON (Table1.Col1 = Table2.Col1 or Table1.Col1 is NULL and Table2.Col1 is NULL) AND
         (Table1.Col2 = Table2.Col2 or Table1.Col2 is NULL and Table2.Col2 is NULL)

In practice, I would be more likely to use coalesce() in the join condition:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
     Table2
     ON (coalesce(Table1.Col1, '') = coalesce(Table2.Col1, '')) AND
        (coalesce(Table1.Col2, '') = coalesce(Table2.Col2, ''))

Where '' would be a value not in either of the tables.

Just a word of caution. In most databases, using any of these constructs prevents the use of indexes.

Sql left join on left key with null values

A left join is quite simple. It keeps all rows in the first (left) table plus all rows in the second (right) table, when the on clause evaluates to "true".

When the on clause evaluates to "false" or NULL, the left join still keeps all rows in the first table with NULL values for the second table.

If either sub_id or id is NULL, then your on clause evaluates to NULL, so it keeps all rows in the first table with NULL placeholders for the columns in the second.

Join 2 tables even with null values and sum each row

The GROUP BY will make it a bit difficult to captured all the necessary data. Also, the sum of different columns will require the use of COALESCE(column, 0) so as to use zero as the value if the column is null because if not done, your total will come back asNULL`.

One possible solution will is:

SELECT a.CustId, a.FName, a.LName, SUM(b.mtg1), SUM(b.mtg2), SUM(b.mtg3), SUM(b.mtg4), (COALESCE(SUM(b.mtg1), 0) + COALESCE(SUM(b.mtg2), 0) + COALESCE(SUM(b.mtg3), 0) + COALESCE(SUM(b.mtg4), 0)) AS total
FROM table_a a
LEFT JOIN table_b b ON(b.CustID = a.CustId)
GROUP BY a.CustID, a.FName, a.LName

LEFT JOIN ON NULL Key Values Combined with GROUP BY

Some entries for t1.key2 und t1.key3 (of the left sided table) are NULL. When that's the case, the rows are not showing in the result, why?

Likely because group by and distinct will group all nulls together. It is true that in SQL, null does not necessarily equal null, but I believe that from a distinct and group by perspective, nulls are considered identical.

I would expect a LEFT JOIN to show rows with NULL values.

I've never tried a left outer join where null is on the left side. I can't say whether the join would emit a result for such a row, but I can tell you that it wouldn't match an equi-join because null does not equal null. Are you looking for a full outer join?

LEFT JOIN table2 t2 ON t1.key1 = t1.key1

This predicate will match every row where t1.key1 is not null, which is almost a Cartesian product. That is why your count is so much larger.

I think you want

LEFT JOIN table2 t2 ON t1.key1 = t2.key1

I'd want to see all distinct values for key1, even when then key2 and key3 are NULL

Overall, I think what you want is

LEFT JOIN table2 t2 ON t1.key1 = t2.key1 AND coalesce(t1.key2, -1) = coalesce(t2.key2, -1) AND coalesce(t1.key3, -1) = coalesce(t2.key3, -1)

You want the second argument to coalesce to be a syntactically valid value of whatever type key2 and key3 are, but be a value that is not valid in your usage of it (otherwise, we'd joining rows where we have -1 on one side and null on the other.

---

Related Topics