Query where foreign key column can be NULL

left-join, null, postgresql, sql

Solution

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;

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

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

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

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`.

Problem

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; ``` Basically the `OR` is if `u_org.orgid` row doesn't exist.

Original source