如何高效查询指定TableType对应的ColumnType数据（无关联则取全部）

嘿，这个场景我之前也碰到过，既要处理有匹配关联的情况，又要兼容无匹配时返回全量数据的需求，下面给你一个高效的SQL实现方案，逻辑清晰还能保证性能：

先明确场景和数据结构 #

我们有三个表的结构如下：

CREATE TABLE [dbo].[TableType] ( 
    [id] INT NOT NULL, 
    [name] VARCHAR(100) NOT NULL, 
    CONSTRAINT [PK_TableType] PRIMARY KEY CLUSTERED ([id] ASC)
)
CREATE TABLE [dbo].[ColumnType] ( 
    [id] INT NOT NULL, 
    [name] VARCHAR(100) NOT NULL, 
    CONSTRAINT [PK_ColumnType] PRIMARY KEY CLUSTERED ([id] ASC)
)
CREATE TABLE [dbo].[TableTypeAllowedObjectType] ( 
    [id] INT IDENTITY (1, 1) NOT NULL, 
    [id_table_type] INT NOT NULL, 
    [id_column_type] INT NOT NULL, 
    [default_value] BIT NOT NULL, 
    CONSTRAINT [PK_TableTypeAllowedObjectType] PRIMARY KEY CLUSTERED ([id] ASC), 
    CONSTRAINT [FK_TableTypeAllowedColumnType_ColumnType] FOREIGN KEY ([id_column_type]) REFERENCES [dbo].[ColumnType]([id]), 
    CONSTRAINT [FK_TableTypeAllowedColumnType_TableType] FOREIGN KEY ([id_table_type]) REFERENCES [dbo].[TableType]([id])
)

测试用的示例数据：

DELETE FROM dbo.TableTypeAllowedObjectType;
DELETE FROM dbo.TableType;
DELETE FROM dbo.ColumnType;

INSERT INTO dbo.TableType (id, name) values (1, 'TableType1');
INSERT INTO dbo.TableType (id, name) values (2, 'TableType2');

INSERT INTO dbo.ColumnType (id, name) values (1, 'ColumnType1');
INSERT INTO dbo.ColumnType (id, name) values (2, 'ColumnType2');
INSERT INTO dbo.ColumnType (id, name) values (3, 'ColumnType3');

INSERT INTO dbo.TableTypeAllowedObjectType (id_table_type, id_column_type, default_value) values(1, 1, 0);
INSERT INTO dbo.TableTypeAllowedObjectType (id_table_type, id_column_type, default_value) values(1, 2, 0);

核心需求：给定一个id_table_type，查询对应的ColumnType：

• 如果TableTypeAllowedObjectType里有这个表类型的关联记录，就返回这些关联的列类型；
• 如果没有关联记录，就返回所有的列类型。

举个例子：查TableType1（id=1）返回ColumnType1、ColumnType2；查TableType2（id=2）返回全部3条列类型记录。

高效SQL实现方案 #

我推荐用UNION ALL加条件判断的写法，这个方案在执行计划上非常高效，而且逻辑直观，容易维护：

DECLARE @TargetTableTypeId INT = 1; -- 替换成你要查询的目标id_table_type

SELECT ct.id, ct.name
FROM dbo.ColumnType ct
JOIN dbo.TableTypeAllowedObjectType taot ON ct.id = taot.id_column_type
WHERE taot.id_table_type = @TargetTableTypeId

UNION ALL

SELECT ct.id, ct.name
FROM dbo.ColumnType ct
WHERE NOT EXISTS (
    SELECT 1 
    FROM dbo.TableTypeAllowedObjectType taot 
    WHERE taot.id_table_type = @TargetTableTypeId
);

为什么这个方案高效？ #

• 避免冗余扫描：UNION ALL的两个分支是互斥的，只会执行其中一个。如果目标表类型有关联记录，就只执行第一个分支的关联查询；如果没有，才会执行第二个分支的全表查询，不会做多余的操作。
• 索引友好：如果给TableTypeAllowedObjectType的id_table_type字段加个非聚集索引，EXISTS判断和第一个分支的JOIN都会跑得飞快，索引能直接定位到目标记录，不用全表扫。
• 逻辑清晰：比那些复杂的LEFT JOIN加过滤或者CASE表达式的写法好理解多了，后续维护也省心。

可选性能优化：添加索引 #

为了让查询速度再上一个台阶，建议给TableTypeAllowedObjectType加个针对id_table_type的非聚集索引，还可以把id_column_type包含进去，避免键查找：

CREATE NONCLUSTERED INDEX IX_TableTypeAllowedObjectType_id_table_type
ON dbo.TableTypeAllowedObjectType (id_table_type)
INCLUDE (id_column_type);

这个索引能让EXISTS判断和关联查询直接从索引里拿数据，不用回表查原数据，性能提升很明显。

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