mysqlsqlleft-joinsql-null

I need to join 2 Tables based on the NULL values

My Tables are able follows

Table name: Student

NAME STATUS
Jason X
John NULL

Table name: Status

STATUS DESCRIPTION
X Active
NULL Inactive

I cannot get the expected results when I join the 2 tables since NULL is not equal to NULL in MySQL.

I tried with the below code

Select a.NAME , b.DESCRIPTION
from STUDENT a
LEFT JOIN STATUS b ON a.STATUS = b.STATUS;

The result I am getting is are below

NAME DESCRIPTION
Jason Active
John NULL

Expected Result

NAME DESCRIPTION
Jason Active
John Inactive
Solution

  • Usually one NULL is not equal to another NULL.

    You need in NULL-safe compare operator <=>:

    SELECT a.name, b.description 
FROM student a 
LEFT JOIN status b ON a.status <=> b.status;