MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE rental (
  rental_id INT NOT NULL AUTO_INCREMENT,
  rental_date DATETIME NOT NULL,
  inventory_id MEDIUMINT UNSIGNED NOT NULL,
  customer_id SMALLINT UNSIGNED NOT NULL,
  return_date DATETIME DEFAULT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (rental_id),
  UNIQUE KEY  (rental_date,inventory_id,customer_id),
  KEY idx_fk_inventory_id (inventory_id),
  KEY idx_fk_customer_id (customer_id),
  KEY idx_fk_staff_id (staff_id)
  )
INSERT INTO rental VALUES (1,'2005-05-24 22:53:30',367,130,'2005-05-26 22:04:30',1,'2006-02-15 21:30:53'),
(2,'2005-05-24 22:54:33',1525,459,'2005-05-28 19:40:33',1,'2006-02-15 21:30:53'),
(15,'2005-05-25 00:39:22',3049,319,null,1,'2006-02-15 21:30:53')
 select inventory_id from rental where return_date is null


inventory_id


3049


select (1525) not in (
 select inventory_id from rental where return_date is null
);


(1525) not in (
 select inventory_id from rental where return_date is null
)


1


select (3049) not in (
 select inventory_id from rental where return_date is null
);


(3049) not in (
 select inventory_id from rental where return_date is null
)


0


select (9999) not in (
 select inventory_id from rental where return_date is null
);


(9999) not in (
 select inventory_id from rental where return_date is null
)


1


select 1 IN (
select i.inventory_id is not null from (
select 1525 inventory_id
union all
select 3049 inventory_id
union all
select 9999 inventory_id
) o left outer join (
select inventory_id from rental where return_date is null
) i using (inventory_id)
)


1 IN (
select i.inventory_id is not null from (
select 1525 inventory_id
union all
select 3049 inventory_id
union all
select 9999 inventory_id
) o left outer join (
select inventory_id from rental where return_date is null
) i using (inventory_id)
)


1