SQL Union not working when one table is empty

Question

Hi I am trying to read data from different tables with a SELECT statement and want to use UNION to get one dataset in the end. It is possible that one table does not (yet) contain data. In this case I can read the data from the first two successfully but once I use UNION to combine it with the data from the empty table the resulting dataset will be empty as well, even though it contained data from the first two datasets before.

My problem is, data is shifted between these tables irregularily so there is no way for me to know in which of the three tables I will find the data and in any case usually I have to combine results from more than one table to get the entire dataset I need (one contains data only 3 months back, the other two contain data for the last and the current year - depending on the period I need the data for it is possible that I have to look in all the three of them to collect all the data I need).

Do you know how I can check out all three tables and read data to collect the entire dataset I need? I also tested if everything works in case all the three tables contain data, et voila it worked and I retrieved all the data I needed. So the statement itself should be OK, just not applicable to the special case where one table does not contain data.

I am using Python to connect to the database and retrieve the data via an sql statement.

So my sql statement currently looks like this:

        sql_stmt = (
            "select " + sql_param+" from " + db_table +
            " where datum>=" + season_start_sql + " and datum<" + season_end_sql +
            " and statnr="+str(statnr) +
            " union" +
            " select " + sql_param+" from " + db_table +
            " where datum=" + season_end_sql + " and stdmin<=" + season_end_time_sql +
            " and statnr="+str(statnr) +
            " union" +
            " select " + sql_param + " from " + db_table + "_" + str(season_start.year) +
            " where datum>=" + season_start_sql + " and datum<" + season_end_sql +
            " and statnr=" + str(statnr) +
            " union" +
            " select " + sql_param + " from " + db_table + "_" + str(season_start.year) +
            " where datum=" + season_end_sql + " and stdmin<=" + season_end_time_sql +
            " and statnr=" + str(statnr) +
            " union" +
            " select " + sql_param + " from " + db_table + "_" + str(next_year) +
            " where datum>=" + season_start_sql + " and datum<" + season_end_sql + 
            " and statnr=" + str(statnr) +
            " union" +
            " select " + sql_param + " from " + db_table + "_" + str(next_year) +
            " where datum=" + season_end_sql + " and stdmin<=" + season_end_time_sql +
            " and statnr=" + str(statnr) +
            " order by datumsec")

question from:https://stackoverflow.com/questions/66045960/sql-union-not-working-when-one-table-is-empty

Answer 1

This is too long for a comment.

This statement is simply untrue:

once I use UNION to combine it with the data from the empty table the resulting dataset will be empty as well, even though it contained data from the first two datasets before.

If one of the components of a UNION is empty, then you will still get the results from the other tables.

Note: If one of the tables does not exist -- which is quite different from being empty -- then the query returns an error, but not an empty result.

I will note that in general you should be using UNION ALL instead of UNION, to avoid the overhead of removing duplicate values. And your code is wide open to SQL injection attacks and to unexpected syntax errors. I would question whether your application can be designed so you don't have to use dynamic SQL for this purpose.