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.
I had the same issue but instead of SUM() I had to use MAX() and to solve it I used in the second query HAVING name_of_column = MAX(name_of_column)