SQL Help

Find free rooms: booking availability queries

Updated 26 October 2026

Based on the SQL Help series David J. Lake published here in 2006 to 2009. Rewritten for MySQL 8 by the WeFixTech editorial team.

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_idroom_name
2Red 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.