Queries return contrary results (Postgre SQL)

[Lutoslaw]

Hello, How is this possible? "orders" has an FK ao_campaign_id to "ao_campaigns".

=# select id from ao_campaigns where id=2851;
id

2851
(1 row)

=# select id from orders where ao_campaign_id=2851;
id

(0 rows)

=# select * from ao_campaigns where id not in (select ao_campaign_id from orders);
id | id_tvn | order_id | start_date | end_date | label
----+--------+----------+------------+----------+-------
(0 rows)

(types are correct, all ids are integers). The last query should return at least one result - 2851, right? What am I missing? Greetings, Jacek

[Lutoslaw]

Got it! Correct query:

select * from ao_campaigns where ao_campaigns.id not in (select orders.ao_campaign_id from orders where ao_campaign_id IS NOT NULL);

Some of ao_campaign_id in orders were NULL, which are treated as "unknown" and therefore uncomparable. It is not intuitive in this particular case, though. Reference: sql null logic - Szukaj w Google[^]

[Jorgen Andersson]

Or get used to use EXISTS instead of IN. EXISTS does not use three valued logic.

SELECT *
FROM ao_campaigns
WHERE NOT EXISTS (
SELECT *
FROM orders
WHERE orders.ao_campaign_id = ao_campaigns.id
)

Wrong is evil and must be defeated. - Jeff Ello