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?

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

Solution 2

A not exists that includes a select from dual will never return anything. Not exists will exclude rows where the embedded SQL returns something. Normally not exists should be used more like this:

select ... from MY_TABLE A where not exists (select 1 from OTHER_TABLE B where A.SOME_COL = B.SOME_COL)

Solution 3

EXISTS just returns true if a record exists in the result set; it does not do any value checking. Since the sub-query returns one record, EXISTS is true, NOT EXISTS is false, and you get no records in your result.

Typically you have a WHERE cluase in the sub-query to compare values to the outer query.

One way to accomplish what you want is to use EXCEPT:

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

Solution 4

As using NOT EXISTS is not good approach as it is return only single row so try it with MINUS or EXCEPT

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 )

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

View more solutions