Difference between Exists and condition in where

Question

I have a doubt concerning the difference between a condition in Where clause and exists or not exists. I know i will not get the same results when I use first or second way, but does someone care to explain why?

For example,

SELECT ACCOUNT_NO,
       CLIENT_NAME
FROM   ACCOUNT a
       LEFT JOIN CLIENT b
              ON a.ACCOUNT_NO = b.ACCOUNT_NO
WHERE  ACCOUNT_TYPE NOT IN ( 'A', 'B', 'C' )

AND

SELECT ACCOUNT_NO,
       CLIENT_NAME
FROM   ACCOUNT a
       LEFT JOIN CLIENT b
              ON a.ACCOUNT_NO = b.ACCOUNT_NO
WHERE  NOT EXISTS (SELECT *
                   FROM   ACCOUNT_DET t1
                          LEFT JOIN ACCOUNT t2
                                 ON t1.ACCOUNT_NO = t2.ACCOUNT_NO
                   WHERE  ACCOUNT_TYPE NOT IN ( 'A', 'B', 'C' ))

?

I hope you understand me, the table ACCOUNT_DET has almost identical fields as ACCOUNT, but this is a more simplified query than the one I used.

Your subquery is not correlated. That means if there are any rows in your subquery the outer query will not return any rows. The way you are using this you would need to correlate the subquery to the outer query. — Sean Lange, Feb 04 '15 at 17:10
Exist vs IN http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql — SoulTrain, Feb 04 '15 at 17:11
I get what you are saying, let's say i change my query to the subquery saying where EXISTS, not NOT EXISTS, how would that change the results i get? I just want to understand the difference between these two clauses, you can ignore the query if you want. — user2517370, Feb 04 '15 at 17:26
You won't get the same results cause for one you are not even querying the same tables. — paparazzo, Feb 04 '15 at 17:36

Sergio Acosta · Accepted Answer · 2015-02-04T17:30:04.247

Those are completely different queries.

Let's ignore the left join with CLIENT, which is identical in both cases.

The first query could be described as:

Return all accounts with type other than A, B or C.

The second query:

If the inner query (between ACCOUNT_DET and ACCOUNT) returns no rows, then return every record from ACCOUNT. If there is at least one row as result of the inner query then return nothing.

So, in essence, the EXISTS is evaluated to true or false. And it is equivalent to one of the following queries:

-- no rows returned by the inner query
Select ACCOUNT_NO, CLIENT_NAME 
From ACCOUNT a left join CLIENT b on a.ACCOUNT_NO=b.ACCOUNT_NO
Where NOT <false condition>

-- At least one row returned by the inner query
Select ACCOUNT_NO, CLIENT_NAME 
From ACCOUNT a left join CLIENT b on a.ACCOUNT_NO=b.ACCOUNT_NO
Where NOT <true condition>

Using inner queries to filter a resultset

I guess your intention is to have the main and inner queries related so you are able to filter each record of the main query. For that to happen you can to use a table alias from the main query inside the inner query.

A simple example could be:

select * from ACCOUNT A
where EXISTS( select * from ACCOUNT_DET D WHERE D.ACCOUNT_TYPE = A.ACCOUNT_TYPE)

Notice how the inner query is using the alias 'A' defined in the main query, so each outer row can be related to the inner query.

¿Which is better?

However, this can be also achieved by a join. I would recommend avoiding inner queries where a join can do the job for both readability and performance reasons.

Difference between Exists and condition in where

1 Answers1

Using inner queries to filter a resultset

¿Which is better?