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`
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.