仅当值不存在时才返回行

2021-11-20 00:00:00 sql mysql

我有 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

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

第一个版本更直观，但我认为第二个版本通常会获得更好的性能(假设您在连接中使用的列上有索引).

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.

相关文章