SQL and test data
Why does NULL in an IN list not find missing values?
Tools to use
You want open rows and rows whose state is missing, but IN ('open', NULL) misses the latter. SQL NULL is not an ordinary value for equality. SQL IN Generator can separate ordinary membership from a missing-value condition, making the intended query explicit.
Separate three apparently empty values
'NULL' -- text consisting of four letters
'' -- empty string
NULL -- SQL null'NULL' is four letters, '' is an empty string and NULL represents a missing value. Import rules determine how blank source cells become stored values. Inspect types and import behavior before broadening a filter, or it may both miss intended rows and include unrelated ones.
This tool removes blank input lines; a blank line does not add an empty-string condition. Enter values without SQL quotes and let the generator quote them. Searching empty strings needs a separately designed condition at the destination.
Generate IS NULL separately
open
NULL("state" IN ('open') OR "state" IS NULL)Choose string values, column state, PostgreSQL dialect and separate NULL handling. open goes into IN and NULL becomes IS NULL. A column name is necessary to identify the missing value. With only NULL, output is just IS NULL, not an empty IN list. Preserve the grouping parentheses.
String handling instead searches the literal text 'NULL'. Which is correct depends on the data. Reject handling can stop ambiguous imports for review. Prefer a clear meaning over merely making the error disappear.
Unknown is different from false
An ordinary comparison involving NULL may be unknown rather than true or false. WHERE keeps true rows, not unknown ones. Listing NULL does not make it equal to another NULL; use IS NULL when missingness is the intended condition.
Be especially careful with NOT IN
NOT IN is not a simple rescue: a NULL in the list can prevent unmatched values from producing true. Negating unknown does not make it true. Do not blindly replace IN with NOT IN in generated output and assume missing values are handled.
State whether you want unmatched ordinary values, missing values too, or missing values excluded. Then test matching, nonmatching and NULL rows in an isolated database. SheepDev generates text without connecting to the database.
Check grouping when adding other conditions
active = TRUE AND ("state" IN ('open') OR "state" IS NULL)Removing parentheses can change how OR combines with another AND. Here active must be true for both open and NULL rows. Format the larger SQL to inspect grouping, then verify expected rows; formatting alone does not prove meaning.