Oracle SQL-使用联接在一个表而不是另一个表中查找值 [英] Oracle SQL - Using joins to find values in one table, and not another

问题描述

因为显然每个人都讨厌子选择，所以我想使用联接来实现.

Because apparently everyone hates sub selects, I would like to do this using joins.

举一个让人难以置信的例子，拿两张桌子，一张桌子列出了1-6的数字，一张桌子列出了0-8的偶数.然后，我的目标是在表格Nums中输出所有奇数.

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.

Table Nums
Number
One
Two
Three
Four
Five
Six

Table Even
Number
Zero
Two
Four
Six
Eight

如果我只是想获取Nums中的偶数列表，我会...

If I just wanted to get the list of even numbers that are in Nums, I'd do...

select nums.number
FROM nums,
     even,
where nums.number = even.number;

但是，如何使用这些表获取Nums表中的非偶数列表?或者换句话说，就像...

But, how can I use these tables to get the list of non-evens in the table Nums? Or, in other words, something like...

select nums.number
from nums
where nums.number not in (select number from even);

推荐答案

正确使用SubSELECTs很好...恕我直言，某人不喜欢某事"还不够好.

SubSELECTs are fine when used appropriately... "someone does not like something" alone is not a good enough reason IMHO.

有几个选项-仅以2个为例:

There are several options - just 2 as examples:

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

这篇关于Oracle SQL-使用联接在一个表而不是另一个表中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！