MySQL 8.0 - implementing UNION and Different Column Types Causing Type Conversion Errors
I'm updating my dependencies and Hey everyone, I'm running into an issue that's driving me crazy... I'm working with a question with a UNION query in MySQL 8.0 where the different data types of the columns in each part of the UNION seem to be causing type conversion errors. I have two tables, `employees` and `contractors`, both of which I want to combine into a single result set for a report. The `employee_id` in the `employees` table is an `INT`, while the `contractor_id` in the `contractors` table is a `VARCHAR`. Here's my query: ```sql SELECT employee_id AS id, name, 'employee' AS type FROM employees UNION SELECT contractor_id AS id, name, 'contractor' AS type FROM contractors; ``` When I run the query, I receive the behavior: ``` behavior 1222 (21000): The used SELECT statements have a different number of columns ``` I've ensured that both SELECT statements return three columns, but I'm also wondering if the different data types could be causing issues even though they’re both aliased to `id`. I’ve tried casting the `contractor_id` to `INT` using `CAST(contractor_id AS UNSIGNED)` but that leads to: ``` behavior 1366 (HY000): Incorrect integer value: 'some_value' ``` This occurs because some of the `contractor_id` values are non-numeric. I need a way to successfully execute this UNION without losing data or running into type conversion issues. Is there a recommended approach to handle this situation properly, or should I consider restructuring my data types for consistency? Any help would be greatly appreciated! This is part of a larger service I'm building. Any ideas what could be causing this? For context: I'm using Sql on Ubuntu. Is there a better approach? Thanks, I really appreciate it!