Merging SQL Query Results: Comprehensive Guide to JOIN Operations on Multiple SELECT Statements

Introduction

In database application development, there is often a need to merge multiple query result sets into a comprehensive data view. This requirement is particularly common in scenarios such as report generation, data analysis, and business statistics. Based on a practical task management database case, this paper thoroughly explores how to efficiently merge results from multiple SELECT queries through SQL statements.

Problem Context and Requirements Analysis

Consider a task management system containing a task table (Table) that stores detailed information for each task, including the responsible person (ks field) and time parameters related to task deadlines (PALT and Age fields). The business requirement is to generate a statistical report showing the total number of tasks and the number of overdue tasks for each responsible person.

The initial solution involves two separate SELECT statements to obtain the data:

-- Get total tasks per responsible person
SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks

-- Get overdue tasks per responsible person
SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks

While this separated query approach is simple and intuitive, it has significant limitations: additional processing steps are required to merge the two result sets, and there is no guarantee that all responsible persons will appear in the final result (particularly those without any overdue tasks).

Core Solution: Combining Subqueries with LEFT JOIN

Based on best practices, we employ an approach that combines subqueries with LEFT JOIN to meet the requirements:

SELECT 
    t1.ks, 
    t1.[# Tasks], 
    COALESCE(t2.[# Late], 0) AS [# Late]
FROM 
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks);

Technical Points Analysis

The core of this solution lies in several key technical choices:

1. Use of Subqueries: Two independent statistical queries are used as subqueries to calculate total tasks and overdue tasks separately. This approach maintains clarity in query logic while providing the foundation for subsequent join operations.

2. Selection of LEFT JOIN: Using LEFT JOIN instead of INNER JOIN is crucial for ensuring result completeness. LEFT JOIN preserves all records from the left table (total tasks query), even when there are no matching records in the right table (overdue tasks query). This guarantees that responsible persons without any overdue tasks still appear in the final result.

3. NULL Value Handling with COALESCE: For responsible persons without overdue tasks, the overdue task count from the right table will be NULL. Using COALESCE(t2.[# Late], 0) converts these NULL values to 0, ensuring data integrity and readability.

Alternative Solutions Comparative Analysis

In addition to the optimal solution described above, other viable implementation approaches exist, each with its applicable scenarios and limitations.

Alternative 1: INNER JOIN Approach

SELECT *
FROM
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1 
INNER JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON t1.ks = t2.ks

The limitation of this approach is that it only returns records for responsible persons who exist in both subqueries. If a responsible person has tasks but no overdue tasks, they will not appear in the final result, failing to meet the business requirement of "displaying all responsible persons."

Alternative 2: Conditional Counting Approach

SELECT 
    ks,
    COUNT(*) AS '# Tasks',
    SUM(CASE WHEN Age > Palt THEN 1 ELSE 0 END) AS '# Late'
FROM Table
GROUP BY ks

This single-query approach is more concise and efficient, calculating both total tasks and overdue tasks simultaneously within the same GROUP BY operation using conditional expressions. The advantage is better performance and more compact code; the disadvantage is that it may be less intuitive than separated queries in complex conditions.

Technical Extensions: Other Result Set Merging Methods

Beyond JOIN operations, SQL provides various methods for merging result sets, suitable for different business scenarios.

UNION Operator

UNION is used to combine result sets from multiple SELECT statements, automatically removing duplicate rows:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

UNION requires that all SELECT statements have the same number of columns, and corresponding columns must have compatible data types. Using UNION ALL preserves all duplicate rows.

EXCEPT and INTERSECT Operators

EXCEPT returns records that exist in the first query but not in the second:

SELECT column1 FROM table1
EXCEPT
SELECT column1 FROM table2

INTERSECT returns records that exist in both queries:

SELECT column1 FROM table1
INTERSECT
SELECT column1 FROM table2

Performance Optimization Considerations

In practical applications, query performance is a critical factor to consider:

Index Optimization: Creating appropriate indexes on query condition fields such as ks, Age, and Palt can significantly improve query performance.

Query Complexity Trade-offs: Single conditional counting queries typically perform better than multiple subqueries with JOINs, but separated queries may be easier to understand and maintain in complex business logic scenarios.

Temporary Table Alternatives: For extremely complex multi-step data processing, consider using temporary tables to store intermediate results, though this increases I/O overhead and code complexity.

Practical Application Recommendations

Based on different business scenarios and technical requirements, the following recommendations are provided:

1. For simple statistical requirements, prioritize single-query approaches using conditional counting

2. When business logic is complex or requires step-by-step processing, adopt the subquery with LEFT JOIN approach

3. Ensure result set completeness, paying special attention to null value handling and edge cases

4. Conduct thorough performance testing and optimization in production environments

Conclusion

Through proper SQL statement design, result sets from multiple SELECT queries can be efficiently merged to meet complex business statistical requirements. The LEFT JOIN combined with subqueries approach ensures data integrity while providing good readability and maintainability. In actual development, the most appropriate implementation method should be selected based on specific business scenarios, balancing performance, readability, and functional requirements.