inner join on null value

Question

You don't get the row if the join is null because NULL cannot be equal to anything, even NULL.

If you change it to a LEFT JOIN, then you will get the row.

With an inner join:

select * from user as u
join banstatus as b on u.banStatus=b.id

1, '1', 1, 'Banned'

With a left join:

select * from user as u
left join banstatus as b on u.banStatus=b.id

1, '1', 1, 'Banned'
2, 'NULL', , ''

Using this test data:

CREATE TABLE user (id int, banstatus nvarchar(100));
INSERT INTO user (id, banstatus) VALUES
(1, '1'),
(2, 'NULL');

CREATE TABLE banstatus (id int, text nvarchar(100));
INSERT INTO banstatus (id, text) VALUES
(1, 'Banned');

When you do an INNER JOIN, NULL values do not match with anything. Not even with each other. That is why your query is not returning any rows. (Source)

This is an inner joins on nulls (Oracle syntax):

select *
  from user
       uu
  join banstatus
       bb
    on uu.banstatus = bb.id
       or
       uu.banstatus is null and bb.id is null

Nulls are not equal to any other value, so the join condition is not true for nulls. You can achieve the desired result by choosing a different join condition. Instead of

u.banStatus = b.id

use

u.banStatus = b.id OR (u.banStatus IS NULL AND b.id IS NULL)

Some SQL dialects have a more concise syntax for this kind of comparison:

-- PostgreSQL
u.banStatus IS NOT DISTINCT FROM b.id

-- SQLite
u.banStatus IS b.id