Скачать или смотреть How to Handle Nullable Columns in SQL: ISNULL and WHERE Clause Made Easy

Discover how to effectively use `ISNULL` to retrieve values from multiple columns in SQL, while filtering out rows with NULL results.
---
This video is based on the question https://stackoverflow.com/q/70668985/ asked by the user 'Obieworld' ( https://stackoverflow.com/u/17903876/ ) and on the answer https://stackoverflow.com/a/70669073/ provided by the user 'Eric Ruder' ( https://stackoverflow.com/u/1296789/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: SQL QUERY: If NULL then take another column but if NULL again ignore it

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Handle Nullable Columns in SQL: ISNULL and WHERE Clause Made Easy

Working with SQL databases can sometimes lead to tricky situations when you encounter NULL values in your columns. A common requirement is to select a value from one column unless it's NULL, in which case you may want to refer to another column. However, if both columns are NULL, you would want to avoid returning any results.

In this guide, we will dive into a simple yet effective solution that handles such scenarios using the ISNULL function along with a WHERE clause.

The Problem Statement

Imagine you have a table with two date columns: Modified and Created. You want to retrieve the most recently updated date from these columns. The ideal conditions are:

Use Modified if it's not NULL.

If Modified is NULL, use Created.

If both columns are NULL, the result should also be NULL, and that row should be ignored.

Example Scenario

Here's a simplified version of what you're trying to achieve:

[[See Video to Reveal this Text or Code Snippet]]

In the above example, if both column1 and column2 are NULL, you would still receive a NULL result.

The SQL Solution

Using ISNULL

The SQL ISNULL function is designed for these situations. It checks the first column for a value, and if it is NULL, it takes the value from the second column. Here is how you can implement this solution:

[[See Video to Reveal this Text or Code Snippet]]

What Happens Here?

If Modified is NULL, the query will check the Created column.

If both Modified and Created are NULL, the result for LastTouched will be NULL.

Filtering Out NULL Results

To ensure that you don't retrieve rows where both Modified and Created are NULL, you can add a WHERE clause. Here’s the modified query:

[[See Video to Reveal this Text or Code Snippet]]

Why Use a WHERE Clause?

Using the WHERE clause ensures that any rows where both columns are NULL will not be included in your results.

Important Syntax Note

It’s essential to be aware of syntax rules. You may encounter errors if statements are not correctly structured. For instance, the correct format when selecting additional columns alongside ISNULL should look like this:

[[See Video to Reveal this Text or Code Snippet]]

Ensure there is a comma after the asterisk to avoid syntax errors.

Alternate Function: COALESCE

While we've focused on ISNULL for this example, COALESCE is another function worth considering if you're dealing with more than two columns or values. It allows you to specify multiple columns to check in order, returning the first non-NULL value found.

Conclusion

By utilizing the ISNULL function in conjunction with the WHERE clause, you can efficiently manage NULL values in your SQL queries. Remember these steps:

Use ISNULL to choose between columns.

Include a WHERE clause to filter out unwanted NULL results.

Ensure your SQL syntax is correct to avoid errors.

With this approach, you can confidently retrieve valid data from your SQL tables while effectively handling null values. Happy querying!