SQL Server NULL values are excluded from results

v09wglhw 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(74)

One of my code functionality requires user to enter dynamic "WHERE" clause for SQL query.
then this where clause is appended to the sql and executed on MS Sql server using Spring Boot - JDBC Template. e.g. SELECT * From Customer_Vehicles where Country in ( 'USA', 'Canada'); This Code works well if there is non Null value but it skips rows with NULL values in country column.

As per MS Sql documentation it skips NULL values as ANSI_NULLS is ON in jdbc connection (default property ).. So I tried to set the property using command "SET ANSI_NULLS OFF" using jdbc template execute method.

jdbctemplate.execute ("SET ANSI_NULLS OFF")
jdbctemplate.query("SELECT * From Customer_Vehicles where Country in ( 'USA', 'Canada')");

This solution works well for String comparison but it doesn't apply to numeric value such as mileage < 25000 e.g. SELECT * From Customer_Vehicles where Country in ( 'USA', 'Canada') and mileage < 25000 doesn't return a row if mileage is NULL.
Same is explained on MS documentation : https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql?view=sql-server-ver16

Is there a way to include NULL values in the comparison (irrespective of datatype) ?

Note : users can use ISNULL function , but users are not willing to modify their SET of WHERE clauses used in legacy app.

sql-server

来源：https://stackoverflow.com/questions/77377999/null-values-are-excluded-from-results

1条答案

按热度按时间

ctehm74n1#

Instead of messing around with ANSI_NULLS (which should never be done due to compatibility issues with newer features such as indexed views and MERGE ), instead just add a condition to your query to include nulls.

SELECT cv.*
FROM Customer_Vehicles cv
WHERE (cv.Country in ('USA', 'Canada') OR cv.Country IS NULL)
  AND (cv.mileage < 25000 OR cv.mileage IS NULL);

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server NULL values are excluded from results

1条答案

相关问题

热门标签

最新问答