T-SQL aggregate function count vs. null

As we all know, aggregate functions are statistical, and the Count function is the number of rows, that is, the number of rows that satisfy a certain condition.

Let's look at this subtle relationship between count and null.

CREATE TABLE dbo. Student (Snoint  NULL, Name nvarchar ( at) ) INSERT into dbo. Student (Sno,name) VALUES (1,'Jesse'); INSERT into dbo. Student (Sno,name) VALUES (2,'Jessca'); INSERT into dbo. Student (Sno,name) VALUES (3,'June'); INSERT into dbo. Student (Sno,name) VALUES (4,'Supper'); INSERT into dbo. Student (Sno,name) VALUES (NULL,'Mike');

SELECT * FROM dbo. Student

We inserted 5 records into table student, and one of the SNO was null.

One common method is to use the count (*)

SELECT COUNT (*) from dbo. Student
SELECT COUNT (Name) from dbo. Student
SELECT COUNT (Sno) from dbo. Student
SELECT COUNT (1) from dbo. Student

Result:

People will be puzzled by this result. Why count (1) would be 5. Let's look at the next few queries below.

SELECT * FROM dbo. Studentselect Name from dbo. Studentselect Sno from dbo. Studentselect 1 from dbo. Student

　　

Select 1 is the result of the display and the rows of results in the table are matched, whether or not null. But there is a null in the SNO, and the compiler recognizes this record as invalid.

The above understanding is only a personal experience, there may not be too professional, or there are many mistakes, but also the main road great God to advise.

T-SQL aggregate function count vs. null