inner join on null value

Question

I'm not sure if i made a mistake in logic.

If i have a query and i do an inner join with a null value would i always get no results or will it ignore the join and succeed? example

user { id PK, name NVARCHAR NOT NULL, banStatus nullable reference }

if i write and u.banStatus i will receive no rows?

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

Answer 1

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)

Answer 2

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');

Answer 3

This is an inner joins on nulls:

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