SQL Server中不修改原有SELECT语句能否新增结果集列？

Absolutely, you can achieve this goal in SQL Server—no need to tweak your original SELECT statement or wrestle with UNION's strict column count/data type/order rules. Here are two straightforward approaches:

1. Wrap Original Query as a Subquery and Join Back to Table1 #

Since your original query already pulls data from Table1, you can wrap that query in a subquery, then join back to Table1 to fetch the col8 column. Just make sure you use a unique identifier from Table1 (like a primary key) for the join to avoid duplicate rows.

SELECT 
    original_results.*,
    T1.col8
FROM (
    -- Your unmodified original SELECT statement
    SELECT T1.col1, T1.col2, T2.col3, T2.col4 from Table1 T1 join Table2 T2
) AS original_results
-- Replace col1 with Table1's primary key or unique identifier if needed
JOIN Table1 T1 ON original_results.col1 = T1.col1;

2. Use a CTE (Common Table Expression) for Better Readability #

If you prefer cleaner code, a CTE works the same way as the subquery approach but is easier to read and maintain, especially for more complex queries:

WITH original_results AS (
    -- Your unmodified original SELECT statement
    SELECT T1.col1, T1.col2, T2.col3, T2.col4 from Table1 T1 join Table2 T2
)
SELECT 
    original_results.*,
    T1.col8
FROM original_results
-- Again, use a unique identifier from Table1 for the join
JOIN Table1 T1 ON original_results.col1 = T1.col1;

Key Notes: #

• Why this avoids UNION issues: UNION is designed to combine rows from separate result sets, which requires matching column counts, data types, and order. We're instead extending your existing result set by joining back to the source table—this is a completely different operation with none of UNION's restrictions.
• Join condition: Always use a unique, reliable identifier (like a primary key column) from Table1 for the join. If col1 isn't unique, replace it with a column (or combination of columns) that uniquely identifies each row in Table1.

内容的提问来源于stack exchange，提问作者Santosh