Issue
I was writing a SQL query in Appsmith to retrieve data based on two conditions but it was returning NULLs. I tried using a UNION between the two conditions but it still didn't work. After turning off prepared statements, I realized that only the first condition was working. When I evaluated the query, I found that the second condition was not filtering out any NULLs. I resolved this issue by using a JOIN instead of a UNION.
Resolution
The first part of your query works as expected. The second part, however, doesn’t apply the same filter, so when you combine them with UNION you pull in extra rows from the unfiltered second query.
If you want only the records that satisfy both conditions, either:
- combine the conditions in a single
WHERE ... AND ..., - use
INTERSECT(if supported), or - join the two filtered result sets on a common key with an
INNER JOIN.
The presence or absence of IS NOT NULL isn’t the main factor here - the mismatch in filters is.