Sql how to select from a table that does not exist in another table

Question

For example i have 2 tables that have relationship with one another. room_directory.id and booked_room.room_id

room_directory

id
room_number

id
booking_id
room_id

How to write query that select only the rooms from room_directory that are not exists in booked_room table?

Could you try looking up the MySQL `NOT EXISTS` expression and let us know if you have trouble implementing it. — scragar, Jun 25 '14 at 15:24
Select * from room_directory where id not in(select room_id from booked_room) — Rafal, Jun 25 '14 at 15:24
Not exists is apparently more efficient ... check here http://stackoverflow.com/questions/173041/not-in-vs-not-exists — Rafal, Jun 25 '14 at 15:26
@Khean `NOT IN` generates a list, then checks rows against the list, `NOT EXISTS` generates a pre-condition, then evaluates against that(which is likely to just be a list, and evaluated the same as `NOT IN`, but with a good index it could be far faster if MySQL just does a reverse match). — scragar, Jun 25 '14 at 15:32
Thank you seems that NOT EXISTS is the most recommended I'll choose it — Khean, Jun 25 '14 at 15:37

score 0 · Answer 1 · edited Jun 25 '14 at 15:27

0

select * from room_directory where id not in (select room_id from  booked_room)

edited Jun 25 '14 at 15:27

War10ck

answered Jun 25 '14 at 15:25

user3714601

score 0 · Accepted Answer · edited Jun 25 '14 at 15:49

0

select * from room_directory r where r.id not in (select room_id from booked_room b where r.id = b.room_id);

or

SELECT * FROM room_directory r WHERE NOT EXISTS (SELECT * from booked_room b WHERE r.id = b.room_id);

edited Jun 25 '14 at 15:49

Khean

answered Jun 25 '14 at 15:28

Auzi