Multiple choice technology databases

There are 10 tables with similar structure but mutually exclusive records. A query needs to fetch some records from each of these tables. Which of the following is the best approach to do this?

  1. Use UNION between the 10 SELECT statements

  2. Execute separate SQL statements and let the front-end merge the results

  3. Use UNION ALL between the 10 SELECT statements

  4. Define a temporary table and populate it with data from the 10 SELECT statements and then do a single SELECT from this table
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

UNION ALL combines results from multiple tables without duplicate checking. Since the tables have mutually exclusive records, no duplicates exist, so the overhead of duplicate elimination in UNION is unnecessary. UNION is slower because it must sort and compare rows to remove duplicates. Separate SQL statements with front-end merging adds application complexity. A temporary table introduces unnecessary I/O and complexity. UNION ALL is the most efficient approach for mutually exclusive data.

Keep practicing — related questions