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.

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:

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.

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.

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.