空值上的 SQL 内部联接

SQL Inner Join On Null Values

提问by Rico

I have a Join

我有一个加入

SELECT * FROM Y
INNER JOIN X ON ISNULL(X.QID, 0) = ISNULL(y.QID, 0) 

Isnullin a Join like this makes it slow. It's like having a conditional Join. Is there any work around to something like this? I have a lot of records where QIDis Null

Isnull在这样的 Join 中使它变慢。这就像有条件加入。有没有办法解决这样的问题？我有很多记录，哪里QID是 Null

Anyone have a work around that doesn't entail modifying the data

任何人都有一个不需要修改数据的解决方法

回答by Evan Carroll

You have two options

你有两个选择

INNER JOIN x
   ON x.qid = y.qid OR (x.qid IS NULL AND y.qid IS NULL)

or easier

或更容易

INNER JOIN x
  ON x.qid IS NOT DISTINCT FROM y.qid

回答by JohnFx

Are you committed to using the Inner join syntax?

您是否承诺使用内部联接语法？

If not you could use this alternative syntax:

如果不是，您可以使用这种替代语法：

SELECT * 
FROM Y,X
WHERE (X.QID=Y.QID) or (X.QUID is null and Y.QUID is null)

回答by Martin Smith

This article has a good discussion on this issue. You can use

这篇文章对这个问题有很好的讨论。您可以使用

SELECT * 
FROM Y
INNER JOIN X ON EXISTS(SELECT X.QID 
                       INTERSECT 
                       SELECT y.QID);

回答by HLGEM

I'm pretty sure that the join doesn't even do what you want. If there are 100 records in table a with a null qid and 100 records in table b with a null qid, then the join as written should make a cross join and give 10,000 results for those records. If you look at the following code and run the examples, I think that the last one is probably more the result set you intended:

我很确定加入甚至没有做你想要的。如果表 a 中有 100 条记录的 qid 为空，而表 b 中的 100 条记录的 qid 为空，则写入的连接应该进行交叉连接并为这些记录提供 10,000 个结果。如果您查看以下代码并运行示例，我认为最后一个可能更像是您想要的结果集：

create table #test1 (id int identity, qid int)
create table #test2 (id int identity, qid int)

Insert #test1 (qid)
select null
union all
select null
union all
select 1
union all
select 2
union all
select null

Insert #test2 (qid)
select null
union all
select null
union all
select 1
union all
select 3
union all
select null


select * from #test2 t2
join #test1 t1 on t2.qid = t1.qid

select * from #test2 t2
join #test1 t1 on isnull(t2.qid, 0) = isnull(t1.qid, 0)


select * from #test2 t2
join #test1 t1 on 
 t1.qid = t2.qid OR ( t1.qid IS NULL AND t2.qid IS NULL )


select t2.id, t2.qid, t1.id, t1.qid from #test2 t2
join #test1 t1 on t2.qid = t1.qid
union all
select null, null,id, qid from #test1 where qid is null
union all
select id, qid, null, null from #test2  where qid is null

回答by ni3nas

If you want null values to be included from Y.QID then Fastest way is

如果您希望从 Y.QID 中包含空值，那么最快的方法是

SELECT * FROM Y LEFT JOIN X ON y.QID = X.QID

SELECT * FROM Y LEFT JOIN X ON y.QID = X.QID

Note: this solution is applicable only if you need null values from Left table i.e. Y (in above case).

注意：此解决方案仅适用于您需要左表中的空值，即 Y（在上述情况下）。

Otherwise INNER JOIN x ON x.qid IS NOT DISTINCT FROM y.qid is right way to do

否则 INNER JOIN x ON x.qid IS NOT DISTINCT FROM y.qid 是正确的做法

回答by n1000

You could also use the coalescefunction. I tested this in PostgreSQL, but it should also work for MySQLor MS SQL server.

您还可以使用合并功能。我在PostgreSQL 中对此进行了测试，但它也适用于MySQL或MS SQL 服务器。

INNER JOIN x ON coalesce(x.qid, -1) = coalesce(y.qid, -1)

This will replace NULLwith -1before evaluating it. Hence there must be no -1in qid.

这将在评估之前替换NULL为-1。因此必须没有-1in qid。

回答by pr1001

Basically you want to join two tables together where their QID columns are both notnull, correct? However, you aren't enforcing any other conditions, such as that the two QID values (which seems strange to me, but ok). Something as simple as the following (tested in MySQL) seems to do what you want:

基本上你想将两个表连接在一起，它们的 QID 列都不为空，对吗？但是，您没有强制执行任何其他条件，例如两个 QID 值（这对我来说似乎很奇怪，但还可以）。像下面这样简单的东西（在 MySQL 中测试）似乎可以做你想做的事：

SELECT * FROM `Y` INNER JOIN `X` ON (`Y`.`QID` IS NOT NULL AND `X`.`QID` IS NOT NULL);

This gives you every non-null row in Y joined to every non-null row in X.

这为您提供了 Y 中的每个非空行都连接到 X 中的每个非空行。

Update:Rico says he also wants the rows with NULL values, why not just:

更新：Rico 说他还想要具有 NULL 值的行，为什么不只是：

SELECT * FROM `Y` INNER JOIN `X`;