如何为SELECT中指定为NULL的列添加WHERE条件？SQL报错解决

错误原因 #

• SQL执行顺序中，WHERE子句的执行早于SELECT子句，因此WHERE里无法直接引用SELECT中定义的字段别名（你在第二个子查询里用的client_name是SELECT阶段定义的别名，WHERE阶段还未生成这个别名）。
• 第二个子查询的源表on_demand_linguist_invoice本身没有client_name字段，你通过NULL AS client_name定义的该字段值全为NULL，添加client_name IS NOT NULL的过滤条件逻辑上毫无意义，还会直接排除该子查询的所有数据。

解决方案 #

直接删除第二个子查询WHERE子句中的client_name is not null条件即可。如果后续需要对合并后的结果集过滤client_name IS NOT NULL或处理分页，建议将整个UNION ALL的结果作为子查询，在外层统一处理。

修改后的基础SQL代码 #

(
select 
    `linguists`.`id` as `linguist_id`, 
    CONCAT(linguists.first_name," ", linguists.last_name) AS linguist_name, 
    `reps`.`id` as `rep_id`, 
    CONCAT(reps.first_name, " ", reps.last_name) AS rep_name, 
    `clients`.`id` as `client_id`, 
    `clients`.`name` as `client_name`, 
    `clients`.`type` as `client_type`, 
    `invoices`.`booking_id`, 
    NULL AS id, 
    `bookings`.`booking_date`, 
    `invoices`.`client_paid`, 
    `invoices`.`linguist_paid`, 
    `invoices`.`demand_letter_id`, 
    `invoices`.`reminder_1`, 
    `invoices`.`reminder_2`, 
    `invoices`.`reminder_3`,
    `invoices`.`reminder_4`, 
    `invoices`.`exceptional_case`, 
    bookings.source_company AS scope, 
    `bookings`.`credit_note`, 
    DATEDIFF(CURDATE(), invoices.created_at) AS days_since_invoice, 
    NULL AS start_date, 
    NULL AS end_date 
from `invoices` 
left join `bookings` on `bookings`.`id` = `invoices`.`booking_id` 
left join `clients` on `clients`.`id` = `bookings`.`client_id` 
left join `reps` on `reps`.`id` = `bookings`.`rep_id` 
left join `linguists` on `linguists`.`id` = `bookings`.`linguist_id` 
where 
    (
        `clients`.`name` like '%t%' 
    or 
        `clients`.`id` = 't'
    ) 
and `invoices`.`linguist_paid` is null 
and `invoices`.`invoice_submitted_linguist` < '2023-04-12' 
and `bookings`.`source_company` = 'UKLS'
) 

union all 
(
 select 
    `linguist_id`, 
    CONCAT(linguists.first_name, " ", linguists.last_name) AS linguist_name, 
    NULL AS rep_id, 
    NULL AS rep_name, 
    NULL AS client_id, 
    NULL AS client_name, 
    NULL AS client_type, 
    `on_demand_linguist_invoice`.`id`, 
    NULL AS booking_id, 
    NULL AS booking_date, 
    NULL AS client_paid, 
    `linguist_paid`, 
    NULL AS demand_letter_id, 
    NULL AS reminder_1, 
    NULL AS reminder_2, 
    NULL AS reminder_3, 
    NULL AS reminder_4, 
    NULL AS exceptional_case, 
    NULL AS scope, 
    NULL AS credit_note, 
    DATEDIFF(CURDATE(), on_demand_linguist_invoice.created_at) AS days_since_invoice, 
    `on_demand_linguist_invoice`.`start_date`, 
    `on_demand_linguist_invoice`.`end_date` 
from `on_demand_linguist_invoice` 
left join `linguists` on `linguists`.`id` = `on_demand_linguist_invoice`.`linguist_id` 
where 
    `on_demand_linguist_invoice`.`linguist_paid` is null 
    and `on_demand_linguist_invoice`.`invoice_submitted_linguist` < '2023-04-12'
)

合并后过滤+分页的进阶写法 #

如果需要保留合并结果中client_name不为空的数据并处理分页，可在外层查询统一操作：

SELECT *
FROM (
    -- 第一个子查询（内容不变）
    (
    select 
        `linguists`.`id` as `linguist_id`, 
        CONCAT(linguists.first_name," ", linguists.last_name) AS linguist_name, 
        `reps`.`id` as `rep_id`, 
        CONCAT(reps.first_name, " ", reps.last_name) AS rep_name, 
        `clients`.`id` as `client_id`, 
        `clients`.`name` as `client_name`, 
        `clients`.`type` as `client_type`, 
        `invoices`.`booking_id`, 
        NULL AS id, 
        `bookings`.`booking_date`, 
        `invoices`.`client_paid`, 
        `invoices`.`linguist_paid`, 
        `invoices`.`demand_letter_id`, 
        `invoices`.`reminder_1`, 
        `invoices`.`reminder_2`, 
        `invoices`.`reminder_3`,
        `invoices`.`reminder_4`, 
        `invoices`.`exceptional_case`, 
        bookings.source_company AS scope, 
        `bookings`.`credit_note`, 
        DATEDIFF(CURDATE(), invoices.created_at) AS days_since_invoice, 
        NULL AS start_date, 
        NULL AS end_date 
    from `invoices` 
    left join `bookings` on `bookings`.`id` = `invoices`.`booking_id` 
    left join `clients` on `clients`.`id` = `bookings`.`client_id` 
    left join `reps` on `reps`.`id` = `bookings`.`rep_id` 
    left join `linguists` on `linguists`.`id` = `bookings`.`linguist_id` 
    where 
        (
            `clients`.`name` like '%t%' 
        or 
            `clients`.`id` = 't'
        ) 
    and `invoices`.`linguist_paid` is null 
    and `invoices`.`invoice_submitted_linguist` < '2023-04-12' 
    and `bookings`.`source_company` = 'UKLS'
    ) 

    union all 

    -- 第二个子查询（内容不变，已去掉错误过滤条件）
    (
     select 
        `linguist_id`, 
        CONCAT(linguists.first_name, " ", linguists.last_name) AS linguist_name, 
        NULL AS rep_id, 
        NULL AS rep_name, 
        NULL AS client_id, 
        NULL AS client_name, 
        NULL AS client_type, 
        `on_demand_linguist_invoice`.`id`, 
        NULL AS booking_id, 
        NULL AS booking_date, 
        NULL AS client_paid, 
        `linguist_paid`, 
        NULL AS demand_letter_id, 
        NULL AS reminder_1, 
        NULL AS reminder_2, 
        NULL AS reminder_3, 
        NULL AS reminder_4, 
        NULL AS exceptional_case, 
        NULL AS scope, 
        NULL AS credit_note, 
        DATEDIFF(CURDATE(), on_demand_linguist_invoice.created_at) AS days_since_invoice, 
        `on_demand_linguist_invoice`.`start_date`, 
        `on_demand_linguist_invoice`.`end_date` 
    from `on_demand_linguist_invoice` 
    left join `linguists` on `linguists`.`id` = `on_demand_linguist_invoice`.`linguist_id` 
    where 
        `on_demand_linguist_invoice`.`linguist_paid` is null 
        and `on_demand_linguist_invoice`.`invoice_submitted_linguist` < '2023-04-12'
    )
) AS combined_result
WHERE client_name IS NOT NULL
-- 分页示例，根据需求调整偏移量和条数
LIMIT 10 OFFSET 0;

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