How to Efficiently Retrieve Uncancelled Reservations from Two Tables?

An effective method to efficiently retrieve uncancelled reservations

This article explores how to efficiently retrieve uncancelled reservation information from two tables (reservation table and reservation log table). The reservation table stores reservation details, and the reservation log table records reservation changes.

One way is to select canceled reservations first and exclude them from the results. However, this method requires additional connections to the subscription log table to obtain canceled subscriptions, which is less efficient.

A more efficient solution is to use the NOT IN clause in a subquery:

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

This query retrieves all rows in the reservation table where the id column is not in the subquery result. The subquery selects the reservation_log values ​​in the change_type table where the reservation_id is 'cancel'.

Another option is to use the LEFT JOIN and WHERE clauses:

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;

This query uses LEFT JOIN to include all rows in the reservation table, regardless of whether they have matching rows in the reservation_log table. The WHERE l.id IS NULL clause filters out all rows in the reservation_log table where a matching row of change_type='cancel' exists, leaving only uncancelled reservations.

The above is the detailed content of How to Efficiently Retrieve Uncancelled Reservations from Two Tables?. For more information, please follow other related articles on the PHP Chinese website!