How to Find Uncancelled Reservations Using SQL: `NOT IN` vs. `LEFT JOIN`?

SQL Techniques for Finding Uncancelled Reservations

This article explores two efficient SQL methods to retrieve uncancelled reservations from a database with reservation and reservation_log tables. The objective is to select only those reservations lacking a cancellation record.

Method 1: Using NOT IN with a Subquery

This approach employs a subquery to identify reservation IDs present in the reservation_log table with a change_type of 'cancel'. The main query then selects reservations whose IDs are not in this list:

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

Method 2: Utilizing LEFT JOIN

A more efficient alternative uses a LEFT JOIN to combine the reservation and reservation_log tables. A LEFT JOIN returns all rows from the left table (reservation), even if there's no match in the right table (reservation_log). If a match is absent, the columns from the right table will be NULL. Filtering for NULL values in the change_type column isolates uncancelled reservations:

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;

Both methods achieve the same outcome. However, the LEFT JOIN approach is generally preferred for its improved performance, especially with larger datasets, as it avoids the potential performance issues associated with NOT IN subqueries. Choose the method that best suits your database system and data volume.

The above is the detailed content of How to Find Uncancelled Reservations Using SQL: `NOT IN` vs. `LEFT JOIN`?. For more information, please follow other related articles on the PHP Chinese website!