dinsdag 17 april 2012

IN, EXISTS, ANY, SOME and ALL subquery operators

Introduction

We usually use IN, EXISTS or JOINs when we want to combine a resultset with another with a subquery. Most of the time it works. But there are some operators that could become handy when you're building queries. These are ANY and ALL. SOME is also mentioned on MSDN but that is because of ANSI compatibility.

When a scalar value has to be compared with a single-column set of values, we usually use IN or JOINs. In addition to that, we can extend the comparison by using ANY and ALL operators which have rarely been used. These two operators work differently, understanding them would avoiding complexity of queries.

Preparation

I've borrowed an example from Dinesh blog for studying the IN, EXISTS, ANY(SOME) and ALL. Below you can see the script I've used for studying and demonstrating the different operators.

USE INEXISTSANYSOMEALL
GO

--Preparation scripts
DROP TABLE Table1
GO

DROP TABLE Table2
GO

CREATE TABLE Table1 (ID int)   
GO
CREATE TABLE Table2 (ID int)  
GO

TRUNCATE TABLE Table1
GO
  
INSERT INTO Table1 (ID)  
VALUES (1), (2), (3), (4), (5)   
GO

TRUNCATE TABLE Table2
GO

INSERT INTO Table2  (ID) 
VALUES (1), (3), (5) 


SELECT * FROM Table1

SELECT * FROM Table2

Resulting in


IN

The IN operator allows you to specify multiple values in a WHERE clause. For instance you could use the IN operator in the following SQL Syntax: WHERE column_name IN (value1,value2,...). But, it's also possible to use a subquery or a correlated subquery with an IN operator. Let's take a look at the following example:

Example:


SELECT ID FROM Table1  
WHERE ID IN (SELECT ID FROM Table2) 

Resulting in


Because of:

     (Table1:Id1 = Table2:Id1) ok
 OR (Table1:Id1 = Table2:Id3)
 OR (Table1:Id1 = Table2:Id5)
 OR (Table1:Id2 = Table2:Id1)
 OR (Table1:Id2 = Table2:Id3)
 OR (Table1:Id2 = Table2:Id5)
 OR (Table1:Id3 = Table2:Id1)
 OR (Table1:Id3 = Table2:Id3) ok
 OR (Table1:Id3 = Table2:Id5)
 OR (Table1:Id4 = Table2:Id1)
 OR (Table1:Id4 = Table2:Id3)
 OR (Table1:Id4 = Table2:Id5)
 OR (Table1:Id5 = Table2:Id1)
 OR (Table1:Id5 = Table2:Id3)
 OR (Table1:Id5 = Table2:Id5) ok

and a NOT IN example:

SELECT ID FROM Table1  
WHERE ID NOT IN (SELECT ID FROM Table2)   

Resulting in


Because of:

         ((Table1:Id1 <> Table2:Id1)        =
AND (Table1:Id1 <> Table2:Id3)
AND (Table1:Id1 <> Table2:Id5))
OR
          ((Table1:Id2 <> Table2:Id1)
AND  (Table1:Id2  <>  Table2:Id3)
AND  (Table1:Id2  <>  Table2:Id5))     ok
OR
           ((Table1:Id3  <>  Table2:Id1)
 AND (Table1:Id3  <>  Table2:Id3) =
 AND (Table1:Id3  <>  Table2:Id5))
 OR
           ((Table1:Id4 <> Table2:Id1)
 AND (Table1:Id4  <>  Table2:Id3)
 AND (Table1:Id4  <>  Table2:Id5))     ok
 OR
           ((Table1:Id5  <>  Table2:Id1)
 AND (Table1:Id5  <>  Table2:Id3)
 AND (Table1:Id5  <>  Table2:Id5))     =

EXISTS

The EXISTS condition is useful when the subquery returns at least one row. You can use the exist together with a correlated query, like the example below:

 SELECT ID FROM Table1   
 WHERE EXISTS (SELECT ID FROM Table2 WHERE Table1.id > Table2.id)

Resulting in:



Because of :

 (Table1:Id1 > Table2:Id1)    
 OR (Table1:Id1 > Table2:Id3)
 OR (Table1:Id1 > Table2:Id5)
 OR (Table1:Id2 > Table2:Id1)   ok
 OR (Table1:Id2 > Table2:Id3)
 OR (Table1:Id2 > Table2:Id5)
 OR (Table1:Id3 > Table2:Id1)   ok
 OR (Table1:Id3 > Table2:Id3)
 OR (Table1:Id3 > Table2:Id5)
 OR (Table1:Id4 > Table2:Id1)   ok
 OR (Table1:Id4 > Table2:Id3)   ok
 OR (Table1:Id4 > Table2:Id5)
 OR (Table1:Id5 > Table2:Id1)   ok
 OR (Table1:Id5 > Table2:Id3)   ok
 OR (Table1:Id5 > Table2:Id5)


ANY (SOME)

ANY operator returns true when the scalar value matches with any value in the single-column set of values.  The comparison can be extended with <, >, and <> operators too. You should notice that =ANY is equal to IN. But the opposite of it, <>ANY and NOT IN are NOT equal. Remember, the equal operator for NOT IN is, <>ALL. >ANY means greater than at least one value, that is, greater than the minimum. So >ANY (1, 3, 5) means greater than 1.

Example :

       
SELECT ID FROM Table1   
WHERE ID = ANY (SELECT ID FROM Table2)  
       

Resulting in



Because of:

        (Table1:Id1 = Table2:Id1) ok
 OR (Table1:Id1 = Table2:Id3)
 OR (Table1:Id1 = Table2:Id5)
 OR (Table1:Id2 = Table2:Id1)
 OR (Table1:Id2 = Table2:Id3)
 OR (Table1:Id2 = Table2:Id5)
 OR (Table1:Id3 = Table2:Id1)
 OR (Table1:Id3 = Table2:Id3) ok
 OR (Table1:Id3 = Table2:Id5)
 OR (Table1:Id4 = Table2:Id1)
 OR (Table1:Id4 = Table2:Id3)
 OR (Table1:Id4 = Table2:Id5)
 OR (Table1:Id5 = Table2:Id1)
 OR (Table1:Id5 = Table2:Id3)
 OR (Table1:Id5 = Table2:Id5) ok

Example:

SELECT ID FROM Table1   
WHERE ID <>  ANY (SELECT ID FROM Table2) 

Resulting in:


Because of:

  NOT  (Table1:Id1 = Table2:Id1)
 OR (Table1:Id1 = Table2:Id3)   ok
 OR (Table1:Id1 = Table2:Id5)   ok
 OR (Table1:Id2 = Table2:Id1)   ok
 OR (Table1:Id2 = Table2:Id3)   ok
 OR (Table1:Id2 = Table2:Id5)   ok
 OR (Table1:Id3 = Table2:Id1)   ok
 OR (Table1:Id3 = Table2:Id3)
 OR (Table1:Id3 = Table2:Id5)   ok
 OR (Table1:Id4 = Table2:Id1)   ok
 OR (Table1:Id4 = Table2:Id3)   ok
 OR (Table1:Id4 = Table2:Id5)   ok
 OR (Table1:Id5 = Table2:Id1)   ok
 OR (Table1:Id5 = Table2:Id3)   ok
 OR (Table1:Id5 = Table2:Id5)

Example :


SELECT ID FROM Table1   
WHERE ID > ANY (SELECT ID FROM Table2)   

Resulting in :


Because of:

 (Table1:Id1 > Table2:Id1)    
 OR (Table1:Id1 > Table2:Id3)
 OR (Table1:Id1 > Table2:Id5)
 OR (Table1:Id2 > Table2:Id1)   ok
 OR (Table1:Id2 > Table2:Id3)
 OR (Table1:Id2 > Table2:Id5)
 OR (Table1:Id3 > Table2:Id1)   ok
 OR (Table1:Id3 > Table2:Id3)
 OR (Table1:Id3 > Table2:Id5)
 OR (Table1:Id4 > Table2:Id1)   ok
 OR (Table1:Id4 > Table2:Id3)   ok
 OR (Table1:Id4 > Table2:Id5)
 OR (Table1:Id5 > Table2:Id1)   ok
 OR (Table1:Id5 > Table2:Id3)   ok
 OR (Table1:Id5 > Table2:Id5)

ALL

ALL operator returns true when the scalar value (of the outer query) matches with all value in the single-column set of values. The comparison can be extended with <, >, and <> operators too. For instance, using the > comparison operator, >ALL means greater than every value. In other words, it means greater than the maximum value. For example, >=ALL (1, 3, 5) means greater than or egual to 5. Let's take a look at the following example.

Example:

SELECT Id FROM Table1   
WHERE Id >= ALL (SELECT Id FROM Table2)      

Resulting in:


And this is because:

          ((Table1:Id1 >= Table2:Id1)  
AND (Table1:Id1 >= Table2:Id3)
AND (Table1:Id1 >= Table2:Id5))
OR
            ((Table1:Id2 >= Table2:Id1)
AND   (Table1:Id2 >= Table2:Id3)
AND   (Table1:Id2 >= Table2:Id5))
OR
           ((Table1:Id3 >= Table2:Id1)
 AND (Table1:Id3 >= Table2:Id3)
 AND (Table1:Id3 >= Table2:Id5))
 OR
           ((Table1:Id4 >=Table2:Id1)
 AND (Table1:Id4 >= Table2:Id3)
 AND (Table1:Id4 >= Table2:Id5))
 OR
          ( (Table1:Id5 >= Table2:Id1)
 AND (Table1:Id5 >= Table2:Id3)
 AND (Table1:Id5 >= Table2:Id5)) ok

Conclusion

I was studying for the exam 70-461 and i ran into these operators and it seems that i haven't noticed them earlier (or i've forgotten them). These seems very handy in case of values ' bigger than' or 'lesser than' in correlated queries. In these cases the syntax is more simplified. There are a couple of examples on the internet for better understanding.

Greetz,
Hennie

1 opmerking:

  1. Many thanks for the tutorial and your time,you helped me out for a little work about subquerys and these operators.

    Dani.

    https://www.facebook.com/dani.ruizlopez?ref=tn_tnmn

    BeantwoordenVerwijderen