3

This is real results from my database. I spend some time trying to reproduce this behavior with an SSCCE but failed. How is the last of the below results possible?

SELECT COUNT(*) FROM rr.resource    a
--- 15771

SELECT COUNT(*) FROM rr.resource    a
NATURAL JOIN rr.interface   b
--- 41419

SELECT COUNT(*) 
FROM rr.resource    a
NATURAL JOIN rr.interface   b
NATURAL JOIN rr.interface   c
--- 0

The zero (0) row count in the last query is consistent and repeatable every time. In some examples I created, adding a second natural join with the same table twice yields the same number of rows (as I was expecting). How is then the above result possible?

4
  • What data types are the columns? – Philᵀᴹ Mar 17 '15 at 21:55
  • Also, what specific version are you using? – Philᵀᴹ Mar 17 '15 at 21:56
  • @Phil I didn't want to provide any more information that would have made the question too narrow and/or too specific for my case; the accepted answer does indeed show how this kind of behavior is possible and that's what I was asking. – Marcus Junius Brutus Mar 17 '15 at 23:18
  • not a problem. It's a good question. Nearly had us stumped for a second :) – Philᵀᴹ Mar 18 '15 at 0:04
8

Basically, NULL is causing this, because NULL<>NULL. One of the columns in your self-joined table will be all NULLs.

Here's a little test case that shows why this can happen. Naughty NULL equality and the way NATURAL JOIN works, picking column names to join on for you.

Setup:

create table one ( a integer, b integer );

CREATE TABLE two ( A INTEGER , c integer);

insert into one values (1,1);

insert into two values (1,1);

insert into two values (1,null);

insert into two values(2,NULL);

Queries:

SELECT COUNT(*)
FROM ONE
NATURAL JOIN TWO tttt;

SELECT COUNT(*)
FROM ONE
NATURAL JOIN TWO tttt
NATURAL JOIN TWO ttttt;

The first query will give 2, the second query 1.

Your problem will just be a more advanced version of this.

Use JOIN .... USING with the columns of your own choice to workaround this.

1
  • Yes, exactly. For the OP's query that results to 0, it means that all rows of table rr.interface (well, not all, actually the ones that survive the join to rr.resource) have at least one of their column values as null. – ypercubeᵀᴹ Mar 17 '15 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.