Return row only if value does not exist

Question

I have 2 tables - Booking:

id | some_other_column
   ---- ------------------
   1 | value
   2|value
   3 | value

Second table - reservation_log:

id | reservation_id | change_type
   -------------------------------------------------
   1 | 1 | create
   2 | 2 | create
   3 | 3 | create
   4 | 1 | cancel
   5 | 2 | cancel

I only need to select reservations that are not canceled (just ID 3 in this case). I can easily select "Cancel" using the simple WHERE change_type = cancel condition, but I have a hard time selecting "Don't cancel" because the simple WHERE doesn't work here effect.

Answer 1

For the sake of completeness (and I really believe it's more appropriate), I encourage you to use the simple NOT EXISTS.

SELECT * FROM reservation R
WHERE NOT EXISTS (
  SELECT 1 FROM reservation_log
  WHERE reservation_id = R.id
    AND change_type = 'cancel'
);

Answer 2

SELECT *
FROM reservation
WHERE id NOT IN (select reservation_id
                 FROM reservation_log
                 WHERE change_type = 'cancel')

or:

SELECT r.*
FROM reservation r
LEFT JOIN reservation_log l ON r.id = l.reservation_id AND l.change_type = 'cancel'
WHERE l.id IS NULL

The first version is more intuitive, but I think you will generally get better performance with the second version (assuming you have indexes on the columns used in the join).

The second version works because LEFT JOIN returns one row for all rows in the first table. When the ON condition succeeds, the rows will contain columns from the second table, just like INNER JOIN. When the condition fails, the rows returned will contain NULL for all columns in the second table. The WHERE l.id IS NULL test then matches those rows, so it finds any rows that don't match between the tables.