仅当值不存在时才返回行 [英] Return row only if value doesn't exist

问题描述

我有2张桌子-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

我只需要选择未取消的预订(在此示例中仅为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')

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

第二个版本有效，因为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.

这篇关于仅当值不存在时才返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！