查询外键列可以为NULL的位置 [英] Query where foreign key column can be NULL

问题描述

如果 orgid = 2 或者 uid 根本没有行，我想获取数据.orgid 是一个 integer.我能想到的最接近的方法是执行 IS NULL ，但是我没有获取没有 orgid 的 uid 的数据排.有什么主意吗?

I want to get data if orgid = 2 or if there is no row at all for the uid. orgid is an integer. The closest thing I could think of is to do IS NULL but I'm not getting data for the uid that doesn't have an orgid row. Any idea?

select u.uid,u.fname,u.lname from u 
inner join u_org on u.uid = u_org.uid 
inner join login on u.uid = login.uid 
where u_org.orgid=2 or u_org.orgid is NULL 
and login.access != 4;

基本上 OR 是 u_org.orgid 行不存在.

推荐答案

如果对于uid根本没有任何行"，并且像您一样 JOIN ，您将得到 no行作为结果.使用 左[OUTER] JOIN 代替:

If there is "no row at all for the uid", and you JOIN like you do, you get no row as result. Use LEFT [OUTER] JOIN instead:

SELECT u.uid, u.fname, u.lname
FROM   u 
LEFT   JOIN u_org o ON u.uid = o.uid 
LEFT   JOIN login l ON u.uid = l.uid 
WHERE (o.orgid = 2 OR o.orgid IS NULL)
AND    l.access IS DISTINCT FROM 4;

另外，你需要我添加的括号，因为运算符优先级 .( AND 绑定在 OR 之前).

Also, you need the parenthesis I added because of operator precedence. (AND binds before OR).

在上一个WHERE条件下，我使用 IS DISTINCT FROM 而不是！= ，因为同样， login.access 可能是NULL ，将不符合条件.

I use IS DISTINCT FROM instead of != in the last WHERE condition because, again, login.access might be NULL, which would not qualify.

但是，由于您似乎只对表 u 中的列感兴趣，所以这种替代查询会更优雅:

However, since you only seem to be interested in columns from table u to begin with, this alternative query would be more elegant:

SELECT u.uid, u.fname, u.lname
FROM   u
WHERE (u.uid IS NULL OR EXISTS (
   SELECT 1
   FROM   u_org o
   WHERE  o.uid = u.uid
   AND    o.orgid = 2
   ))
AND NOT EXISTS (
   SELECT 1
   FROM   login l
   WHERE  l.uid = u.uid
   AND    l.access = 4
   );

此替代方法还有一个额外的优点，即使您在 u_org 或登录.

This alternative has the additional advantage, that you always get one row from u, even if there are multiple rows in u_org or login.

这篇关于查询外键列可以为NULL的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！