CODEWITHSUNDEEP
sqlmysqlt-sqlsqlite
user34537
user34537

Reputation:

inner join on null value

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

Upvotes: 29

Views: 74399

Answers (4)

Karl Bartel
Karl Bartel

Reputation: 3434

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

Upvotes: 4

Vadim K.
Vadim K.

Reputation: 2446

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

Upvotes: 8

Mark Byers
Mark Byers

Reputation: 838416

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

Upvotes: 54

Daniel Vassallo
Daniel Vassallo

Reputation: 344371

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)

Upvotes: 11

Related Questions