A Practical Guide to the ISNULL() Function in GBase 8s

ISNULL() is a core function in GBase 8s for handling NULL values. It works in two modes — single‑argument and double‑argument — with clean, predictable behavior.

Function Characteristics

Two‑Argument Form: ISNULL(a, b)

• Returns the first non‑NULL value from left to right.
• If a is not NULL, returns a; otherwise returns b.
• If both arguments are NULL, returns an empty value.

Single‑Argument Form: ISNULL(a)

• Returns a boolean: t or f.
• Returns t if a is NULL; otherwise returns f.
• Can be cast to VARCHAR and other character types.

Examples

Two‑Argument Examples

-- Expected: 12
SELECT ISNULL(12, NULL) FROM dual;

-- Expected: aa
SELECT ISNULL(NULL, 'aa') FROM dual;

-- Expected: (empty)
SELECT ISNULL(NULL, NULL) FROM dual;

-- Expected: abc (nested call)
SELECT ISNULL(ISNULL('abc', NULL), 'ef') FROM dual;

Single‑Argument Examples

-- Expected: f
SELECT ISNULL('aa') FROM dual;

-- Expected: t
SELECT ISNULL(NULL) FROM dual;

-- Note: 'null' is a string, not NULL — returns f
SELECT ISNULL('null') FROM dual;

Real‑World Scenario: Filtering NULLs in a CTE

The following query demonstrates how ISNULL() works inside Common Table Expressions (CTEs) in a gbase database.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    id   INT,
    col1 INT,
    col2 INT
);
INSERT INTO t1 VALUES(1, 1, 1);
INSERT INTO t1 VALUES(2, NULL, NULL);
INSERT INTO t1 VALUES(3, 4, 4);
INSERT INTO t1 VALUES(4, 0, 0);

WITH tmp1 AS (
    SELECT id, col1, ISNULL(col1), SUM(col2) AS total_col2
    FROM t1
    GROUP BY id, col1
),
tmp2 AS (
    SELECT id, col1, ISNULL(tmp1.total_col2) AS null_col2
    FROM tmp1
    WHERE id > 1 AND ISNULL(col1) = 't'
)
SELECT dt.id, dt.col1, dt.null_col2
FROM tmp2 dt;

Expected result:

id  col1   null_col2
--  ----   ---------
2   NULL   1

ISNULL(col1) = 't' accurately filters rows where col1 is NULL, while ISNULL(total_col2) returns t when SUM over an empty set yields NULL, aiding downstream logic.

Mastering both forms of ISNULL() — as a replacement for NULLs and as a NULL detector — keeps your SQL logic in GBASE's GBase 8s cleaner and more readable.