combining sql statements

[Daniel_31]

I really would like to know the best way of going about this issue

I need to union 2 select statements and then do a select statement from result of the union

[cereal]

You can use a view, for example:

CREATE VIEW test_view AS SELECT fruit FROM t1 WHERE id IN(1,2) UNION SELECT fruit FROM t1 WHERE fruit = 'cherries';

Or you can use a temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS test_memory ENGINE = memory AS SELECT fruit FROM t1 WHERE id IN(1,2) UNION SELECT fruit FROM t1 WHERE fruit = 'cherries';

In the first case the union is performed each time you run the query, in the second the temporary table will survive until you close the session, so by querying:

select * from test_memory;

you will always get the cached data, nothing new unless you decide to insert new data over a new select. By removing the statement TEMPORARY the table structure will survive and the data will become accessible also to other connections, but if the engine remains memory it will loose the data when performing a reboot of the server.

To see the differences between test_view and test_memory use explain over the queries:

explain select * from test_view;
explain select * from test_memory;

Another method is to enclose the union in a subquery and perform the where statement in the primary query, for example:

SELECT sub.id, sub.fruit FROM (SELECT * FROM t1 WHERE id IN(1,2) UNION SELECT * FROM t1 WHERE fruit = 'coconut') AS sub WHERE sub.id = 5;

The execution plan of this last query is pratically the same of the one produced by the view.

Live examples: http://sqlfiddle.com/#!9/660ac/1

[janissantony]

select * from
( select_stmt1 union select_Stmt2) as temp_tbl