SQL "Join" on 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.

SQL join with null values not returning all rows

Make the join a LEFT OUTER JOIN and move the WHERE clause associated with it to the join - simpler than making the where clause also check for null there perhaps. Related question on this type of a change: SQL JOIN Condition moved to with where clause produces differences

SELECT Company.Comp_CompanyId, Company.comp_bigchange_contactid, Company.Comp_Type, Company.Comp_Name, Address.Addr_Address1, Address.Addr_Address2, Address.Addr_Address3, Address.Addr_Address4, Address.Addr_City, Address.Addr_PostCode, Custom_Captions.Capt_UK AS Addr_Country, Company.Comp_UpdatedDate
FROM Company 
INNER JOIN Address ON Company.Comp_PrimaryAddressId = Address.Addr_AddressId
LEFT OUTER JOIN Custom_Captions ON Address.Addr_Country = Custom_Captions.Capt_Code
    AND Custom_Captions.Capt_Family = 'addr_country' 
WHERE Company.comp_bigchange_sync = 'Y' 
ORDER BY Company.Comp_UpdatedDate ASC

SQL multiple joins while dealing with null values

Your INNER JOINs feature ON clauses that do equijoin on a possibly NULL field.
Yet you want to get a row back anyway,
showing that the given field was empty.

The tool you're looking for is LEFT OUTER JOIN,
which will do exactly that.
https://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

For example

SELECT cat.name, cat.price, inv.qty, inv.date
FROM catalog cat
LEFT OUTER JOIN inventory inv  ON cat.id = inv.cat_id

will reveal catalog items even if they've not been inventoried.

An expression like COALESCE(inv.qty, 0)
can be handy for suppressing NULLs
in the result rows, if desired.

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

Count from 4 tables with join and null values

You are joining along two dimensions, so you are getting a Cartesian products. That is simply what happens.

A hacky solution -- which works quite well if there are not too many comments and likes for a given post -- is to use COUNT(DISTINCT):

SELECT Posts.ID,
       COUNT(DISTINCT Comments.Post_id) as Total_comments, 
       COUNT(DISTINCT Likes.Post_id) as Total_likes,
       Users.Name

The most efficient solution, though, is probably correlated subqueries:

SELECT p.ID,
       (SELECT COUNT(*) FROM Comments c WHERE c.Post_Id = p.ID) as Total_comments, 
       (SELECT COUNT(*) FROM Likes l WHERE l..Post_id = p.ID) as Total_likes,
       u.Name
FROM Posts p INNER JOIN
     Users u
     ON u.ID = p.User_id ;

This is faster because it avoids the outer aggregation. However, it requires indexes on comments(post_id) and likes(post_id).

---

Related Topics