How can I query with NOT IN in a many-to-many relationship including NULLs

Given I have 3 tables and I need to retrieve all the rows where there is no relationship with a specific record and include the rows where there are zero relationships, how can I build this query?

SELECT id, name FROM books;
 id | name    
---------------------------
 B1 | the book
 B2 | super book
 B3 | another great book
 B4 | how to be a book

SELECT id, name FROM authors;
 id | name         
---------------------------
 A1 | Huey
 A2 | Dewey
 A3 | Louie
 A4 | Donald

SELECT "bookId", "authorId" FROM books_authors;
 bookId | authorId           
---------------------------+---------------------------
 B1 | A1
 B3 | A2
 B4 | A2
 B4 | A4

-- Book B2 has no author
-- Book B4 has 2 authors

I need all books where the author Dewey (id = A2) has no relation at all.

Because B4 has a relation with author Dewey (A2), it should not be returned.

The query should return only B1 and B2.

Answer

SELECT id 
FROM books EXCEPT (
  SELECT bookid FROM books_authors WHERE authorId='A2'
)

Advertisement

Answer