MySQL ‘Column X cannot be null’ When Performing UNION ALL

Whilst recently trying to run a query that involved a UNION ALL, I got an error claiming that a column could not be NULL. Running each of the queries individually worked fine so I knew I had to dig a bit deeper.

Here’s a simplified version of my query:

1. SELECT   
2.     `amount1` AS amount  
3. FROM  
4.     `table1`  
5.   
6. UNION ALL  
7.   
8. SELECT  
9.     SUM(`amount2`) AS amount  
10. FROM  
11.     `table2`  

The Solution

It turns out that the error was caused because of the aggregate SUM() function in the second query. Adding a GROUP BY to the query prevented the error and returned the resultset as expected.