NULLIF

SQL NULLIF is a function that is used to compare two expressions and return NULL if they are equal. It is a very useful function in SQL, especially when you are dealing with data that may contain empty or null values.

Syntax

The syntax for NULLIF is:

NULLIF(expression1, expression2)

The function compares expression1 with expression2. If the two expressions are equal, the function returns NULL. If they are not equal, the function returns expression1.

Example

Here’s an example:

SELECT NULLIF('hello', 'hello');

In this example, the two expressions are equal, so the function returns NULL.

Now let’s try a different example:

SELECT NULLIF('hello', 'world');

In this example, the two expressions are not equal, so the function returns ‘hello’.

The NULLIF function can be useful when you are dealing with data that may contain null values. For example, let’s say you have a table that contains a column for a customer’s email address. Some customers may not have an email address, so their email field will be null. If you want to select all customers who do not have an email address, you could use the NULLIF function like this:

SELECT *
FROM customers
WHERE NULLIF(email, '') IS NULL;

In this example, the NULLIF function compares the email column to an empty string. If the email column is equal to an empty string, the function returns NULL. If the email column is not equal to an empty string (meaning it contains an email address), the function returns the email address. The IS NULL condition then filters out all rows where the function returned a non-null value, leaving only the rows where the email field is null.

In summary, the SQL NULLIF function is a very useful tool for working with data that may contain null or empty values. It allows you to easily compare two expressions and return NULL if they are equal, which can help you write more efficient and accurate SQL queries.