SQL筛选非空字段：规避weight_group别名报错及无需子查询的方案

Let's tackle both your requirements clearly, starting with the specific error you ran into since that's the immediate pain point.

Why the WHERE weight_group IS NOT NULL Failed #

First, quick SQL 101 reminder: The logical execution order of SQL clauses means WHERE runs before SELECT. That alias weight_group you define in SELECT doesn't exist yet when the database evaluates the WHERE clause—hence the "Invalid Identifier" error. Got it? Good.

Requirement 2: Show Only Rows with Non-Null weight (No Subqueries, No Changing ELSE '') #

Since your goal is to filter for rows where weight itself isn't null (not just non-empty weight_group values), you don't need to reference the alias at all. Just add a direct check on the original weight column in your WHERE clause:

SELECT 
  player_name,
  weight,
  CASE 
    WHEN weight > 250 THEN 'over 250' 
    WHEN weight > 200 THEN '201-250' 
    WHEN weight > 175 THEN '176-200' 
    ELSE '' 
  END AS weight_group 
FROM benn.college_football_players
WHERE weight IS NOT NULL; -- Direct check on the original table column

This works because weight is a native column from the table, so it's available during the WHERE execution phase. No subqueries needed, and you leave the ELSE '' clause untouched—perfect for avoiding those widespread query errors you mentioned.

Requirement 1: Filter for Non-Empty Dynamic Columns (CASE ... END AS) #

If you did need to filter based on the value of that dynamically generated weight_group (not just the original weight), and you can't use a subquery, here are two solid options:

Option 1: Repeat the CASE Statement in the WHERE Clause #

Since you can't use the alias, you can reuse the entire CASE logic directly in the WHERE clause. This is a bit verbose but universally supported across all SQL databases:

SELECT 
  player_name,
  weight,
  CASE 
    WHEN weight > 250 THEN 'over 250' 
    WHEN weight > 200 THEN '201-250' 
    WHEN weight > 175 THEN '176-200' 
    ELSE '' 
  END AS weight_group 
FROM benn.college_football_players
WHERE 
  weight IS NOT NULL -- Keep your original weight filter
  AND CASE 
        WHEN weight > 250 THEN 'over 250' 
        WHEN weight > 200 THEN '201-250' 
        WHEN weight > 175 THEN '176-200' 
        ELSE '' 
      END <> ''; -- Filter out empty weight_group values

Note: We use <> '' instead of IS NOT NULL here because your ELSE returns an empty string (''), not a NULL value.

Option 2: Use a CTE (Common Table Expression) #

CTEs are a cleaner alternative to subqueries and are widely supported in modern databases (PostgreSQL, BigQuery, MySQL 8+, etc.). They run before the main query, so the weight_group alias is fully available in the final WHERE clause:

WITH player_weights AS (
  SELECT 
    player_name,
    weight,
    CASE 
      WHEN weight > 250 THEN 'over 250' 
      WHEN weight > 200 THEN '201-250' 
      WHEN weight > 175 THEN '176-200' 
      ELSE '' 
    END AS weight_group 
  FROM benn.college_football_players
)
SELECT *
FROM player_weights
WHERE weight IS NOT NULL AND weight_group <> '';

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