Impact of NULL in Aggreaget Functions

Aww. So dusty in here. Where have I been for the past seven months?!

Either way, it’s that time. Yep, you know what I mean – it’s the QUIZ TIME!

I’ve got a new quiz for you today:

What will be the result of running each of the SELECTs?
Assumptions:

• Version of Oracle: 11g R2
• Before the SELECTs were run, the following setup script had been executed:

CREATE TABLE im_empty (
  id NUMBER,
  dummy VARCHAR2(1)
);

CREATE TABLE im_NOT_empty (
  id NUMBER,
  dummy VARCHAR2(1)
);

INSERT INTO im_NOT_empty VALUES (1, 'X');
INSERT INTO im_NOT_empty VALUES (2, NULL);
INSERT INTO im_NOT_empty VALUES (NULL, NULL);

COMMIT;

The quiz is a bit different this time since you are not presented with choices to be marked as „correct/incorrect”, but rather the point is to figure out what will be the result. I’ll provide the answer in my next post. Enjoy!

The SELECTs in question follow:

SELECT COUNT(1) FROM im_NOT_empty;
SELECT COUNT(2) FROM im_NOT_empty;

SELECT COUNT(id) FROM im_NOT_empty;
SELECT COUNT(dummy) FROM im_NOT_empty;

SELECT COUNT(*) FROM im_NOT_empty;
SELECT COUNT(NULL) FROM im_NOT_empty;

SELECT SUM(id) FROM im_NOT_empty;

SELECT SUM(1) FROM im_NOT_empty;
SELECT SUM(1 + id) FROM im_NOT_empty;

SELECT SUM(id) FROM im_NOT_empty WHERE id IS NULL;
SELECT SUM(1 + id) FROM im_NOT_empty WHERE id IS NULL;
SELECT SUM(1) FROM im_NOT_empty WHERE id IS NULL;

SELECT MIN(id) FROM im_NOT_empty;
SELECT MAX(id) FROM im_NOT_empty;

SELECT COUNT(NULL) FROM im_empty;
SELECT COUNT(1) FROM im_empty;
SELECT COUNT(id) FROM im_empty;
SELECT SUM(1) FROM im_empty;