SQL selects from one or another table

Question

Suppose I have table A with a large number of records (> 100'000), and table B has the same columns as A, and about the same amount of data. Is it possible with one clever select statement that I can either get all the records of table A or all the records of table B?

I am not very happy with the approach that I am currently using because of 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

Offline, your approach seems to be the only approach in standard SQL.

You will greatly improve performance by changing UNION to UNION ALL . UNION must read data from both tables and then delete duplicates before returning any data.

UNION ALL does not eliminate duplicates. How much better this is done depends on the database engine and, possibly, on the rotation of the parameters.

Actually, there is one more possibility. I don't know how well this works, but you can try:

 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 a table with 1 or 0 rows. This will not work in MySQL because it does not allow WHERE clauses without FROM . In other databases, you may need a scoreboard, such as dual . This allows the query engine to fully optimize the reading of the table when there are no records in the subquery. Of course, just because you give an SQL engine, the possibility of optimization does not mean that it will be.

Also, "*" is a bad idea, especially in a union. But I left him, because this is not at the center of the issue.

Answer 2

you can try the following solution, it selects only from the tmp1 table ('A' = 'A')

 select * from tmp1 where 'A' = 'A' union all select * from tmp2 where 'B' = 'A' 

SQL Fiddle demo here check execution plan

Answer 3

It's hard to say exactly what you want, without a little context, but maybe something like this might work?

 DECLARE @TableName nvarchar(15); DECLARE @Query nvarchar(50); SELECT @TableName = YourField FROM YourTable WHERE ... SET @Query = 'SELECT * FROM ' + @TableName EXEC @Query 

The syntax may vary slightly depending on which RDBMS you are using, or rather, what you are trying to accomplish, but it may be a push in the right direction.

Answer 4

The right way to do this and maintain performance requires some modification of your physical table design.

If you can add a column to each table that contains an indicator column and add a control constraint to this column, you can achieve a section exception in the query.

DDL:

 create table table_a ( c1 ... ,c2 ... ,c3 ... ,table_ind char(1) not null generated always as 'A' ,constraint ck_table_ind check (table_ind = 'A') ); create table table_b ( c1 ... ,c2 ... ,c3 ... ,table_ind char(1) not null generated always as 'B' ,constraint ck_table_ind check (table_ind = 'B') ); create view v1 as ( select * from table_a union all select * from table_b ); 

If you run select c1,c2,c3 from v1 where table_ind = 'A' , the DB2 optimizer will use a check constraint to recognize that no rows in table_b can match the predicate table_ind = 'A' , so it will completely eliminate the table from access plan.

This was used (and in some cases so far) before DB2 for Linux / UNIX / Windows supports partitioning. You can read more about this technique in this research paper [PDF] , written by some IBM DB2 developers back in 2002.