Empty values

Empty values#

This page focuses on handling empty values in SQL.

Replace in query#

It’s a very common case when empty values have business sense, but in query results you need to replace them. So you can write SELECT to replace NULL in query results, this section is focused on it.

COALESCE#

In PostgreSQL, there is the COALESCE function. Use the syntax COALESCE(<column_name> | <value>, ...). This function will return the first value that is not NULL. You can use columns or just values as arguments—values will be broadcast to the column size.

Check the description of the COALESCE function in the official PostgreSQL documentation.

The following cell shows applying COALENCE to the columns col1, col2 and col3 that contains many empty values.

--postgreSQL
SELECT
    *,
    COALESCE(col1, col2, col3) no_default,
    COALESCE(col1, col2, col3) IS NULL no_default_is_null,
    COALESCE(col1, col2, col3, 'default') with_default
FROM (
    VALUES
    (NULL, 'a', NULL),
    ('b', NULL, NULL),
    (NULL, NULL, 'c'),
    (NULL, NULL, NULL)

) AS temp(col1, col2, col3);

SELECT 4
col1 col2 col3 no_default no_default_is_null with_default
a a False a
b b False b
c c False c
True default

The same example in sqlite:

--sqlite
CREATE TABLE IF NOT EXISTS tab (
    col1 TEXT, col2 TEXT, col3 TEXT
);


INSERT INTO tab (col1, col2, col3) VALUES
(NULL, 'a', NULL),
('b', NULL, NULL),
(NULL, NULL, 'c'),
(NULL, NULL, NULL);


SELECT 
    *,
    COALESCE(col1, col2, col3) no_default,
    COALESCE(col1, col2, col3) IS NULL no_default_is_null,
    COALESCE(col1, col2, col3, 'default') with_default
FROM tab;
col1 col2 col3 no_default no_default_is_nullwith_default
a a 0a
b b 0b
c c 0c
1default

IFNULL#

IFNULL is an alternative function that can be used to replace empty values in query statements. It returns the first non-null value passed to its arguments and is supported by SQLite.

The following shows some cases for IFNULL function.

--ClickHouse
CREATE TABLE IF NOT EXISTS tab (
    var1 TEXT,
    var2 TEXT
) ENGINE=MergeTree ORDER BY var1;


INSERT INTO tab (var1, var2) VALUES
('a', 'b'),
(NULL, 'a'),
('b', NULL),
(NULL, NULL);


SELECT IFNULL(var1, var2) FROM tab;
IFNULL(var1, var2)
a
b

Note: The main difference from COALESCE is that IFNULL can operate with only two operands.

docker exec empty_value_sqlite sqlite3 -box -cmd "SELECT IFNULL(10, 20, 'hello');"

Error: in prepare, wrong number of arguments to function IFNULL()
  SELECT IFNULL(10, 20, 'hello');
         ^--- error here

Change base#

Sometimes empty values in database appear as errors or business sense of data table can be selected. So you can face with case when you need to replace all values in the database, not only in the results of the specific query.

For such purpose you can use syntax:

UPDATE <table_name>
SET <column name> = <replace value>
WHERE <column name> IS NULL;

The following cell creates a table with some NULL values, then replaces them directly in the table and shows the results.

--postgreSQL
DROP TABLE IF EXISTS change_null_table;
CREATE TABLE change_null_table(col1 TEXT, col2 TEXT, col3 TEXT);

INSERT INTO change_null_table(col1, col2, col3)
VALUES
    ('val1', NULL, 'val2'),
    (NULL, 'val2', NULL);


SELECT * FROM change_null_table;

UPDATE change_null_table
SET col1 = 'replaced'
WHERE col1 IS NULL;


SELECT * FROM change_null_table;

NOTICE: table "change_null_table" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 2
SELECT 2
col1 col2 col3
val1 val2
val2 
UPDATE 1
SELECT 2
col1 col2 col3
val1 val2
replacedval2