SQL select from either one or other table

Question

Assume I have a table A with a lot of records (> 100'000) and a table B with has the same columns as A and about the same data amount. Is there a possibility with one clever select statement that I can either get all records of table A or all records of table B?

I am not so happy with the approach I currently use because of the performance:

select
     column1
    ,column2
    ,column3
from (
    select 'A' as tablename, a.* from table_a a
    union
    select 'B' as tablename, b.* from table_b b
) x
where 
    x.tablename = 'A'

Answer 1

Offhand, your approach seems like the only approach in standard SQL.

You will improve performance considerably by changing the UNION to UNION ALL. The UNION must read in the data from both tables and then eliminate duplicates, before returning any data.

The UNION ALL does not eliminate duplicates. How much better this performs depends on the database engine and possibly on turning parameters.

Actually, there is another possibility. I don't know how well it will work, but you can try it:

select *
from ((select const.tableName, a.*
       from A cross join
            (select 'A' as tableName where x.TableName = 'A')
      ) union all
      (select const.tableName, b.*
       from B cross join
            (select 'B' as tableName where x.TableName = 'B')
      )
     ) t

No promises. But the idea is to cross join to a table with either 1 or 0 rows. This will not work in MySQL, because it does not allow WHERE clauses without a FROM. In other databases, you might need a tablename such as dual. This gives the query engine an opportunity to optimize away the read of the table entirely, when the subquery contains no records. Of course, just because you give a SQL engine the opportunity to optimize does not mean that it will.

Also, the "*" is a bad idea particularly in union's. But I've left it in because that is not the focus of the question.