Using ALL, Some and Any with Sub Queries

In case a sub Query return a single value , here you can write a normal query without using comparison operators like the following an example:

DECLARE @Employees Table (Id int , Name varchar(50) , Salary int)
INSERT INTO @Employees 
SELECT 1 ,'Fadi'  ,  10000  UNION ALL 
SELECT 2 ,'Shadi' ,  20000  UNION ALL 
SELECT 3 ,'Tareq' ,  30000  UNION ALL 
SELECT 4 ,'Rola'  ,  40000  UNION ALL 
SELECT 5 ,'Rana'  ,  50000  UNION ALL 
SELECT 6 ,'Rasha' ,  60000

-- we will use this query as sub Query return a single value
-- it's return the Average of Salary
SELECT AVG(Salary) FROM @Employees

SELECT * FROM @Employees
WHERE Salary > (SELECT AVG(Salary) FROM @Employees)

 The result of the above query:

35000


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

What if your Sub Query returns a list of values, in this case you will need to use the comparison operators to compare each value return by a sub query so to simplify these queries let's see the benefits of ALL , Any and Some.

Let's start by ALL by this example:

-- we will use this query as sub Query 
SELECT Salary FROM @Employees WHERE Salary < 40000

SELECT * FROM @Employees
WHERE  Salary > ALL 
(SELECT Salary FROM @Employees WHERE Salary < 40000)
Salary
-----------
10000
20000
30000


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

ALL keyword equal to AND comparison operator so it means the values return by a Query must be Salary > 30000 and Salary > 20000 and Salary > 10000.

Note: if the sub query return no rows, a comparison always true so means it will return all rows.

--we will use this query as sub Query 
-- this query will return no rows
SELECT Salary FROM @Employees WHERE Salary > 80000

SELECT * FROM @Employees
WHERE  Salary > ALL (SELECT Salary FROM @Employees WHERE Salary > 80000)
Salary
-----------


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
1           Fadi                                               10000
2           Shadi                                              20000
3           Tareq                                              30000
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

ANY key word equal to OR comparison operator so it means the values return by a Query must be greater than one on any values 30000, 20000 and 10000.

Note: SOME is equal to ANY but SOME (ANSI Standard keyword).

--any one condition true return the value
SELECT * FROM @Employees
WHERE  Salary > ANY (SELECT Salary FROM @Employees WHERE Salary < 40000)

--any one condition true return the value
SELECT * FROM @Employees
WHERE  Salary > SOME (SELECT Salary FROM @Employees WHERE Salary < 40000)
Id          Name                                               Salary
----------- -------------------------------------------------- -----------
2           Shadi                                              20000
3           Tareq                                              30000
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

Note: if the sub query return no rows or null value, a comparison always false so means it will return nothing.

SELECT * FROM @Employees
WHERE  Salary > ANY (SELECT Salary FROM @Employees WHERE Salary > 80000)
Id          Name                                               Salary
----------- -------------------------------------------------- -----------

Tatees journey (التطعيس)

Just I want to share these photos to any one he does not know about the Tatees (swimming in the Sand by the Car).

COALESCE and ISNULL functions

Both of these functions are similar which allow you to replace a null value of column with any expression belong to the same data type of this column except in one point, COALESCE has ability in case you need to pass a list of expressions to be replaced when the value comes NULL.

Let's see these examples:

DECLARE @Table Table
        (Col1 VARCHAR(10) NULL ,
         Col2 INT NULL,
         Col3 INT NULL)

INSERT INTO @Table
SELECT  'Microsoft',1,100 UNION ALL
SELECT  NULL,2,200        UNION ALL
SELECT  NULL,NULL,300     UNION ALL
SELECT  NULL,NULL,NULL 

--Both Functions are similar In Case of One Expression 
SELECT Col1 , ISNULL(Col1,'No Value') AS [Col1 with ISNULL] 
FROM @Table
SELECT Col1 , COALESCE(Col1,'No Value') AS [Col1 with COALESCE] 
FROM @Table

--COALESCE allows you to pass a list of expressions.
--When first Expression (in this case Col2 ) is null then 
--it will take the next expression (in this case Col3) and 
--replaced with Null value and so on.
SELECT Col1 , COALESCE(Col1,CAST(Col2 AS VARCHAR(10)),
                            CAST(Col3 AS VARCHAR(10))) AS [List of expressions wiht COALESCE] FROM @Table

2010-03-04 – Dubai Journey

I have visited Dubai city for multiple missions in a day and the missions were:

1. Purchases a car FORD Explorer 2006 4x4 .
2. Visit my lovely Uncle (Eid) and his family.
3. Get tour in Dubai country.

All praise is to Allah, I completed the missions successfully with some problems from the Customs of SAUDI ARABIA but I did not feel about this headache because the seeing of my Uncle with his family and of course the delicious meal :).

Note: from Dubai to Riyadh and vice versa about 9 hours by car (140 km per hour).

2010-01-29 - AL-Madina AL-Monawara journey

I have joined the two SharePoint Specialists in my Company Ismail and khaled to visit our blessing place AL-Madina AL-Monawara by Ismael's Car (COROLA Car) :).