It became a leisure time out of most hectic QA release
day, when my friend ask me to design 10
typical query, don’t know why this SQL Server is so interesting it fuels up
energy to even a non living atmosphere. Here is my first query goes.
What will be the output of the third query if following
first two query produce the below result
select count(*) as TotalCount from
dbo.MyTable
TotalCount
-----------
9
select count(*) as WhereCount from
dbo.MyTable where
MyID = 4
WhereCount
-----------
3
select count(*) as MyCount from dbo.MyTable where MyID <> 4
WhereCount
-----------
??
Before come to any conclusion let’s stimulate the same
problem
CREATE TABLE dbo.MyTable
(
MyID INT
, MyChar VARCHAR(10)
);
GO
Command(s) completed
successfully.
Our table is ready, let’s insert some data and prepare
our environment for testing.
INSERT INTO dbo.MyTable
( MyID, MyChar )
VALUES
( 1, 'A' ),
( 2, 'B' ),
( NULL, 'C' ),
( 4, 'D' ),
( NULL, 'E' ),
( 6, 'F' ),
( 6, 'G' ),
( 4, 'H' ),
( 4, 'I' );
GO
Let’s find out the total count of rows in the table
select count(*) from dbo.MyTable
TotalCount
-----------
9
So its 9 in count, lets try to find out now of rows
where “MyID” field has value equal to 4
select count(*) as WhereCount from
dbo.MyTable where
MyID = 4
MyCount
-----------
3
So here it comes out as 3, lets now come to our asked
query output
select count(*) as MyCount from dbo.MyTable where MyID <> 4
Here we saw from previous two output that
Total row count =
9
Total row with
MyId value equal to 4 is = 3
So if we are asked
Total row with
value not equal to 4, in a simple mathematic term we can reply,
Total row with
value not equal to 4=(Total row count) – (Total row with MyId value equal to 4)
Total row with
value not equal to 4 = 9-3 = 6
Now let’s run the
query to find the real answer for the following query
select count(*) as MyCount from dbo.MyTable where MyID <> 4
MyCount
-----------
4
The output is not footing
to our thought, lets find out where we went wrong
Lets first query the table
data
MyID MyChar
----------- ----------
1 A
2 B
NULL C
4 D
NULL E
6 F
6 G
4 H
4 I
(9 row(s) affected)
From the output we can see total counts of rows are 9
in number and number of rows with MyID equal four are 3. Now if we check number
of rows with values not equal to 4:
Considering Null is 6
Not considering Null is 4
Now if we recall our query the count came out as 4
select count(*) as MyCount from dbo.MyTable where MyID <> 4
MyCount
-----------
4
This means MyID <> 4 skip Null values while evaluating count(*) function.
Conclusion: While calculating count, SQL Server skip
NULL values.
A cup of tea with SQL Server is the
greatest combination :)
Post Reference: Vikram Aristocratic Elfin Share
