COUNT(expresion) returns the count of of rows where expresion is not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL will return 0, because you are only counting col_name where col_name is null, and a count of nothing but nulls is zero. COUNT(*) will return the number of rows of the query:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL
The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.
If you have rows with variable strings of space characters that you want included in the count, use:
SELECT COUNT (*) FROM TABLE WHERE trim(COL_NAME) IS NULL
trim(COL_NAME) will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes '' (empty string), which is equivalent to null in Oracle.
Oracle
docs.oracle.com › en › database › oracle › oracle-database › 26 › sqlrf › Nulls.html
Nulls
1 week ago - Note: The database currently treats ... the same as nulls. Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null....
Oracle-Base
oracle-base.com › articles › misc › null-related-functions
NULL-Related Functions - ORACLE-BASE
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id; ID COL3 ---------- ---------- 1 THREE 2 THREE 3 THREE 4 THREE 4 rows selected. SQL> The NANVL function was introduced in Oracle 10g for use with the BINARY_FLOAT and BINARY_DOUBLE datatypes, which can contain a special "Not a Number" or "NaN" value.
03:57
Comparing NULLs in Oracle SQL - YouTube
15:45
NULL Value || Oracle SQL Tutorial Video || Session 22
14:32
Oracle NULL Values | NULL Values in Oracle | NULL in SQL | What ...
04:32
NVL Function | How To Replace Null Values In Oracle SQL - YouTube
02:21
How to filter null or empty in Oracle - YouTube
01:03:16
How to handle NULL in SQL - YouTube
Top answer 1 of 9
65
COUNT(expresion) returns the count of of rows where expresion is not null. So SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL will return 0, because you are only counting col_name where col_name is null, and a count of nothing but nulls is zero. COUNT(*) will return the number of rows of the query:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL
The other two queries are probably not returning any rows, since they are trying to match against strings with one blank character, and your dump query indicates that the column is actually holding nulls.
If you have rows with variable strings of space characters that you want included in the count, use:
SELECT COUNT (*) FROM TABLE WHERE trim(COL_NAME) IS NULL
trim(COL_NAME) will remove beginning and ending spaces. If the string is nothing but spaces, then the string becomes '' (empty string), which is equivalent to null in Oracle.
2 of 9
13
A NULL column is not countable, however a row that has a NULL column is. So, this should do what you're looking for:
SELECT COUNT (*) FROM TABLE WHERE COL_NAME IS NULL OR LENGTH(TRIM (COL_NAME)) = 0
Note, that there are non-printing characters that this will not address. For example U+00A0 is the non-breaking space character and a line containing that will visually appear empty, but will not be found by the tests above.
Medium
medium.com › @DigitalFootprints › how-to-handle-null-values-in-oracle-database-a4110bf8243a
How to handle NULL values in Oracle database | by BK | Medium
September 20, 2023 - SELECT EMP_ID, NULLIF(PERSONAL_NO, HOME_NO) AS CONTACT_NO FROM EMPLOYEES; ... DECODE function works like if-then-else statements. It can have multiple arguments (max 255). DECODE function compares the expression (the first argument) to the search ...
Oracle
docs.oracle.com › en › database › other-databases › nosql-database › 26.1 › sqlreferencefornosql › is-null-and-is-not-null-operators.html
IS NULL and IS NOT NULL Operators
2 weeks ago - See BaggageInfo table in the Tables used in the Examples topic. SELECT ticketNo,fullname FROM BaggageInfo bag WHERE EXISTS bag.bagInfo.tagNum [$element IS NOT NULL] Explanation: In the airline baggage tracking application, there is a unique tag number associated with every checked bag carried by the passenger. In this query, you fetch the details of passengers who have a tag number, which means the tagNum field in the bagInfo table is not null.
Oracle
docs.oracle.com › en › database › other-databases › timesten › 22.1 › sql-reference › null-values.html
Null Values
July 25, 2025 - You can use the NULL value itself directly as an operand of an operator or predicate. For example, the (1 = NULL) comparison is supported. This is the same as if you cast NULL to the appropriate data type, as follows: (1 = CAST(NULL AS INT)).
Oracle FAQ
orafaq.com › wiki › NULL
NULL - Oracle FAQ
For example, 10 + NULL = NULL. In fact, all operators (except concatenation and the DECODE function) return null when given a null operand. Nothing is ever equal to a NULL not even NULL ("anything = NULL" evaluates as UNKNOWN): ... An empty string literal is evaluated in the same way as NULL ...
Oracle Tutorial
oracletutorial.com › home › oracle basics › oracle is null operator
Oracle IS NULL Operator
April 27, 2025 - To check if a value is NULL or not, you the IS NULL operator: ... The IS NULL returns true if the value is NULL or false otherwise.
TechOnTheNet
techonthenet.com › oracle › functions › nvl.php
Oracle / PLSQL: NVL Function
The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value. Another example using the NVL function in Oracle/PLSQL is:
DZone
dzone.com › data engineering › databases › null in oracle
NULL in Oracle - DZone
August 17, 2023 - You cannot compare any value with it using any operators: =, <, >, like ... Even the expression NULL != NULL will not be true because one cannot uniquely compare one unknown with another. By the way, this expression will not be false either because when calculating the conditions, Oracle is not ...
TechOnTheNet
techonthenet.com › oracle › isnull.php
Oracle / PLSQL: IS NULL Condition
This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.
Red Gate Software
red-gate.com › home › checking for null with oracle sql
Checking for NULL with Oracle SQL | Simple Talk
July 14, 2021 - The last example results in an error because ‘B’ cannot be converted to a number. In the one before it, it was possible to convert 2 to a varchar2 value. ... If expr1 contains a NULL value, then return expr3.
Tutorials
onlinetutorialhub.com › home › oracle sql › how to handle null in oracle sql? & what is null handling?
How to Handle NULL in Oracle SQL? & What is NULL Handling?
September 8, 2025 - Using these operators to compare any value to NULL or to compare two NULLs will yield UNKNOWN. Only when two NULLs appear in compound keys or are evaluated by a DECODE function does Oracle treat them as equal. You must use the IS NULL or IS NOT NULL operators to check for the existence or absence of NULLs. Example: To find rows where Precipitation is unknown (NULL):
Oracle
docs.oracle.com › cloud › latest › big-data-discovery-cloud › BDDEQ › ceql_literals_null.htm
Handling NULL attribute values
If an attribute value is missing for a record, then the attribute is referred to as being NULL. For example, if a record does not contain an assignment for a Price attribute, EQL defines the Price value as NULL.
O'Reilly
oreilly.com › library › view › oracle-sql-plus-the › 0596007469 › ch04s03.html
4.3. The Concept of Null - Oracle SQL*Plus: The Definitive Guide, 2nd Edition [Book]
November 16, 2004 - What about the termination date? There's no default for that column, so what's the value? The answer is there is no value. Because no value is supplied, employee_termination_date is said to be null. Example 4-20 uses the SET NULL command to make the null termination date obvious.
Author: Jonathan Gennick
Published: 2004
Pages: 584
Psoug
psoug.org › definition › NULL.htm
The Oracle NULL Value
NULL values represent missing or unknown data. NULL values are used as placeholders or as the default entry in columns to indicate that no actual data
Stanford InfoLab
infolab.stanford.edu › ~ullman › fcdb › oracle › or-nulls.html
The Behavior of NULL's in SQL
Intuitively, NULL approximately represents an unknown value. An arithmetic operation involving a NULL returns NULL. For example, NULL minus NULL yields NULL, not zero.
Oracle
docs.oracle.com › en-us › iaas › data-safe › doc › null-value.html
Null Value
Suppose you have a column named SALARY that contains salary information and you want to replace those numbers with NULL. You can apply the Null Value masking format to the SALARY column.
Upscale Analytics
ramkedem.com › home › oracle is null operator
Oracle IS NULL Operator - Upscale Analytics
May 20, 2019 - The value NULL does not equal zero (0), nor does it equal a space (‘ ‘). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on NULL values by using operators such as ‘=’ or ‘<>’. The following Oracle queries will produce no results:
W3Schools
w3schools.com › sql › sql_isnull.asp
SQL COALESCE(), IFNULL(), ISNULL(), NVL() Functions
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Training ... Operations involving NULL values can sometimes lead to unexpected results. SQL has some built-in functions to handle NULL values, and the most common functions are: COALESCE() - The preferred standard. (Works in MySQL, SQL Server and Oracle...