oracle 检查一个表的一列中的所有值是否存在于另一个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13125534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
check if all values in a column of one table exists in another table
提问by user1783427
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 fidand faculty_namein faculty tableand fid,class_name, room_no in another. how do i check all faculty who teaches in all the room present?
我想知道的命令检查，如果在一个表中的所有值（使用select语句创建的）出现在其他表（使用SELECT命令创建的）都在同一个选择statement.for例如，我有一个属性fid，并faculty_name在教师表和fid, class_name, room_no 在另一个。我如何检查在所有房间里教书的所有教员？
回答by Nicholas Carey
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
回答by demo.b
You can try something like this,
你可以试试这样的
select a.faculty_name, b.class_name, b.room_no
from faculty a, Table2 b
where a.fid = b.fid
回答by Luke Liu
Let's say you have two tables: faculty and class. Fid (faculty id) should be the primary key on faculty table, and foreign key on class table.
假设您有两个表：faculty 和 class。Fid（faculty id）应该是faculty表的主键，class表的外键。
Here only can be two cases you are looking for: all faculties have class or only some faculties.
这里只能是您要查找的两种情况：所有院系都有课程或只有部分院系。
To find who has class:
要查找谁有课：
SELECT
fid,
faculty_name
FROM
faculty f
INNER JOIN
class c
ON
f.fid = c.fid
To find who don't have class:
查找没有上课的人：
SELECT
fid,
faculty_name
FROM
faculty f
LEFT OUTER JOIN
class c
ON
f.fid = c.fid
WHERE
c.fid is null
