SuiteQL Empty Strings Are NULL: Why Optional Filters Return No Rows

An optional SuiteQL filter can look perfectly reasonable and still remove every row from your results.

The trap usually looks like this:

AND (
    ? = ''
    OR customer.entityid = ?
)

The intention is straightforward: if the parameter is blank, skip the filter; otherwise, match the supplied value.

In SuiteQL, that first condition does not become TRUE. It becomes unknown, because Oracle treats a zero-length character value as NULL.

That small difference is enough to make an otherwise valid query return nothing.

Why an empty-string comparison fails

SuiteQL supports Oracle SQL syntax. In Oracle Database, a character value with a length of zero is treated as NULL. Oracle also specifies that ordinary comparisons involving NULL evaluate to UNKNOWN, not TRUE or FALSE.

This means:

'' = ''

is effectively evaluated as:

NULL = NULL

The result is not TRUE. It is UNKNOWN.

Inside a WHERE clause, a condition that evaluates to UNKNOWN does not select the row. Oracle recommends using IS NULL and IS NOT NULL when testing for null values rather than equality operators. You can find the exact behaviour in Oracle’s documentation on null values and comparison conditions.

A minimal SuiteQL reproduction

You can demonstrate the behaviour without querying any NetSuite records by using DUAL.

This query returns no rows:

SELECT 1 AS result
FROM DUAL
WHERE '' = ''

This version returns one row:

SELECT 1 AS result
FROM DUAL
WHERE '' IS NULL

The same behaviour matters when the empty string arrives through a SuiteQL bind parameter.

The broken optional-filter pattern

Suppose a script accepts an optional customer code:

const customerCode = input.customerCode || '';

const results = query.runSuiteQL({
  query: `
    SELECT
      customer.id,
      customer.entityid
    FROM customer
    WHERE (
      ? = ''
      OR customer.entityid = ?
    )
  `,
  params: [customerCode, customerCode],
});

When customerCode is blank, both placeholders receive an empty string. Oracle treats those values as NULL, so the condition behaves like this:

WHERE (
    NULL = NULL
    OR customer.entityid = NULL
)

Both comparisons are unknown. The OR is therefore also unknown, and the query filters out every customer.

This can be especially confusing when the optional condition sits beside several working filters. The query may succeed whenever a real customer code is supplied, then mysteriously return no rows only when that field is left blank.

Fix 1: Test the parameter with IS NULL

The smallest correction is to test the blank parameter using IS NULL:

WHERE (
    ? IS NULL
    OR customer.entityid = ?
)

The JavaScript can continue passing an empty string:

const customerCode = input.customerCode || '';

const results = query.runSuiteQL({
  query: `
    SELECT
      customer.id,
      customer.entityid
    FROM customer
    WHERE (
      ? IS NULL
      OR customer.entityid = ?
    )
  `,
  params: [customerCode, customerCode],
});

When the value is blank, ? IS NULL evaluates to TRUE and the optional filter becomes a no-op. When a value is supplied, the equality condition performs the match.

Do not replace the empty string with a JavaScript null without testing the API boundary. NetSuite documents query.runSuiteQL() parameters as strings, numbers or booleans; unsupported parameter types cause SSS_INVALID_TYPE_ARG. See the official query.runSuiteQL(options) documentation for the current parameter rules.

Fix 2: Omit the condition entirely

For application-generated queries, I usually prefer constructing the optional clause only when a value exists.

const clauses: string[] = [];
const params: string[] = [];

if (input.customerCode) {
  clauses.push('customer.entityid = ?');
  params.push(input.customerCode);
}

const where = clauses.length
  ? `WHERE ${clauses.join(' AND ')}`
  : '';

const sql = `
  SELECT
    customer.id,
    customer.entityid
  FROM customer
  ${where}
`;

const results = query.runSuiteQL({
  query: sql,
  params,
});

This approach has a few advantages:

• The generated SQL contains only filters that are actually needed.
• Each placeholder and parameter can be added together in the same block.
• The database does not need to evaluate a collection of no-op conditions.
• The intent is easier to read when several optional fields are involved.

The important rule is to keep the SQL fragments and the parameter array in lockstep. Every ? in the final query must have a corresponding value in the same position in params.

Which fix should you use?

Use an IS NULL guard when the query shape needs to remain fixed, such as a saved query template with a stable parameter order.

Build the WHERE clause dynamically when your application already controls the SQL and the query has several optional filters. It generally produces clearer SQL and avoids repeating each optional value.

Whichever approach you choose, avoid this pattern in SuiteQL:

? = ''

If the parameter is intended to represent a missing value, test it with IS NULL or leave the condition out of the query.

Quick debugging checklist

When an optional SuiteQL filter unexpectedly returns no rows:

1. Look for comparisons against ''.
2. Remember that NULL = NULL is not TRUE.
3. Reproduce the condition against DUAL.
4. Replace empty-string equality checks with IS NULL.
5. Confirm that every bind placeholder matches the position of its parameter.
6. Consider generating only the filters that have real values.

It is a small Oracle behaviour, but once you know about it, a whole class of mysterious empty SuiteQL results becomes much easier to diagnose.

If you are building larger parameterised queries, the same ideas work well with the structure in SuiteQL: My Default Query Template.