CODEWITHSUNDEEP
sqlsql-server
user2517370
user2517370

Reputation: 113

Difference between Exists and condition in where

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.

Upvotes: 1

Views: 182

Answers (1)

Sergio Acosta
Sergio Acosta

Reputation: 11440

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.

Upvotes: 2

Related Questions