Just spent half a day trying to track down some missing asset data in our logs. I was convinced our custom log source was broken, but it turns out the records were there—just with empty fields. I completely forgot that in AQL, you can't use `=` or `!=` to check for NULLs.
For anyone else hitting this, here's the syntax that finally worked. You need to use `IS NULL` or `IS NOT NULL`.
```sql
SELECT * FROM events
WHERE some_custom_field IS NULL
AND starttime > '2024-01-01 00:00'
LAST 1 DAYS
```
Or, if you're looking for populated fields:
```sql
SELECT * FROM events
WHERE some_custom_field IS NOT NULL
```
The classic `WHERE field = NULL` will return zero results every time. It's a simple thing, but when you're deep in a migration project and validating data flow from integrations, this little detail can save you a massive headache.
I ended up using this to verify that our Zapier webhooks were populating all the expected fields correctly. Super handy.
hth
Oh man, I've done the exact same thing! It feels like such a logic trap when you're stuck on it. Your example with Zapier fields is perfect, it's exactly the kind of integration where nulls sneak in and wreck your reports later.
A similar quirk got me recently: if you're trying to filter for empty strings in a query, you still can't use `= ''`. You have to use `IS NOT DISTINCT FROM ''`. Different syntax, same kind of time-sink frustration when you're under pressure.
Glad you got it sorted. That "aha" moment after hours of banging your head is weirdly satisfying.
This is a fundamental SQL behavior that extends far beyond AQL - it's rooted in three-valued logic where NULL represents an unknown value, so equality comparisons always return UNKNOWN rather than TRUE or FALSE. What's particularly tricky is that some database systems implement vendor-specific extensions that can catch people off guard.
For example, MySQL permits `SET ANSI_NULLS = OFF` to enable `= NULL` comparisons, while SQL Server has different behavior depending on the ANSI_NULLS setting. If you're working across multiple database systems or migrating queries, this inconsistency can create subtle bugs.
In the context of data validation for webhook integrations like your Zapier example, I'd recommend creating a validation script that explicitly checks for both NULL and empty string values separately. The semantic difference between "field not present" (NULL) and "field present but empty" (empty string) often matters for downstream processing, and treating them identically can mask integration issues.