Oracle SQL - Using joins to find values in one table, and not another

Question

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.

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...

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...

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

Answer 1

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

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

Answer 2

for Oracle :

select nums.number
  FROM nums,
       even
 where nums.number = even.number(+)
   and even.number is null;

for ansi SQL:

SELECT nums.number
  FROM nums LEFT OUTER JOIN even ON nums.number = even.number
 WHERE even.number IS NULL;