Find free rooms: booking availability queries

Updated August 2026 · First published 2006 by David J. Lake

Part of the classic SQL Help series first published on this domain by David J. Lake (guelphdad). Rewritten from scratch and updated for MySQL 8 — same address, modern SQL.

Meeting rooms, hotel rooms, hire vans, tennis courts — the question is identical: which ones are free between time A and time B? Everything hinges on one small piece of logic worth memorising:

Two intervals overlap when each starts before the other ends:
existing.start < wanted.end AND existing.end > wanted.start

Every clash, however the intervals sit — nested, straddling, identical — satisfies that condition, and nothing else does. A room is free when no booking satisfies it.

The tables

CREATE TABLE rooms (
  room_id   INT AUTO_INCREMENT PRIMARY KEY,
  room_name VARCHAR(40) NOT NULL
);

INSERT INTO rooms (room_name) VALUES
('Great Hall'), ('Red Room'), ('Big Blue'), ('Orange Room');

CREATE TABLE bookings (
  booking_id INT AUTO_INCREMENT PRIMARY KEY,
  room_id    INT NOT NULL,
  starts_at  DATETIME NOT NULL,
  ends_at    DATETIME NOT NULL,
  INDEX idx_room_time (room_id, starts_at, ends_at)
);

INSERT INTO bookings (room_id, starts_at, ends_at) VALUES
(1, '2026-08-13 09:00', '2026-08-13 09:30'),
(1, '2026-08-13 11:00', '2026-08-13 14:30'),
(2, '2026-08-13 10:00', '2026-08-13 10:30'),
(3, '2026-08-13 07:00', '2026-08-13 16:30'),
(4, '2026-08-13 09:00', '2026-08-13 11:30');

Note the booking stores only the room_id — the room's name lives once in rooms, and a join fetches it when needed.

Which rooms are free 11:00–11:30?

SELECT r.room_id, r.room_name
FROM rooms r
WHERE NOT EXISTS (
  SELECT 1
  FROM bookings b
  WHERE b.room_id   = r.room_id
    AND b.starts_at < '2026-08-13 11:30'   -- booking starts before we'd finish
    AND b.ends_at   > '2026-08-13 11:00'   -- and ends after we'd start
);

room_id	room_name
2	Red Room

Reading it aloud: "give me rooms for which no overlapping booking exists." The Great Hall is out (its 11:00–14:30 booking overlaps), Big Blue is blocked all day, Orange Room's 09:00–11:30 booking collides with our start. Only the Red Room — whose 10:00–10:30 meeting ends before we begin — survives.

NOT EXISTS is the clearest tool for "has no matching row", and with the (room_id, starts_at, ends_at) index it checks each room without scanning the whole bookings table. Two details deserve attention:

Use strict inequalities (</>). A meeting ending at 11:00 does not clash with one starting at 11:00 — treat interval ends as exclusive, or back-to-back bookings become impossible.
Boundaries as literals: in real code these are query parameters, never string-concatenated values.

Checking one specific room

SELECT EXISTS (
  SELECT 1 FROM bookings
  WHERE room_id = 2
    AND starts_at < '2026-08-13 11:30'
    AND ends_at   > '2026-08-13 11:00'
) AS is_taken;      -- 0 = free, 1 = clash

Preventing the double booking

The availability check and the insert are two statements — another user can book between them. Close the window with a transaction that re-checks under a lock:

START TRANSACTION;

SELECT booking_id
FROM bookings
WHERE room_id = 2
  AND starts_at < '2026-08-13 11:30'
  AND ends_at   > '2026-08-13 11:00'
FOR UPDATE;                -- blocks competing writers on these rows

-- application: proceed only if that returned no rows
INSERT INTO bookings (room_id, starts_at, ends_at)
VALUES (2, '2026-08-13 11:00', '2026-08-13 11:30');

COMMIT;

For low-stakes systems the plain check is usually fine; for anything where a double booking costs money, do it under a transaction like this.