Using Multiple EXISTS Statement to Make Judgement Against Scenarios - Be Careful

The EXISTS statement is beloved by many SQL specialists and developers. After all, it's simple in the syntax, easy to use and quite straightforward - so straightforward that we hardly give it a thought about how it really works before we use it. That type of ignorance and arrogance got me caught in one of my previous work. I hope by sharing my experience here you're not making the same mistake I once made. Talk is cheap so let's see the code.

declare @test TABLE
       (
              CompanyID int
              ,ShareHolding int
              ,ShareHolderRank int
       )

insert into @test
values (1, 5, 2)
       ,(1, 3, 4)

select * from @test

select *
from @test T
where ShareHolding < 4
       and ShareHolderRank < 4

select *
from @test T
where exists (select 1 from @test where CompanyID = T.CompanyID and ShareHolding < 4)
       and exists (select 1 from @test where CompanyID = T.CompanyID and ShareHolderRank < 4)

Firstly, the above script creates a table variable and inserts some data into it, so that when we select everything from the table variable as the first SELECT statement does, we get the below result set


The second SELECT statement queries against the table variable with two conditions - ShareHolding < 4 and ShareHolderRand < 4. We know that no record fulfils that condition, so the second SELECT statement returns an empty result set.

Now the third SELECT statement, when we have an initial look at it, we might think - hmm, isn't it equivalent to the second SELECT statement logically? Aren't the two returning the same result set? Well, that's what I used to think, and the answer is no, they are not the same. This is what the third SELECT statement returns



What?! How come! Well, let's see how.

According to the Microsoft online Doc, the EXISTS statement 'specifies a sub-query to test for the existence of rows' and returns a Boolean value. In the third SELECT statement, we can easily tell that the sub-query in the first EXISTS statement exists , so the first EXISTS statement returns true. Similarly the second EXISTS returns true as well. Because the WHERE clause always returns a true value, we can remove it from the third SELECT statement, and it then becomes

select *
from @test T

That's why it returns what it does. So be careful when using the EXISTS statement because if you don't, you'll end up getting the unexpected result.

Comments

Post a Comment

Popular posts from this blog

SQL Server - Using SQL Agent JOBs to Schedule and Execute Multiple Stored Procedures in Parallel

This article introduces a way to schedule and kick off multiple stored procedures in parallel. Suppose we need to build an ETL system to migrate data from the source system to the target in T-SQL script; we've created multiple stored procedures (SP) each of which contains the payload of extracting data from one module of the source system and loading it to the target system; any of those stored procedures can be executed independently without relying on the execution statuses of any other stored procedures; and to speed up the whole process, we need to run multiple stored procedures simultaneously. Ideally, the above task can be handled easily in SSIS. Of course we could've created a Sequence Container and dropped a batch of SP execution tasks to it in SSIS, and then another Sequence Container containing the next batch of SP execution tasks, etc., but what if using SSIS has become a luxury for us (for example, your boss is not a fan of SSIS and prohibits you from using it as ...
Read more

sp_executesql – Let's learn How to Use It

Image
In the post SQL Server - Using SQL Agent JOBs to Scheduleand Execute Multiple Stored Procedures in Parallel , I’ve demonstrated the usage of sp_executesql and how powerful and helpful it is. This time I’ll put more explanations about it. Talk is cheap, and let’s see the code. DECLARE @ModDate datetime = '2017-12-04 00:00:00.000' SELECT        [AddressID]     , [AddressLine1]     , [AddressLine2]     , [City]     , [StateProvinceID]     , [PostalCode]     , [SpatialLocation]     , [rowguid]     , [ModifiedDate] FROM [AdventureWorks2017] . [Person] . [Address] WHERE [ModifiedDate] <= @ModDate The above code is straightforward enough. Now let’s consider the below scenarios – suppose our database name is composed by the keyword ‘AdventureWorks’ plus the year number suffix (in our example it’s 2017); ...
Read more