performance question: union all with or without sub-select?

[mbrenner]

i'm using a sqlite database and have to query similar fields spread across 3-5 tables, so in other words, the schema of the tables are the same.

now, what you guys think is faster, more performant?


SELECT ... FROM table1 WHERE ...
UNION ALL
SELECT ... FROM table2 WHERE ...
...
ORDER BY ...;


or


SELECT ... FROM
(SELECT * FROM table1 UNION ALL SELECT * FROM table2 ...)
WHERE ...
ORDER BY ...;


thanks

 

[PHV]

Why would you use subqueries if you don't need them ?

 

[mbrenner]

just want to add, that the WHERE statement is the same in all cases - all tables have the same scheme.

 

[PHV]

Provided the WHERE clause is against indexed columns the first approach should be far faster.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886