Checking for NULL values in SQL

ShabbyAbby

I'm using this query to get news articles by tags.

SELECT N.*
FROM News N
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
                   STRING_SPLIT('tag1,tag2,tag3', ',') s2
                   ON s1.value = s2.value
             );

I'm wondering if there is any possibility to return all news articles if no tags were defined in query. For example:

SELECT N.*
FROM News N
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 JOIN
                   STRING_SPLIT(NULL, ',') s2
                   ON s1.value = s2.value
             );
Larnu

If we assume that the value being passed to s2 is a parameter, as the above doesn't make a lot of sense otherwise, you could use an OR. I also suggest adding OPTION (RECOMPILE) as the query plan for the NULL and non-NULL queries will likely be different, thus this stops poor query caching:

SELECT N.* --Replace with Columns
FROM dbo.News N
WHERE EXISTS (SELECT 1
              FROM STRING_SPLIT(N.Tags, ',') s1 
                   JOIN STRING_SPLIT(@Param, ',') s2 ON s1.value = s2.value)
    OR @Param IS NULL
OPTION (RECOMPILE);

You can read more on why OPTION (RECOMPILE) is a good choice in Revisiting catch-all queries and An Updated Kitchen Sink Example.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to avoid checking for null values in method chaining?

How to avoid checking for null values in method chaining?

JavaScript - Checking if all values in an array are not Null - Array / Each /

SqlDeveloper - checking for NULL values without IS NULL clause - in a scripted query

Modify the contents of null columns in pandas by checking values in multiple colums

Updating multiple values SQL, checking on values in update statement

Laravel multiple where clause checking for null values through a variable

Checking for null/empty float values when using sscanf

LINQ to SQL - Expression checking for null could not be translated

Sql select with Null and Not Null values

Django: checking is_null in values() queryset

Checking db for null values

SQL query with null values

How to assign a value to sqlparameter NULL values without checking for null or 0?

SQL idiom for checking whether values in a column are uniform

Checking if all values are null inside an initialised object

checking an array of string objects for null values

Reduce the use of foreach in array when checking null values php

SQL GROUP BY with null values

Checking for null and empty values in javascript

Checking for null values in a user object

Problem checking SQL values with WHEN statement

How to write unit testing checking null input values using wicket

checking if point is in polygon giving null values

checking null values in a dataframe

Checking if all values for user_id IS NOT NULL

Cassandra UDF: getting error on checking null values

Checking if any values in object are not null

Checking if values in multiple fields are null relative to each other?