Comprehensive Methods for Combining Multiple SELECT Statement Results in SQL Queries

Technical Implementation of SQL Query Result Combination

In database operations, there is often a need to combine and compute results from multiple independent SELECT queries, a requirement particularly common in data aggregation and analysis scenarios. This article will explore the technical details of two mainstream implementation approaches based on a specific case study.

Problem Scenario and Basic Queries

Assume we have two data tables: resource and projects-time, each containing an hours field representing working duration. The original requirement involves calculating total working hours for each table through two separate SELECT statements:

select sum(hours) from resource;
select sum(hours) from projects-time;

This separated query approach requires result combination at the application level, increasing code complexity and network round-trip overhead.

Solution One: UNION ALL with Derived Tables

The best practice solution employs the UNION ALL operator combined with derived tables:

SELECT SUM(totalHours) totalHours
FROM
( 
    select sum(hours) totalHours from resource
    UNION ALL
    select sum(hours) totalHours from projects-time
) s

Technical Principle Analysis

The core of this solution lies in constructing a temporary derived table through the following steps:

1. Two subqueries calculate respective table sums using the same alias totalHours to ensure consistent column names
2. The UNION ALL operator vertically combines both result sets, preserving all duplicate records
3. The outer SELECT performs secondary aggregation on the combined temporary table

Table Name Delimiter Handling

Notably, the table name projects-time contains a hyphen and requires special handling in most SQL dialects. Different database systems have varying delimiter conventions:

• SQLite and MySQL use backticks: `projects-time`
• SQL Server uses square brackets: [projects-time]
• PostgreSQL uses double quotes: "projects-time"

The correct query should be adjusted to: select sum(hours) totalHours from `projects-time` (using SQLite as an example).

Solution Two: Scalar Subquery Approach

As a supplementary solution, scalar subqueries can be used to perform calculations directly within the SELECT clause:

select ((select sum(hours) from resource) +
        (select sum(hours) from projects-time)
       ) as totalHours

Solution Comparison Analysis

Advanced Application Scenarios

Multi-dimensional Result Output

When needing to output both individual component results and aggregated totals simultaneously, the strengths of both solutions can be combined:

select ResourceHours, ProjectHours, (ResourceHours+ProjectHours) as TotalHours
from (select (select sum(hours) from resource) as ResourceHours,
             (select sum(hours) from projects-time) as ProjectHours
     ) t

Dynamic Table Name Processing

In practical applications, dynamically generated table names may need processing. The following example demonstrates how to use parameterized queries in SQLite to prevent SQL injection:

-- Assuming table names are passed as parameters
SELECT SUM(totalHours) totalHours
FROM
( 
    select sum(hours) totalHours from resource
    UNION ALL
    select sum(hours) totalHours from ?
) s

Performance Optimization Recommendations

1. Index Optimization: Ensure appropriate indexing on the hours field, particularly with large table datasets
2. Query Caching: Consider materialized views or query caching for infrequently changing data
3. Batch Processing: For extremely large tables, implement batch UNION operations to reduce memory pressure

Conclusion

Through this detailed analysis, we observe that SQL provides multiple flexible approaches for combining query results. The UNION ALL solution, with its excellent readability, extensibility, and performance characteristics, emerges as the preferred approach, particularly suitable for scenarios requiring multiple data source combinations. The scalar subquery solution offers a concise alternative for simple aggregation scenarios. In practical applications, the most appropriate implementation should be selected based on specific data scale, performance requirements, and database characteristics.

It is important to note that regardless of the chosen solution, attention must be paid to SQL syntax database compatibility and proper handling of special characters, which forms the foundation for correct query execution. As data volumes grow, appropriate index design and query optimization become increasingly critical.