Null Handling¶

In some cases, you may need to handle NULL values in your database. GlueSQL provides functions such as ifnull and nullif to handle these cases.

IFNULL - ifnull¶

The ifnull function checks if the first expression is NULL, and if it is, it returns the value of the second expression. If the first expression is not NULL, it returns the value of the first expression.

let actual = table("Foo")
    .select()
    .project("id")
    .project(col("name").ifnull(text("isnull")))  // If the "name" column is NULL, replace it with "isnull"
    .execute(glue);

In the above example, if the "name" column is NULL, "isnull" is returned. Otherwise, the value of the "name" column is returned.

You can also use ifnull with another column:

let actual = table("Foo")
    .select()
    .project("id")
    .project(col("name").ifnull(col("nickname")))  // If the "name" column is NULL, replace it with the value from the "nickname" column
    .execute(glue);

In this example, if the "name" column is NULL, the value from the "nickname" column is returned. If "name" is not NULL, the value of the "name" column is returned.

The ifnull function can also be used without a table:

let actual = values(vec![
    vec![query_builder::function::ifnull(text("HELLO"), text("WORLD"))],  // If "HELLO" is NULL (it's not), return "WORLD". Otherwise, return "HELLO".
    vec![query_builder::function::ifnull(null(), text("WORLD"))],  // If NULL is NULL (it is), return "WORLD".
])
.execute(glue);

In the first case, "HELLO" is returned because it's not NULL. In the second case, "WORLD" is returned because the first value is NULL.

NULLIF - nullif¶

The nullif function compares two expressions. If they are equal, it returns NULL; otherwise, it returns the first expression.

let actual = table("Foo")
    .select()
    .project("id")
    .project(col("name").nullif(text("hello")))
    .execute(glue);

You can also use nullif without a table:

let actual = values(vec![
    vec![query_builder::function::nullif(text("HELLO"), text("WORLD"))],
    vec![query_builder::function::nullif(text("WORLD"), text("WORLD"))],
])
.execute(glue);

In the first case, "HELLO" is returned because the two values are different. In the second case, NULL is returned because the two values are equal.