Differentiate null and empty in Postgres JOIN query

Question

If I have tables like this:

t1:
 id | name 
----+------
  1 | a
  2 | b
  3 | c
  4 | d

t2:
 id | value 
----+-------
 10 | xxx
 20 | yyy
 30 | zzz

t_join:
 t1_id | t2_id 
-------+-------
     1 |    10
     2 |    20
     3 |    30

A SELECT query for t1.id=1 looks like:

SELECT t1.id, t1.name, t2.value FROM t1, t2,t_join WHERE t1.id=t_join.t1_id AND t2.id=t_join.t2_id AND t1.id=1;

And of course returns:

id | name | value 
----+------+-------
  1 | a    | xxx

If I do the same thing with id=4, I get nothing.

 SELECT t1.id, t1.name, t2.value FROM t1, t2,t_join WHERE t1.id=t_join.t1_id AND t2.id=t_join.t2_id AND t1.id=4;
 id | name | value 
----+------+-------
(0 rows)

And, if I do the same thing with a nonsense id=1234132, I also get nothing.

SELECT t1.id, t1.name, t2.value FROM t1, t2,t_join WHERE t1.id=t_join.t1_id AND t2.id=t_join.t2_id AND t1.id=1234132;
 id | name | value 
----+------+-------
(0 rows)

Is there a way I can differentiate between having an empty result (id=4) vs something that's null (id=1234132)? I guess I want verification that the id I'm checking exists without a separate query. Is this possible?

Did you try the OUTER JOIN with table t1? which will return all the records in t1 — Pons
– Pons, Commented Mar 27, 2017 at 23:18

Gordon Linoff · Accepted Answer · 2017-03-28 00:46:52Z

2

How about a left join:

SELECT t1.id, t1.name, t2.value
FROM t1 LEFT JOIN
     t_join
     ON t1.id = t_join.t1_id LEFT JOIN
     t2 
     ON t2.id = t_join.t2_id 
WHERE t1.id = 1;

If t.id is not found, you'll get no rows. If there are no matches in t2, then you'll get a NULL value.

Also, a simple rule: Never use commas in the FROM clause. Always, always use proper, explicit JOIN syntax.

edited Mar 28, 2017 at 0:46

answered Mar 27, 2017 at 23:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Newtang Over a year ago

Do you mean

SELECT t1.id, t1.name, t2.value FROM t1 LEFT JOIN t_join ON t1.id = t_join.t1_id LEFT JOIN t2 ON t2.id = t_join.t2_id WHERE t1.id = 1;

(The second Left Join). Also, are you recommending never to do the JOIN via WHERE clause as I did in the original question?

Gordon Linoff Over a year ago

@Newtang . . . Commas in the FROM clause have been obsolete for over 20 years.

Newtang Over a year ago

I understood the FROM comment, thanks. I was asking about the proper explicit JOIN part.

Collectives™ on Stack Overflow

Differentiate null and empty in Postgres JOIN query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related