MYSQL查找外键不在相关表中的行

Question

I have reservations that can be made in advance. On the day of the reservation, the devices reserved can be checked out.

I have two tables, reservations and checkouts. Reservations have many checkouts.

How would I construct a query that would return all reservations for a particular date that do NOT have associated checkout records?

To put it another way, all rows from reservations where reservation_id column does not contain the reservation's ID?

So far, my best guess is

SELECT * FROM reservations WHERE reservations.id NOT IN (SELECT reservation_id 
FROM checkouts)

But that returns empty. Here's a rough idea what the tables look like

|reservations|  |checkouts         |
|id = 1      |  |reservation_id = 1|
|id = 2      |  |reservation_id = 2|
|id = 3      |  

My result should be reservation 3.

P.S. If php is required, that's fine.

Answer 1

Most likely explanation for the query returning no rows is that there are rows in checkouts that have a NULL value for reservation_id. Consider:

4 NOT IN (2,3,5,NULL,11)

In interpreting this, the NULL value is seen as meaning "unknown what the value is". Is 4 in that list? The answer (coming back from SQL) is basically "unknown" whether 4 matches the "unknown" value in the list.

If that's what's causing the behavior, you can "fix" your current query by including WHERE reservation IS NOT NULL in the subquery.

SELECT r.* 
  FROM reservations r
 WHERE r.id NOT IN ( SELECT c.reservation_id 
                       FROM checkouts c
                      WHERE c.reservation_id IS NOT NULL
                   )

This may not be the most efficient approach to returning the specified result. An anti-join is a common pattern for returning this type of result. In your case, that would be an outer join, to return all rows from reservations, along with matching rows from checkouts, and then a predicate in the WHERE clause to filter out all the rows that had a match, leaving us with rows from reservations that didn't have a match.

For example:

SELECT r.*
  FROM reservations r
  LEFT
  JOIN checkouts c
    ON c.reservation_id = r.reservation_id
 WHERE c.reservation_id IS NULL

It's also possible to get an equivalent result with a NOT EXISTS with a correlated subquery.

SELECT r.*
  FROM reservations r
 WHERE NOT EXISTS ( SELECT 1
                      FROM checkouts c
                     WHERE c.reservation_id = r.reservation_id
                   )