outer join example

problem.sql

db=# select * from books;
             bookclub_id              |               book_id
--------------------------------------+--------------------------------------
 22222222-2222-2222-2222-222222222222 | 00000000-0000-0000-0000-000000000000
 22222222-2222-2222-2222-222222222222 | 11111111-1111-1111-1111-111111111111
(2 rows)

db=# select * from book_reviews;
             bookclub_id              |               book_id                | reviewer_id | rating
--------------------------------------+--------------------------------------+-------------+--------
 22222222-2222-2222-2222-222222222222 | 00000000-0000-0000-0000-000000000000 | alice       |      1
(1 row)

db=# SELECT *
FROM
    books
    LEFT OUTER JOIN
    book_reviews ON
        books.bookclub_id = book_reviews.bookclub_id
        AND books.book_id = book_reviews.book_id
        AND book_reviews.reviewer_id = 'alice'
        AND book_reviews.rating != 1
        AND book_reviews.rating != 2
WHERE books.bookclub_id = '22222222-2222-2222-2222-222222222222';
             bookclub_id              |               book_id                | bookclub_id | book_id | reviewer_id | rating
--------------------------------------+--------------------------------------+-------------+---------+-------------+--------
 22222222-2222-2222-2222-222222222222 | 00000000-0000-0000-0000-000000000000 |             |         |             |
 22222222-2222-2222-2222-222222222222 | 11111111-1111-1111-1111-111111111111 |             |         |             |
(2 rows)