CODEWITHSUNDEEP
mysqlsql
mdr
mdr

Reputation: 441

Return row only if value doesn't exist

I have 2 tables - reservation:

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

And 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 need to select only reservations NOT cancelled (it is only ID 3 in this example). I can easily select cancelled with a simple WHERE change_type = cancel condition, but I'm struggling with NOT cancelled, since the simple WHERE doesn't work here.

Upvotes: 9

Views: 11044

Answers (2)

Barmar
Barmar

Reputation: 780974

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 the second version usually gets better performance (assuming you have indexes on the columns used in the join).

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

Upvotes: 17

leonardo_assumpcao
leonardo_assumpcao

Reputation: 686

Just for completeness (and I honestly believe it fits better), I encourage you to use a simple NOT EXISTS.

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

Upvotes: 4

Related Questions