CODEWITHSUNDEEP
postgresqljoin
user6733568
user6733568

Reputation: 3

Convert a `not in` sub query to a `join`

Normally converting a sub query to a join is relatively straightforward, but this case has been stumping me. Given

CREATE TABLE pages(
   id serial PRIMARY KEY     NOT NULL,
   name           TEXT    NOT NULL,
   category_id            INT     NOT NULL,
   some_setting        CHAR(50)
);

INSERT INTO pages (name, category_id, some_setting) VALUES ('a', 10, 'lorem');
INSERT INTO pages (name, category_id, some_setting) VALUES ('b', 10, 'lorem');
INSERT INTO pages (name, category_id, some_setting) VALUES ('invalid', 10, 'true');


INSERT INTO pages (name, category_id, some_setting) VALUES ('a', 20, 'lorem');
INSERT INTO pages (name, category_id, some_setting) VALUES ('b', 20, 'lorem');
INSERT INTO pages (name, category_id, some_setting) VALUES ('invalid', 20, 'false');


INSERT INTO pages (name, category_id, some_setting) VALUES ('a', 30, 'lorem');
INSERT INTO pages (name, category_id, some_setting) VALUES ('b', 30, 'lorem');

I can query for

SELECT * 
FROM pages 
WHERE category_id not in (SELECT category_id FROM pages WHERE name = 'invalid' AND some_setting = 'true')

But due to the the fact that the page[name=special] doesn't need to exist I can't easily convert this to a join. Anybody any idea? Here is a link to an SQLFiddle, but it refuses to save the query: http://sqlfiddle.com/#!15/c4ca7

Upvotes: 0

Views: 68

Answers (1)

Ivan Burlutskiy
Ivan Burlutskiy

Reputation: 1623

You can try this having

select pg1.*
from pages pg1 
      left join pages pg2 on pg1.category_id = pg2.category_id
group by pg1.id
having
    ARRAY['invalid', 'true']::text != ALL (ARRAY_AGG(array[pg2.name, pg2.some_setting]::text))
order by pg1.id  
;

Why you do not what use in statement? I know that not in not use index. But having not use it too.

Upvotes: 1

Related Questions