inner join on null value

Admin 1
Viewed 63343

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
sqlite tsql mysql sql
4 Answers

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

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)

user_2229080

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
user_208450

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
user_1149260
Related