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.
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';
Depending on your setup and needs, I could propose the following hack:
SELECT * FROM a subquery, that contains all the desired fields as NULL values, like so:
SELECT
*
FROM
(
SELECT
NULL as id,
NULL as email,
NULL as firstname,
NULL as lastname
) x
Then, JOIN your actual table without specifying a ON statement (or providing a ON statement that always evaluates truely, like 1).
If the table was to contain the columns id and email, the query would now return all the columns like so:
|id |email|firstname|lastname|id|email |
____________________________________________
|NULL|NULL |NULL |NULL |1 |[email protected] |
|NULL|NULL |NULL |NULL |2 |NULL |
|NULL|NULL |NULL |NULL |3 |[email protected]|
which of course is no use whatsoever. If, however, you would, for example, task php to load this resultset into an assoziative array, you would get the following:
$data = [
[
'id' => 1,
'email' => '[email protected]',
'firstname' => NULL,
'lastname' => NULL
],
[
'id' => 2,
'email' => NULL,
'firstname' => NULL,
'lastname' => NULL
],
[
'id' => 3,
'email' => '[email protected]',
'firstname' => NULL,
'lastname' => NULL
]
]
Additionally, I would like to offer an example for those who argue that no one would ever need such a thing in real life:
We collect meteorological data, and for some historical reason, we have created separate databases for every year. All containing a table with the same name and some core columns like time of measurement. Over the years, more and more columns were added. Now, if I want to create a UI to query the data, I can not simply just change the database name dynamically, since older tables may not contain all the columns. In this case, I would be fine with those queries returning NULL for those columns, but not for the queries to fail altogether.
