問題描述

我有 2 張桌子 - reservation:

I have 2 tables - reservation:

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

第二個表 - reservation_log:

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

我只需要選擇未取消的預(yù)訂(在本例中僅為 ID 3).我可以使用簡單的 WHERE change_type = cancel 條件輕松選擇取消，但我正在努力解決未取消的問題，因為簡單的 WHERE 在這里不起作用.

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.

推薦答案

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

或:

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

第一個版本更直觀，但我認為第二個版本通常會獲得更好的性能(假設(shè)您在連接中使用的列上有索引).

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).

第二個版本有效，因為 LEFT JOIN 為第一個表中的所有行返回一行.當 ON 條件成功時，這些行將包含第二個表中的列，就像 INNER JOIN 一樣.當條件失敗時，返回的行將包含第二個表中所有列的 NULL.WHERE l.id IS NULL 測試然后匹配這些行，因此它會找到表之間不匹配的所有行.

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.

這篇關(guān)于僅當值不存在時才返回行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！