SQL中如何使用派生列作为JOIN关联键实现字段匹配

问题背景 #

现有表结构与测试数据如下：

CREATE TABLE Person (
  id bigint,
  first_name nvarchar(60),
  last_name nvarchar(60),
  custom_employee_id nvarchar(60)
);

CREATE TABLE User (
  id bigint,
  username nvarchar(60),
  department nvarchar(60)
);

INSERT INTO Person ( id,first_name,last_name,custom_employee_id)
VALUES (1,'steve','rogers','00009094');

INSERT INTO User ( id,username,department)
VALUES( 23,'9094','accounting');

需求是将Person表的custom_employee_id字段（存在前置零及NULL值）与User表的username字段关联，但原关联查询返回的department为NULL，未得到预期的accounting结果。

原尝试的关联查询：

SELECT A.id, A.first_name, A.last_name, A.custom_employee_id
     , CAST( CAST( A.custom_employee_id AS bigint) AS nvarchar) AS trythis
     , B.department
FROM Person A
LEFT JOIN USER B ON B.username = 'trythis'
WHERE ISDECIMAL( A.custom_employee_id) =1

错误原因 #

JOIN条件中B.username = 'trythis'里的'trythis'是字符串字面量，并非SELECT子句中定义的派生列别名，数据库会将其当作普通字符串去匹配User.username，自然找不到对应数据，导致department返回NULL。

调整后的查询方案 #

方案一：在JOIN条件中直接使用转换逻辑 #

无需依赖派生列别名，直接在关联条件里对custom_employee_id做转换：

SELECT 
    A.id, 
    A.first_name, 
    A.last_name, 
    A.custom_employee_id,
    CAST(CAST(A.custom_employee_id AS bigint) AS nvarchar) AS trythis,
    B.department
FROM Person A
LEFT JOIN [User] B 
    ON B.username = CAST(CAST(A.custom_employee_id AS bigint) AS nvarchar)
WHERE ISDECIMAL(A.custom_employee_id) = 1

方案二：用CTE/子查询预处理Person表 #

先通过CTE将custom_employee_id转换后的数据单独处理，再关联User表，逻辑更清晰：

WITH ProcessedPerson AS (
    SELECT 
        id, 
        first_name, 
        last_name, 
        custom_employee_id,
        CAST(CAST(custom_employee_id AS bigint) AS nvarchar) AS normalized_emp_id
    FROM Person
    WHERE ISDECIMAL(custom_employee_id) = 1
)
SELECT 
    p.id, 
    p.first_name, 
    p.last_name, 
    p.custom_employee_id,
    p.normalized_emp_id AS trythis,
    u.department
FROM ProcessedPerson p
LEFT JOIN [User] u 
    ON u.username = p.normalized_emp_id

优化建议：处理NULL与非数字值 #

如果custom_employee_id存在NULL或无法转换为数字的值，建议使用TRY_CAST（支持SQL Server 2012+）替代CAST，避免转换失败报错：

SELECT 
    A.id, 
    A.first_name, 
    A.last_name, 
    A.custom_employee_id,
    TRY_CAST(TRY_CAST(A.custom_employee_id AS bigint) AS nvarchar) AS trythis,
    B.department
FROM Person A
LEFT JOIN [User] B 
    ON B.username = TRY_CAST(TRY_CAST(A.custom_employee_id AS bigint) AS nvarchar)
WHERE TRY_CAST(A.custom_employee_id AS bigint) IS NOT NULL

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