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:

SELECT 
	`amount1` AS amount
FROM
	`table1`

UNION ALL

SELECT
	SUM(`amount2`) AS amount
FROM
	`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.