NULLIF function behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NULLIF function do in SQL?

The NULLIF function compares two expressions. If they are equal, it returns NULL. Otherwise, it returns the first expression.

beginner

What is the result of NULLIF(5, 5)?

The result is NULL because both values are equal.

beginner

What happens if the two arguments of NULLIF are not equal?

If the two arguments are not equal, NULLIF returns the first argument as is.

intermediate

How can NULLIF help avoid division by zero errors?

You can use NULLIF to replace zero with NULL in the divisor. For example, value / NULLIF(divisor, 0) returns NULL instead of an error if divisor is zero.

beginner

Is NULLIF a standard SQL function or specific to PostgreSQL?

NULLIF is part of the SQL standard and supported by many databases including PostgreSQL.

What does NULLIF('apple', 'apple') return?

A NULL

B 'apple'

C Error

D '' (empty string)

NULLIF returns NULL when both arguments are equal.

What is the output of NULLIF(10, 5)?

A NULL

B 10

C Error

D 5

Since 10 and 5 are not equal, NULLIF returns the first argument, 10.

How can NULLIF help prevent division by zero?

A By returning NULL when divisor is zero

B By throwing an error

C By returning zero instead of NULL

D By converting numbers to strings

Using NULLIF(divisor, 0) returns NULL if divisor is zero, avoiding division by zero error.

If NULLIF returns NULL, what does that mean?

A The first input was NULL

B The two inputs were different

C There was a syntax error

D The two inputs were equal

NULL means the two inputs compared by NULLIF were equal.

Is NULLIF specific to PostgreSQL?

A Yes, only PostgreSQL supports it

B Only MySQL supports it

C No, it is part of the SQL standard

D It is deprecated

NULLIF is a standard SQL function supported by many databases including PostgreSQL.

Explain how the NULLIF function works and give a simple example.

Think about what happens when the two inputs are the same or different.

Describe a practical use case where NULLIF can help avoid errors in SQL queries.

Consider what happens when you divide by zero in SQL.