MySql: Fetch data only when column exists, otherwise return null

Question

I am trying to write a MYSql query in which I wanth following behaviour:

fetch position Id from table when the column position_id exists
set position_id to NULL when the column position_id does not exists in the table.

This is because we want the query to be backward compatible with older versions of our database.

SELECT
               DISTINCT(sg.id) as site_group_id,
               coalesce(sgd.name, sgdefault.name) as site_group_name,
               sg.site_group_type,
               sg.term_end,
                (SELECT CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'site_group' AND column_name = 'position_id')
                THEN sg.position_id
                ELSE NULL
            END) as position_id,
               sa.site_id
            FROM site_group sg

The above solution does not works and Mysql throws an error that Unknown column sg.position_id in field list

score 0 · Answer 1 · answered Jul 19 '23 at 09:17

I don't believe doing this within a query is possible, because MySQL checks for the existence of columns before executing the query.

I had a look into dynamic queries, but this also wouldn't help, because I'm fairly sure you'd need to execute it as a separate query. See Dynamic conversion of string into column name. MySQL

But more importantly, even if there is a way to do this, it doesn't mean you should. Running conditional queries based on an unknown schema seems like a really bad idea. I can imagine some reasons why you might be tempted to run multiple schemas for different installations, and also why you might want to have code that is backwards compatible with all of them, but those notions can, and probably should be challenged. Even if it's because the customer or manager demands it, that doesn't mean it's a good idea. They do not have your perspective, and probably aren't aware of all/any of the negative implications of this.

Already, you are running into complexity, and this is likely to only grow over time, and it can be argued, that as a professional developer, it's your responsibility to resist/prevent such poor design choices.

If you have to keep different installations running different schemas, it probably makes sense to run some kind of versioning, whereby each update to a new version will automatically apply the modified schema (e.g. adding/removing/modifying tables and columns as needed, for each iterative version.

Similarly, there are versioning solutions for code, which may allow different queries to be executed per version, whilst still outputting compatible data to the client user or service, or as a minimum, making it possible to explicitly document changes of functionality between versions.

MySql: Fetch data only when column exists, otherwise return null

1 Answers1