不同结构SQL表联合查询：获取名称含‘Orange’的县与市数据

Got it, let's tackle this! Since [geo].[tblCity] and [geo].[tblCounty] have different structures, a direct UNION won't work—but we can fix this by making both queries return a matching set of columns first. Here's how to combine your two searches into one result set:

Step-by-Step Approach #

The key is to standardize the output columns from both queries. We'll add a helper column to distinguish between county and city records, and align the rest of the fields (using NULL for any columns that don't exist in one of the tables).

Example Query #

-- Get matching counties, with standardized columns
SELECT 
    'County' AS RecordType, -- Tells us this is a county record
    co.CountyName AS LocationName,
    co.CountyID,
    co.StateCode,
    NULL AS CityPopulation -- County table doesn't have this, so use NULL
FROM [geo].[tblCounty] co 
WHERE co.CountyName LIKE 'ORANGE%'

-- Combine with matching cities
UNION ALL

SELECT 
    'City' AS RecordType, -- Tells us this is a city record
    c.CityName AS LocationName,
    NULL AS CountyID, -- City table doesn't have this, so use NULL
    c.StateCode,
    c.Population AS CityPopulation
FROM [geo].[tblCity] c 
WHERE c.CityName LIKE 'ORANGE%'

Key Notes #

• Use UNION ALL instead of UNION: Since county and city records are distinct, we don't need to waste time removing duplicates—this makes the query faster.
• Match column count and data types: Every column position in the first query must line up with a compatible data type in the second query. If one table lacks a field, use NULL to fill the gap.
• Customize columns to your needs: You can add or remove fields as long as both queries have the same number of columns. For example, if you only care about the name and type, simplify it to just those two columns.

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