SQL:在两个表上联接,其中on列为null [英] SQL: Join on two tables where on column is null
问题描述
我正在使用mysql数据库.我有两个表的错误和配置文件. Bug表有两列(assigned_to,qa_contact),它们通过多对一关系指向配置文件. 这些是我查询的简化版本.
I am using mysql database. I have two tables bugs and profiles. Bugs table has two columns (assigned_to, qa_contact) pointing to profiles by a many to one relationship. These are the simplified version of my queries.
首先,我尝试执行此操作,但是它会返回重复的行,其中bugs表中的qa_contact为null
Firstly, I was trying to do this but it returns duplicate rows where qa_contact is null in the bugs table
select
bug_id,
desc,
dev.assigned_to,
qa.qa_contact
from
bugs,
profiles as dev,
profiles as qa
where
assigned_to = dev.userid
and (qa_contact = qa.userid or qa_contact is null)
第二,我的新方法是:
select bug_id, desc, dev.assigned_to, qa.qa_contact
from
bugs,
profiles as dev,
profiles as qa
where
assigned_to = dev.userid
and qa_contact = qa.userid
UNION
select bug_id, desc, dev.assigned_to, null
from
bugs,
profiles as dev,
profiles as qa
where
assigned_to = dev.userid
and qa_contact is null
但是在第二种方法中,它排除了qa_contact为null的结果.谁能建议一种有效的方法,因为我正在处理数百万个记录,并且想在结果集上添加更多过滤器.
But in the second approach it excludes the result where qa_contact is null. Can anyone suggest a efficient way of doing this because I am dealing with records in order of millions and would like to add more filters on the resultset.
推荐答案
这是LEFT JOIN的用途:
This is what LEFT JOINs are for:
SELECT bug_id, `desc`, dev.assigned_to, qa.qa_contact
FROM bugs
INNER JOIN profiles as dev ON bugs.assigned_to = dev.userid
LEFT OUTER JOIN profiles as qa ON bugs.qa_contact = qa.userid
这篇关于SQL:在两个表上联接,其中on列为null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!