SQLite技术咨询：检测空列并返回对应列名的SQL语句实现

Hey there! I’ve got a couple of straightforward approaches to solve this problem—identifying columns in a SQLite table where every row has a NULL value, then returning those column names for visibility adjustments. Let’s break them down:

1. Static Query (For Known Columns) #

If you already know the exact columns in your table, you can use a UNION ALL query to check each column individually. This works because COUNT(column_name) ignores NULL values, so a count of 0 means every row in that column is NULL.

-- Replace 'your_table' and column names with your actual table/columns
SELECT 'column1' AS empty_column
FROM your_table
HAVING COUNT(column1) = 0
UNION ALL
SELECT 'column2' AS empty_column
FROM your_table
HAVING COUNT(column2) = 0
UNION ALL
SELECT 'column3' AS empty_column
FROM your_table
HAVING COUNT(column3) = 0;

Edge Case Note: Empty Table #

If your table has no rows at all, the above query will return all columns (since COUNT returns 0 for every column). If you want to exclude this scenario (only return columns that are all-NULL when the table has rows), add a check for existing rows:

WITH table_has_rows AS (
    SELECT 1 FROM your_table LIMIT 1 -- Check if table has at least one row
)
SELECT 'column1' AS empty_column
FROM your_table, table_has_rows
HAVING COUNT(column1) = 0
UNION ALL
SELECT 'column2' AS empty_column
FROM your_table, table_has_rows
HAVING COUNT(column2) = 0;

2. Dynamic Approach (For Tables with Changing Columns) #

If your table’s schema might change (columns added/removed), manually updating the static query isn’t practical. Instead, you can use SQLite’s PRAGMA table_info() to fetch column names dynamically, then build your query programmatically. Here’s an example using Python (you can adapt this to your preferred language):

import sqlite3

def find_empty_columns(db_file, table_name):
    conn = sqlite3.connect(db_file)
    cursor = conn.cursor()

    # Fetch all column names from the table
    cursor.execute(f"PRAGMA table_info({table_name})")
    columns = [row[1] for row in cursor.fetchall()]

    # Build the dynamic query
    query_segments = []
    for col in columns:
        segment = f"SELECT '{col}' AS empty_column FROM {table_name} HAVING COUNT({col}) = 0"
        query_segments.append(segment)
    
    full_query = " UNION ALL ".join(query_segments)
    
    # Execute and collect results
    cursor.execute(full_query)
    empty_columns = [row[0] for row in cursor.fetchall()]

    conn.close()
    return empty_columns

# Usage example
empty_cols = find_empty_columns("your_database.db", "your_table")
print("Columns with all NULL values:", empty_cols)

3. Alternative: Using MAX() to Check for Non-NULL Values #

Another way to detect all-NULL columns is using MAX(column_name)—if the result is NULL, there are no non-NULL values in the column. This approach returns results as separate columns instead of rows, so you’ll need to pivot or process the output if you want a flat list:

SELECT
    CASE WHEN MAX(column1) IS NULL THEN 'column1' END AS col1_status,
    CASE WHEN MAX(column2) IS NULL THEN 'column2' END AS col2_status,
    CASE WHEN MAX(column3) IS NULL THEN 'column3' END AS col3_status
FROM your_table;

All these methods should give you the list of columns you need to adjust visibility for. Pick the one that fits your use case best!

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