带UNION的INTO命令两种查询写法：性能差异与通用规范咨询

云原生阿岚

2026-5-19

Great question! Let's tackle this from both performance and best practice angles.

性能差异分析

First off, for modern relational databases (think SQL Server, PostgreSQL, MySQL, etc.), there's almost no meaningful performance difference between the two common UNION+INTO patterns.

Here's why: Query optimizers are smart enough to recognize that both approaches aim for the same end goal—taking the combined results of the UNIONed queries and populating a new table. They’ll generate nearly identical execution plans for both, so you won’t see measurable differences in runtime, resource usage, or I/O.

To make this concrete, here are the two typical syntax examples:

Pattern 1 (INTO attached to the first SELECT):

SELECT col1, col2 INTO new_table
FROM table_a
UNION
SELECT col1, col2 FROM table_b;

Pattern 2 (UNION wrapped in a subquery, INTO on the outer SELECT):

SELECT * INTO new_table
FROM (
    SELECT col1, col2 FROM table_a
    UNION
    SELECT col1, col2 FROM table_b
) AS union_subquery;

The only edge case where you might spot a tiny gap is if you’re working with a very old or limited database system that doesn’t optimize subqueries well—but that’s extremely rare these days.

行业广泛认可的写法

When it comes to readability, maintainability, and compatibility, the subquery-wrapped approach (Pattern 2) is the industry standard. Here’s why:

Clearer logic flow: It explicitly signals that you first create a combined result set via UNION, then insert that entire set into the new table. This makes the code easier to follow for other developers (or future you).
Better flexibility: If you later need to add filters, sorting, or transformations to the combined result, you can do it directly in the outer query without restructuring the entire statement. For example:

SELECT * INTO new_table
FROM (
    SELECT col1, col2 FROM table_a
    UNION
    SELECT col1, col2 FROM table_b
) AS union_subquery
WHERE col1 > 100
ORDER BY col2;

Wider compatibility: While most databases support both syntaxes, some older systems or niche databases might have issues with Pattern 1 (where INTO is tied to the first SELECT in a UNION chain). The subquery method is more universally supported.

A quick side note: If you don’t need duplicate removal (which UNION does by default), use UNION ALL instead—it’s faster because it skips the deduplication step. This applies equally to both patterns.

内容的提问来源于stack exchange，提问作者Lee Y.