MySQL 空值的内连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2123006/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
inner join on null value
提问by Mark Byers
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?
如果我写和 u.banStatus 我将不会收到任何行？
select * from user as u
join banstatus as b on u.banStatus=b.id
where id=1
回答by Mark Byers
You don't get the row if the join is null because NULL cannot be equal to anything, even NULL.
如果连接为空，您将不会得到该行，因为 NULL 不能等于任何值，甚至 NULL。
If you change it to a LEFT JOIN, then you will get the row.
如果您将其更改为 LEFT JOIN，则您将获得该行。
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');
回答by Daniel Vassallo
回答by Vadim K.
This is an inner joins on nulls (Oracle syntax):
这是对空值的内部联接（Oracle 语法）：
select *
from user
uu
join banstatus
bb
on uu.banstatus = bb.id
or
uu.banstatus is null and bb.id is null
