LEFT JOIN on a nullable column. What is the behaviour?

Question

It is not clear to me what is the behaviour of a LEFT JOIN if you try to JOIN on a column that may be NULL.
E.g.

SELECT columns  
FROM  
EmployeePayment ep JOIN EmployeePaymentGroup g ON g.group_id = ep.group_id  AND g.subsidiary_id = ep.subsidiary_id  
LEFT JOIN EmployeeSalaryDetails esd USING (department_id)    
LEFT JOIN Employee e ON e.id = esd.emp_id

What happens if the INNER JOIN of EmployeePayment and EmployeePaymentGroup return 1 record and then the second LEFT JOIN on EmployeeSalaryDetails retains this record but this record has as esd.emp_id a NULL value and try to LEFT JOIN on Employee on that NULL value.
I know that NULLs are tricky so I was wondering how does the LEFT JOIN cope with NULLs

Note:
I opened a question earlier about JOINs but the comment of Abhik Chakraborty make me realise that there was a problem with a NULL value in a condition

Paralyze

@fancyPants:I actually tried it and the record is retained despite the fact of the NULLness. But since I know NULLs are tricky I don't want to end up with some surprise later e.g. weird/undefined behavior – Paralyze

Answer 1

If you have a LEFT JOIN and the right table returns nothing (NULL) all the fields belonging to the right table in the projection are simply NULL, but you still get your result from the left table. RIGHT JOIN has the opposite behavior and INNER JOIN will not return anything.

SELECT * FROM `left_table` LEFT JOIN `right_table`

NULL = NULL evaluates to UNKNOWN (which means “no, don’t join because I have no clue if we are allowed to.”) and the projection will only contain the results from the left table.

Of course there are ways to go around this problem:

SELECT *
FROM `left_table` AS `l`
    LEFT JOIN `right_table` AS `r`
        ON `r`.`id` <=> `l`.`id`

Now checks against NULL will work normally as you are used to (NULL <=> NULL is 1 and 'value' <=> NULL is 0). Also see the documentation for the equal to operator.

Dianoetic

But what if you do a left join on columns that are NULL in both tables? – Paralyze

NULL = NULL evaluates to FALSE and you'll only get the result from the left table. – Dianoetic

Nit: NULL = NULL neither evaluates to TRUE nor to FALSE. It doesn't matter here (since for join conditions, it only matters whether the condition is true, or something else), but it does matter in general, because NOT (NULL = NULL) is not NOT FALSE. – Guaiacum

It wasn't meant like that, I wanted to express that it evaluates to FALSE regarding the join condition ("shall I join?" answer "no"). Of course you're right with your comment. We'd have to use field IS NOT NULL to get a real evaluation as we want it. – Dianoetic

NULL = NULL actualy evaluates to UNKNOWN, not FALSE. The result is the same though, you get only the result from the left table. – Hebbel

I extended my answer further to make things more clear. Thanks for your comments guys. – Dianoetic

Answer 2

You can use isnull(cast()) function to convert the null record in a type and give it a value that is also in the employee column in order to join them based on that value.

LEFT JOIN EmployeeSalaryDetails esd on isnull((esd.department_id, "input here value of id") =  "input here the value of the column"

Here is a example:

Inner join FORECAST_Claims b on  (isnull(cast(a.Durg_Key as varchar(20)),'UNKNOWN') = isnull(cast(b.Durg_Key as varchar(20)),'UNKNOWN')

Entrance

Can you give an example in a query? – Paralyze

LEFT JOIN EmployeeSalaryDetails esd on isnull((esd.department_id, "input here value of id") = "input here the value of the column" – Entrance

"input here value of id"? What id? I don't have an id. – Paralyze

From my second query, 'Unknown' represents the value you want to give to the a.Durg_Key column in case it is null. – Entrance