1 Quiz #2 – NULL in Aggregate Functions

Hello again! It’s due time to provide the answers for my second quiz – the one about NULL in aggregate functions.

In this particular quiz, it wasn’t the point to pick the correct choices, but rather figure out what will be the result of running each of the SELECTs. Let me quickly remind you the test data and we’ll get right to the interesting stuff:

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;

Mind the gap! NULLs, I mean!

Answers

There is one vital thing you must know about the COUNT function. It counts existence of whatever you pass as its parameter.

What does „existence” mean in this context? Well, it means something that is not a NULL. Let’s take a look at the first of the SELECTs.

SELECT #1

SELECT COUNT(1) FROM im_NOT_empty;

  COUNT(1)
----------
         3

COUNT returned 3 because there are three rows in the im_NOT_empty table and for each of the rows, we simply pass number 1 – this is something, i. e. not a NULL, so each of the rows was counted.

SELECT #2

SELECT COUNT(2) FROM im_NOT_empty;

  COUNT(2)
----------
         3

Exactly like in the SELECT #1, we pass a number to COUNT for each of the rows. It doesn’t matter what the number is because we either get 1 (existence) or 0 (non-existence).

SELECT #3

SELECT COUNT(id) FROM im_NOT_empty;

 COUNT(ID)
----------
         2

Now that’s a little bit more interesting! One of the rows in the im_NOT_empty table has a NULL in the ID column – this row will not be counted so the answer here is 2.

SELECT #4

SELECT COUNT(dummy) FROM im_NOT_empty;

COUNT(DUMMY)
------------
           1

Similarly to SELECT #3, but now there are two rows with NULL in the DUMMY column therefore only one row is counted.

SELECT #5

SELECT COUNT(*) FROM im_NOT_empty;

  COUNT(*)
----------
         3

I wonder now – is this answer surprising to you? It was to me once, because I thought that if a row has NULLs in all of the columns, then it will not be counted (the third row in our table has NULLs in both ID and DUMMY columns).

But we get 3, not 2. The reason is that in this example, when using the * (asterisk), we are checking for existence of a row, not a particular value – and there are three rows in our table.

SELECT #6

SELECT COUNT(NULL) FROM im_NOT_empty;

COUNT(NULL)
-----------
          0

Here we pass NULL for each of the rows in the im_NOT_empty table – as we know, we count non-NULLs, hence the 0 is returned.

SELECT #7

SELECT SUM(id) FROM im_NOT_empty;

   SUM(ID)
----------
         3

The answer is three, because 1 + 2 = 3. The NULL value in the third row of our test table has no impact on the result.

SELECT #8

SELECT SUM(1) FROM im_NOT_empty;

    SUM(1)
----------
         3

Here we simpy pass number 1 for each of the rows, and since we have three rows, we sum 1 + 1 + 1 and get 3 as the result.

SELECT #9

SELECT SUM(1 + id) FROM im_NOT_empty;

 SUM(1+ID)
----------
         5

We again sum the number in the ID column, but we add 1 to each of them. Adding 1 to a NULL doesn’t change the value – it will still be NULL. Hence, we end up with (1 + 1) + (2 + 1) = 5.

SELECT #10

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

   SUM(ID)
----------
           

This is one of my favourites. Did you expect a NULL or 0 here? The answer would also be NULL if the table was empty. In this example, SELECT operates on only one record which has a NULL in the ID column – there is nothing to be summed up. It’s not 0, since NULL means undefined and SUM(id) in this example yields NULL.

SELECT #11

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

 SUM(1+ID)
----------
           

Like in example #9, adding 1 to a NULL still evaluates to NULL and, like in example #10, this leaves us with SUM resulting in a NULL.

SELECT #12

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

    SUM(1)
----------
         1

Hah! Wait, what? Not a NULL? Not at all! We do not operate on a NULL here – we pass 1 for each of the found records, and the WHERE clause narrows down the records to one, so we get 1 as the result.

SELECT #13

SELECT MIN(id) FROM im_NOT_empty;

   MIN(ID)
----------
         1

NULL has no impact on the MIN function – we get what we expect: 1.

SELECT #14

SELECT MAX(id) FROM im_NOT_empty;

   MAX(ID)
----------
         2

MAX functions doesn’t look at NULLs and we get 2 as the result as we would expect. Why did I bring this example up? Well, when you sort records in descending order using ORDER BY column_name DESC clause, NULLs (if any) are first. I wanted to show that a NULL is not returned by the MAX function when there are other values.

However, if the table was empty or the column passed as the argument to MIN or MAX was NULL in all the records in the dataset, both function would return NULL.

SELECT #15

SELECT COUNT(NULL) FROM im_empty;

COUNT(NULL)
-----------
          0

Our table is empty and we get 0 as the result. What is important is that COUNT never returns NULL, even when the table is empty. You can COUNT on it to always get a number (pawn intended).

SELECT #16

SELECT COUNT(1) FROM im_empty;

  COUNT(1)
----------
         0

It doesn’t matter what we pass as the parameter when the dataset is empty – there are no records, so 1 passed as the parameter to COUNT will never be treated.

SELECT #17

SELECT COUNT(id) FROM im_empty;

 COUNT(ID)
----------
         0

Yet another varation – still, nothing to count like in examples #16 and #17 since the table is empty.

SELECT #18

SELECT SUM(1) FROM im_empty;

    SUM(1)
----------
          

The table is empty, so there is nothing to be summed up and we end up with NULL in the end.

Existence & Something

I hope you enjoyed my second quiz, and even more, that you have learnt existence!.. something, I mean!

---

If you have found any errors in my post (even typos), you think that I haven’t explained anything clearly enough or you have an idea how I could make it better – please, do not hesitate to contact me, or leave a comment.