How to Write a SQL Query to Return Only 0 or NULL Values in Excel and WPS Spreadsheets
Question details
The user needs to return rows where a column is 0 or NULL, and to understand why SQL conditions such as <> 1, IN, and NOT = 1 do not include NULL values when results are displayed in a spreadsheet grid.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is querying a database and exporting or displaying the result set in Excel or WPS Spreadsheets, expecting rows with zero and NULL values to appear correctly.
- Observed behavior
- Standard SQL comparison operators do not treat NULL as a value, so filters like <> 1, IN (0), or NOT = 1 skip NULL rows; database NULLs may also appear as zero or blank after export to a spreadsheet.
Before changing your SQL query, check how your database platform treats NULL in comparisons and whether your export target, Excel or WPS Spreadsheets, converts database NULLs to blank or zero.
Use IS NULL and = 0 in the SQL WHERE clause to include NULL and zero rows
Use this when you need the result set to contain both stored zeroes and database NULLs, because SQL comparisons such as <> 1 or IN (0) exclude NULL.
In SQL, NULL means unknown. Equality and inequality comparisons do not evaluate to true for NULL, so a condition like column_name <> 1 removes NULL rows even if you expected them to remain.
Open your SQL editor, query console, or database management tool and locate the SELECT statement that returns the rows you want to filter.
Change the WHERE clause to WHERE column_name IS NULL OR column_name = 0, or use COALESCE(column_name, 0) = 0, instead of WHERE column_name <> 1, NOT column_name = 1, or IN (0) alone.
Execute the query and confirm that both NULL and 0 rows are returned before you export the data to WPS Spreadsheets or Excel.
Return an empty string or verify the SQL data directly when NULL appears as zero in the spreadsheet
Use this when SQL returns NULL correctly but a spreadsheet grid such as Excel or WPS Spreadsheets displays blanks as zero after export.
Use WPS Office to Review SQL Export Results Without Excel NULL Display Issues
The SQL NULL behavior is controlled by your database query, not by WPS Office. When the display issue comes from Excel's treatment of NULL cells, WPS Office offers a free, lightweight spreadsheet alternative that opens .xlsx, .xls, and .csv files with a familiar interface, making it easy to inspect exported SQL results and migrate from Microsoft Excel.
- 1. Download WPS Office: Go to the official WPS website and download WPS Office for your device.
- 2. Open WPS Spreadsheets: Install and open WPS Spreadsheets from the WPS Office suite.
- 3. Open the SQL export file: Open the exported .xlsx, .xls, or .csv file from your database tool or Excel, and inspect NULL, blank, and zero cells before sharing.
Frequently Asked Questions
Why does WHERE column_name <> 1 not return NULL rows?
In SQL, NULL means unknown, so any comparison with NULL is not true. Use WHERE column_name IS NULL OR column_name = 0, or a NULL-safe expression such as COALESCE(column_name, 0) = 0, to include NULL rows.
How do I write a SQL query that returns only 0 or NULL values?
Use an explicit NULL-safe condition such as WHERE column_name IS NULL OR column_name = 0. Avoid IN (0), <> 1, or NOT = 1 alone, because they exclude NULL unless you also add IS NULL.
Why does my database NULL show as 0 in Excel or WPS Spreadsheets?
Spreadsheet cells do not have a true database NULL type. Some export tools or grid displays convert NULL to blank, and a later import may interpret that blank as zero. Use COALESCE(column_name, '') in SQL or check the data directly in your database before exporting.
Can WPS Spreadsheets fix SQL NULL values?
No, SQL NULL handling must be fixed in the query. WPS Spreadsheets can open SQL export files and help you inspect blank, NULL-derived, and zero cells, but it cannot change how your database returns NULL values.
