Oracle SQL - 使用连接在一个表中而不是另一个表中查找值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9206962/
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
Oracle SQL - Using joins to find values in one table, and not another
提问by Jeremy
Because apparently everyone hates sub selects, I would like to do this using joins.
因为显然每个人都讨厌子选择，所以我想使用连接来做到这一点。
For an incredibly contrived example, take two tables, one with a list of numbers from 1-6 and one with a list of even numbers from 0-8. Then, my goal would be to output all odd numbers in the table Nums.
举一个非常人为的例子，有两张表，一张是 1-6 的数字列表，一张是 0-8 的偶数列表。然后，我的目标是输出表 Nums 中的所有奇数。
Table Nums
Number
One
Two
Three
Four
Five
Six
Table Even
Number
Zero
Two
Four
Six
Eight
If I just wanted to get the list of even numbers that are in Nums, I'd do...
如果我只想获得 Nums 中偶数的列表，我会做...
select nums.number
FROM nums,
even,
where nums.number = even.number;
But, how can I use these tables to get the list of non-evens in the table Nums? Or, in other words, something like...
但是，如何使用这些表来获取表 Nums 中的非偶数列表？或者，换句话说，像……
select nums.number
from nums
where nums.number not in (select number from even);
回答by Yahia
SubSELECTs are fine when used appropriately... "someone does not like something" alone is not a good enough reason IMHO.
如果使用得当，SubSELECTs 很好......恕我直言，“某人不喜欢某事”本身并不是一个足够好的理由。
There are several options - just 2 as examples:
有多种选择 - 仅以 2 个为例：
SELECT nums.number FROM nums
LEFT OUTER JOIN even ON even.number = nums.number
WHERE even.number IS NULL
OR
或者
SELECT nums.number FROM nums
MINUS
SELECT even.number FROM even
回答by dursun
for Oracle :
对于甲骨文：
select nums.number
FROM nums,
even
where nums.number = even.number(+)
and even.number is null;
for ansi SQL:
对于ANSI SQL：
SELECT nums.number
FROM nums LEFT OUTER JOIN even ON nums.number = even.number
WHERE even.number IS NULL;
