SQL Where Not Exists

Question

I think I have a misunderstanding of how NOT EXISTS work and hope it can be clarified to me.

Here is the sample code I am running (also on SQL Fiddle)

select sum(col1) col1, sum(col2) col1, sum(col3) col3
from (
  select 1 col1, 1 col2, 1 col3
  from dual tbl1
  )
where not exists(
  select 2 col1, 1 col2, 1 col3
  from dual tbl2
)

I thought that it should return:

1, 1, 1

But instead it returns nothing.

I make this assumption only on the fact that I though NOT EXISTS would give me a list of all the rows in the first query that do not exist in the second query (in this case 1,1,1)

1. Why does this not work
2. What would be the appropriate way to make it work the way I am expecting it to?

Answer 1

You are performing an uncorrelated subquery in your NOT EXISTS() condition. It always returns exactly one row, therefore the NOT EXISTS condition is never satisfied, and your query returns zero rows.

Oracle has a rowset difference operator, MINUS, that should do what you wanted:

select sum(col1) col1, sum(col2) col1, sum(col3) col3
from (
  select 1 col1, 1 col2, 1 col3
  from dual tbl1

  MINUS

  select 2 col1, 1 col2, 1 col3
  from dual tbl2
)

SQL Server has an EXCEPT operator that does the same thing as Oracle's MINUS. Some other databases implement one or the other of these.