Alternative ways to include NULL values in the results while using SQL negation...
source link: https://blog.kiprosh.com/alternative-ways-to-include-null-values-in-the-results-while-using-sql-negation-commands-with-rails/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Alternative ways to include NULL values in the results while using SQL negation commands(!= or NOT IN) with Rails
In the previous article, we went through How NOT IN works with NULL values. We also learned how we can overcome this restriction. In this article, we will look at alternative ways to handle NULL
values with SQL functions.
So basically when we use !=
or NOT IN
in query, it ignores the records with NULL
values for fields.
User.where("state != ?", 'active')
SELECT * FROM users WHERE state != 'active'
User.where("state NOT IN (?)", ['active'])
SELECT * FROM users WHERE state NOT IN ('active')
The above queries will consider records with state
having a NOT NULL
value, but state
having a NULL
value will not be considered. To consider state
with the NULL
value, we have to explicitly add the OR
clause.
User.where("state != ? OR state IS NULL", 'active')
SELECT * FROM users WHERE state != 'active' OR state IS NULL
User.where("state NOT IN (?) OR state IS NULL", ['active'])
SELECT * FROM users WHERE state NOT IN ('active') OR state IS NULL
Lets take a look at the alternatives for handling NULL
values
-
With COALESCE function - MySQL/SQL Server/SQLite/PostgreSQL/Oracle
The
COALESCE
function returns the first non-NULL
expression in the specified list. If all the arguments areNULL
, then it will returnNULL
.COALESCE(expression, replacement1...n)
Here, considering
''
(blank string) forNULL
value while performing query to return records withNULL
value.User.where("COALESCE(state, '') != 'active'")
-
With IFNULL function in MySQL/SQLite
The MySQL
IFNULL
function returns an alternative value if an expression isNULL
.IFNULL(expression, replacement)
User.where("IFNULL(state, '') != 'active'")
-
With ISNULL function in SQL Server
The SQL Server
ISNULL
function returns an alternative value if an expression isNULL
.ISNULL(expression, replacement)
User.where("ISNULL(state, '') != 'active'")
-
With NVL function in Oracle
The Oracle
NVL
function returns an alternative value if an expression isNULL
.NVL(expression, replacement)
User.where("NVL(state, '') != 'active'")
-
With IS DISTINCT FROM statement in PostgreSQL
In
PostgreSQL
, we can useIS DISTINCT FROM
to work around the problems ofNULL
, which treatNULL
as a comparable value.attribute IS DISTINCT FROM value
User.where("state IS DISTINCT FROM 'active'")
I hope you enjoyed this article and learned other ways of dealing with NULL
values. Feel free to share your feedback or suggestion. We would ❤️ to hear from you. Thank you.
References
Sampat Badhe
I am a Rubyist and Node.js developer at Kiprosh.com. I enjoy building innovative applications and currently in love with TDD practice. I am practicing Agile and love to pair program.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK