Whether same output is obtained for the two queries

Question

Consider the following SQL Queries executed on the relation Employee:

Employee (Eid, Ename, sal)

Eid is the primary key

Queries:

S1:SELECT count (*) FROM Employee;

S2: SELECT count (Eid) FROM Employee;

Will these queries return the same result or different results?

According to me, these queries should return same values.

But my solution manual claims that the values returned should be different. It says that S1 will also include results where Eid field is empty.

But according to me, the primary key field can never be empty. So, there will never be a case where Eid field will be empty. As this field will never be empty, the output of S1 and S2 should be equal.

Can anyone please tell me whether I am correct? If not, please tell me what I am doing wrong.

Thanks in advance!

Answer 1

where Eid field is empty.

What do you mean by "empty"? Perhaps you mean 'null'?

What words exactly does your "solution manual" use? "empty" or "null"?

If the Eid field is CHAR there could easily be a row with Eid "empty" -- that is: zero length, or containing only space characters. count(*) and count(Eid) return the same value in that case.

If the Eid field is NULLABLE there could easily be a row with Eid null. count(*) will include that row, whereas count(Eid) will not.

Usually fields in keys are expected to be NOT NULL. Some vendors' implementations of the SQL standard insist on NOT NULL. But some don't. It is good practice to make key fields NOT NULL. Presumably your manual is getting you to think about why.