SQL Is Null

What is the IS NULL Operator in SQL?

The SQL IS NULL is an operator that tests for NULL (or empty) values in a column. It is a common requirement to search for empty spaces in a database, as these values indicate missing or unknown data.

A NULL value indicates that the data is missing or unknown, while a value of 0 indicates that the data is explicitly known and set to zero. If you are analyzing a stock table, the quantity of a product would be NULL when you don’t have the total units for that product. If it’s zero, it means it’s sold out.

Keep in mind that NULL values are treated differently than other values in SQL. When comparing two NULL values, they are considered unequal since NULL represents the absence of a value. This can sometimes lead to unexpected results, so it’s fundamental to know how your database management system handles them.

For example, the following SQL statement selects all rows where column1 is NULL:

SELECT *   
FROM table   
WHERE column1 IS NULL;

This information can be used to fill in missing data or to identify areas where data is lacking and needs to be collected.

In addition to the IS NULL operator, SQL also provides the IS NOT NULL operator, which is used to test for non-NULL values in a column.

SELECT *   
FROM table   
WHERE column1 IS NOT NULL;

As a result, you would see the rows that are not empty or do not have NULL values.

Using IS NULL within a CoginitiScript block

Within Coginiti, you can use a CoginitiScript block to dynamically check for the presence of one or more null columns in a given table.

#+src sql FindNulls(table, columns)
#+meta { :doc "Find records where specified columns are NULL in a given table." }
#+begin
  SELECT * FROM {{ table }}
  WHERE 
  #+for col : columns separator " OR " do
    {{ col }} IS NULL
  #+end
#+end

Usage

SELECT * FROM {{ FindNulls("employees", ["email", "phone_number"]) }};
SELECT * FROM {{ FindNulls("orders", ["shipping_address", "billing_address"]) }};

In this CoginitiScript block, the FindNulls block takes two parameters:

1. table: The name of the table you want to query.
2. columns: A list of column names you want to check for NULL values.

The CoginitiScript for loop is used to iterate through the list of columns, and the separator " OR " ensures that the conditions are combined using the OR operator. This makes the function flexible enough to handle one or more columns in the specified table.

Download your free trial of Coginiti Pro to try this for yourself!