MySQL: Using COALESCE and temporary variables to fill out empty column cells

In this post I will be describing two potential solutions to a specific problem in managing human-created data in a MySQL database related to the presence of NULLs in table columns.

Data entry is a tedious and demanding task on humans and the data produced often tends to contain errors and inconsistencies. This presents a problem to the database administrator who needs to manage the quality of the data in the database as well as maintain the relations between tables. One such inconsistency is the presence of empty ("") or NULL fields purposely introduced into a dataset because it contains repeated data. These empty values were introduced by the data entry clerk because they did not feel there was a need to enter the same data in multiple times. This behavior is actually understandable and has a beneficial effect because it lessens the clerk's workload, reducing the time spent on the job as well as reducing the potential for typos because the less that is typed the fewer the number of errors introduced. So it should not be seen as an error but rather it is an inconsistency in the data of the table making it unsuitable for querying and impossible to normalize even up to 1NF. It is up to the database administrator to solve this problem.

Consider the following example:

For the past two years Lucas, the Data Entry Clerk, has been working for the Royal Swedish Academy of Sciences and has been responsible for entering the information about Nobel prize laureates into the database. Unfortunately, while Lucas has been working there, he has been paid little attention as well as a poor salary. Today, Elsa, the new MySQL Database Administrator, came in to look at the data he had collected and realized she had a problem. This was the data she she saw in the table for Nobel laureates collected in 2016 and 2017:

Query: SELECT * FROM nobel_laureates;
+-------------+------+------------------------+----------------------------+-------------------------+
| laureate_id | year | field                  | fname                      | lname                   |
+-------------+------+------------------------+----------------------------+-------------------------+
|           1 | 2016 | Physics                | David                      | Thouless                |
|           2 | NULL | NULL                   | Duncan                     | Haldane                 |
|           3 | NULL | NULL                   | John                       | Kosterlitz              |
|           4 | NULL | Chemistry              | Jean-Pierre                | Sauvage                 |
|           5 | NULL | NULL                   | Fraser                     | Stoddart                |
|           6 | NULL | NULL                   | Ben                        | Feringa                 |
|           7 | NULL | Physiology or Medicine | Yoshinori                  | Ohsumi                  |
|           8 | NULL | Literature             | Bob                        | Dylan                   |
|           9 | NULL | Peace                  | Juan Manuel                | Santos                  |
|          10 | NULL | Economics              | Oliver                     | Hart                    |
|          11 | NULL | NULL                   | Bengt                      | Holmstrom               |
|          12 | 2017 | Physics                | Rainer                     | Weiss                   |
|          13 | NULL | NULL                   | Barry                      | Barish                  |
|          14 | NULL | NULL                   | Kip                        | Thorne                  |
|          15 | NULL | Chemistry              | Jacques                    | Dubochet                |
|          16 | NULL | NULL                   | Joachim                    | Frank                   |
|          17 | NULL | NULL                   | Richard                    | Henderson               |
|          18 | NULL | Physiology or Medicine | Jeffrey                    | Hall                    |
|          19 | NULL | NULL                   | Michael                    | Rosbash                 |
|          20 | NULL | NULL                   | Michael                    | Young                   |
|          21 | NULL | Literature             | Kazuo                      | Ishiguro                |
|          22 | NULL | Peace                  | International Campaign to  | Abolish Nuclear Weapons |
|          23 | NULL | Economics              | Richard                    | Thaler                  |
+-------------+------+------------------------+----------------------------+-------------------------+

So many NULLs in the year and field columns!

It became clear to her what he had done and why. Essentially, he entered the first value for the year 2016 and felt he didn't need to enter it every time for all the laureates of the same year. And for the field column, he had done the same thing, where for three people awarded for Physics in 2016, he needed only to enter the field once for the first person. She realized this needed to be fixed and considered it an interesting challenge. So how does Elsa fill down the correct data in the columns to eradicate the NULLs?

I found two solutions to the problem and there may be more. But first let's recreate the dataset so you can follow along and understand how I go about solving the problem.

-- Create the example table

CREATE TABLE nobel_laureates (

laureate_id INT(10) NOT NULL AUTO_INCREMENT,

year INT(4) DEFAULT NULL,

field VARCHAR(50),

fname VARCHAR(50) NOT NULL,

lname VARCHAR(50) NOT NULL,

UNIQUE(laureate_id)

);

-- Populate with some data with NULLs in it signifying repeated data

INSERT INTO nobel_laureates (year, field, fname, lname) VALUES

(2016, 'Physics', 'David', 'Thouless'),

(NULL, NULL, 'Duncan', 'Haldane'),

(NULL, NULL, 'John', 'Kosterlitz'),

(NULL, 'Chemistry', 'Jean-Pierre', 'Sauvage'),

(NULL, NULL, 'Fraser', 'Stoddart'),

(NULL, NULL, 'Ben', 'Feringa'),

(NULL, 'Physiology or Medicine', 'Yoshinori', 'Ohsumi'),

(NULL, 'Literature', 'Bob', 'Dylan'),

(NULL, 'Peace', 'Juan Manuel', 'Santos'),

(NULL, 'Economics', 'Oliver', 'Hart'),

(NULL, NULL, 'Bengt', 'Holmstrom'),

(2017, 'Physics', 'Rainer', 'Weiss'),

(NULL, NULL, 'Barry', 'Barish'),

(NULL, NULL, 'Kip', 'Thorne'),

(NULL, 'Chemistry', 'Jacques', 'Dubochet'),

(NULL, NULL, 'Joachim', 'Frank'),

(NULL, NULL, 'Richard', 'Henderson'),

(NULL, 'Physiology or Medicine', 'Jeffrey', 'Hall'),

(NULL, NULL, 'Michael', 'Rosbash'),

(NULL, NULL, 'Michael', 'Young'),

(NULL, 'Literature', 'Kazuo', 'Ishiguro'),

(NULL, 'Peace', 'International Campaign to ', 'Abolish Nuclear Weapons'),

(NULL, 'Economics', 'Richard', 'Thaler');

-- Keeping a backup just in case something goes wrong

CREATE TABLE nl_bkp AS SELECT * FROM nobel_laureates;

Solution 1: Create a lookup table containing filled-in information that can be used to update the rows of this table. This solution works by running a SELECT sub-query on every row to fill in the value of the column. That means we need to run the UPDATE query for as many columns (d) are required. This has a performance downside because it needs to run d times for n rows, a complexity of O(d*n), which is slow, as well as requiring a temporary table and a lot of SQL to solve the problem.

-- Creating a temporary table without repeating NULL rows

CREATE TABLE nl_filled AS

SELECT * FROM nobel_laureates WHERE year IS NOT NULL OR field IS NOT NULL ORDER BY laureate_id;

-- Fill in year using nl_filled

UPDATE nobel_laureates n

SET n.year = (SELECT f.year FROM nl_filled f WHERE f.laureate_id < n.laureate_id AND f.year IS NOT NULL ORDER BY f.laureate_id DESC LIMIT 1)

WHERE n.year IS NULL ORDER BY n.laureate_id;

-- Fill in field using nl_filled

UPDATE nobel_laureates n

SET n.field = (SELECT f.field FROM nl_filled f WHERE f.laureate_id < n.laureate_id AND f.field IS NOT NULL ORDER BY f.laureate_id DESC LIMIT 1)

WHERE n.field IS NULL ORDER BY n.laureate_id;

-- Cleanup

DROP TABLE nl_filled;

DROP TABLE nobel_laureates;

CREATE TABLE nobel_laureates AS SELECT * FROM nl_bkp;

Using the year query to explain what is going on: the UPDATE query runs through each row one by one in ORDER of laureate_id to update only the rows where year IS NULL. The inner SELECT query returns one row (LIMIT 1), the highest previous row as determined ORDER BY laureate_id DESC, from the filled-in table (nl_filled) and year is selected to update the nobel_laureates year field.

Solution 2: Use temporary variables and the COALESCE() function to store values from the previous row and assign it to the current row in order to update the value. This solution is ingenious because it requires little SQL code, updates all columns simultaneously and is fast, taking only O(n) time to solve the problem.

SET @yr = NULL;

SET @fld = NULL;

UPDATE nobel_laureates SET

year = (@yr := COALESCE(year, @yr)),

field = (@fld := COALESCE(field, @fld))

ORDER BY laureate_id;

I will be using the year column again to explain what is happening: first, we create a temporary variable @yr which we assign NULL. Then in the UPDATE query, we use COALESCE(year, @yr). The COALESCE function takes 2 or more arguments and returns the first non-NULL value it finds in the list, so for the first row that is seen in the table:

+-------------+------+------------------------+----------------------------+-------------------------+

| laureate_id | year | field                  | fname                      | lname                   |

+-------------+------+------------------------+----------------------------+-------------------------+

|           1 | 2016 | Physics                | David                      | Thouless                |

+-------------+------+------------------------+----------------------------+-------------------------+

The value in @yr is NULL and the value in column year is 2016 - so COALESCE returns 2016. This is assigned back to @yr using the special in-query assignment operator := and the new value stored in @yr is now 2016. So when it comes to the next row where year is NULL, the COALESCE function is called again and it compares NULL and @yr, and returns @yr's 2016 value to update the year column.

It is necessary to use ORDER BY laureate_id  in the query because the UPDATE command needs to run on each row in order, otherwise the result would not make sense or not be complete.

In conclusion, you can solve this problem in at least two ways in MySQL but using temporary variables and COALESCE is the best solution. Elsa had to solve this problem through lots of googling and reading stackoverflow responses but you are lucky because I posted the solution here and you are welcome to use it.

Understanding GROUP BY and when to use it (SQL)

This is a little guide I wrote for second year computer science students at KCL doing the database module. But anyone is welcome to benefit from this.

---

Imagine we have a list of students and each of these students attend a single college. We create the students table and colleges tables containing data like:

Table:Students
+---+----------+-----+
| 1 | Dave     | KCL |
| 2 | Gemma    | KCL |
| 3 | Ali      | UCL |
| 4 | Adam     | CWC |
| 5 | Claudia  | UCL |
+---+----------------+

Table:Colleges
+-----+------------------------------+
| UCL | University College London    |
| KCL | King's College London        |
| CWC | City of Westminister College |
| WLC | West London College          |
+-----+------------------------------+

Next, imagine the dean of KCL asks you: How many students attend each college?
So now you have to flex your SQL skills and you want to give him a list of college codes and the number of students attending them. Something like this:

+-----+---+
| WLC | 0 |
| CWC | 1 |
| KCL | 2 |
| UCL | 2 |
+-----+---+

But how do we achieve this? Let's give it a try and go through some common mistakes. First, let's do it the naive (newb) way:

SELECT
Colleges.code, COUNT(*) AS num_students
FROM
Colleges
JOIN
Students ON Colleges.code = Students.college_code;
Result:
+-----+---+
| CWC | 5 |
+-----+---+

Not what we wanted, right? Why does this happen? That's right, COUNT() is an aggregation function and it returns one row containing the count of all the students, and along with it the first row of the college code.

You realise you need to use GROUP BY:

SELECT
Colleges.code, COUNT(*) AS num_students
FROM
Colleges
JOIN
Students ON Colleges.code = Students.college_code
GROUP BY
Colleges.code;

+-----+---+
| CWC | 1 |
| KCL | 2 |
| UCL | 2 |
+-----+---+

Observe that because we use an INNER JOIN, WLC is not in the list. How do we correct this? That's right, you use a LEFT JOIN so all the colleges are listed.

So that's got us the correct result. Now, you think to yourself, what if he had asked me for a list of colleges with 2 or more students? So you consider using a condition in the WHERE clause:

SELECT
Colleges.code, COUNT(*) AS num_students
FROM
Colleges
JOIN
Students ON Colleges.code = Students.college_code
WHERE
num_students >= 2
GROUP BY
Colleges.code;

Result:
ERROR: Unknown column 'num_students' in 'where clause'.

OK. So we try again with COUNT(*) >= 2.

Result:
ERROR: Invalid use of group function.

So what is the correct solution?! You realise that you need to apply a condition that is related to the aggregated resultset that is returned by the query.
When you use aggregate functions like COUNT and GROUP BY, you would need to use "HAVING" like so:

SELECT
Colleges.code, COUNT(*) AS num_students
FROM
Colleges
JOIN
Students ON Colleges.code = Students.college_code
GROUP BY
Colleges.code
HAVING
num_students >= 2;

+-----+---+
| KCL | 2 |
| UCL | 2 |
+-----+---+

Just what we wanted here!

Anyway, you get back to our original task - get a list of colleges and the number of students that attend them. You know how to answer it but you want to list the colleges ordered by num_students.
You use LEFT JOIN and ORDER BY to achieve this using the query:

SELECT
Colleges.code, COUNT(*) AS num_students
FROM
Colleges
LEFT JOIN
Students ON Colleges.code = Students.college_code
GROUP BY
Colleges.code
ORDER BY
num_students DESC, code ASC;

And this gives you the output that will impress the dean, impress all who know you and establish you as an SQL guru in the department.

+-----+---+
| KCL | 2 |
| UCL | 2 |
| CWC | 1 |
| WLC | 0 |
+-----+---+