check if all values in a column of one table exists in another table

Question

I wanted to know the command to check if all the values in one table(created using select statement) is present in the other table (created using select command) all in one select statement.for eg ,i have a attribute fid and faculty_name in faculty table and fid ,class_name, room_no in another. how do i check all faculty who teaches in all the room present?

Answer 1

Poorly asked question, but

--
-- all faculty without a class
--
select *
from faculty f
where not exists ( select *
                   from class c
                   where c.fid = f.fid
                 )
--
-- all classes wihout faculty
--
select *
from class c
where not exists ( select *
                   from faculty f
                   where f.fid = c.fid
                 )
--
-- all-in-one. Each returned row represents
-- either a faculty or class without a match
-- in the other
--
select *
from      faculty f
full join class   c on c.fid = f.fid
where c.fid is null
   or f.fid is null