5

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

Hardik Vinzava
  • 968
  • 10
  • 22
Jim
  • 18,826
  • 34
  • 135
  • 254
  • @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 – Jim Apr 25 '14 at 10:25

2 Answers2

5

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.

Fleshgrinder
  • 15,703
  • 4
  • 47
  • 56
  • But what if you do a left join on columns that are NULL in **both** tables? – Jim Apr 25 '14 at 10:28
  • 1
    `NULL = NULL` evaluates to `FALSE` and you'll only get the result from the left table. – Fleshgrinder Apr 25 '14 at 10:30
  • 1
    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`. –  Apr 25 '14 at 10:34
  • 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. – Fleshgrinder Apr 25 '14 at 10:36
  • `NULL = NULL` actualy evaluates to `UNKNOWN`, not `FALSE`. The result is the same though, you get only the result from the left table. – ypercubeᵀᴹ Apr 25 '14 at 10:39
  • I extended my answer further to make things more clear. Thanks for your comments guys. – Fleshgrinder Apr 25 '14 at 12:14
-1

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')
jaji18
  • 55
  • 1
  • 7