Anjali writes the following commands with respect to a table employee having fields, empno, name, department, commission :

Command1: Select count(*) from employee;
Command2: Select count(commission) from employee;

She gets the output as 4 for the first command but gets an output 3 for the second command. Explain the output with justification.

The difference in output is due to how COUNT() function behaves with different types of data in the specified column.

Command1: Select count(*) from employee; : This command counts all rows in the employee table, regardless of whether any column contains NULL values or not. When we specify an asterisk (*) with COUNT(), it returns the total number of rows in the table, including duplicates and NULL values. So, it returns the total number of rows in the table, which is 4.

Command2: Select count(commission) from employee; : If we specify argument (expr) in COUNT() function, it returns rows where expr is not null. This command counts the number of non-NULL values in the commission column. Therefore, it returns a count of 3 instead of 4 due to the presence of a NULL value in that column in one of the rows.