mysql

MySQL SELECT a field as NULL if not existent in table


I like to use a SELECT on a table that maybe contains some field, but maybe not. If not, the value could be returned as NULL like JOIN LEFT would do for not existing rows.

Eg. something like this Pseudo-SQL:

SELECT id, email IF EXISTS, name, address FROM users;

should run without errors on a table 'users' without an 'email' field but return email=NULL then.


Solution

  • What you want can not be done in pure SQL.

    Essentially, you want SQL that can conditionally select a column that might not exist. Such SQL could not be parsed - all columns selected must exist or the query will be invalid.

    You can however achieve this is application code by querying the catalog tables to inspect the schema of the database you're connected to and dynamically build you SQL based on that.

    This query may help your app code to build your query:

    select COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = 'users'
    and TABLE_SCHEMA = 'YOUR-DB-NAME';