NULLs in Marketing Data: What LLM-Written SQL Must Handle

In SQL, NULL means unknown, not zero and not empty, and a filter keeps a row only when its condition is true. So channel <> 'Direct' silently drops every order whose channel is NULL, NOT IN returns nothing if its list contains a NULL, and AVG skips NULL rows. No study cited here measures NULL mistakes in LLM-written queries on Shopify or GA4 data.

What do the studies and documentation show?

Two peer-reviewed papers and one arXiv paper bear on NULL. One measures people, one measures models, one explains the rule.

• Toussaint et al. (peer-reviewed, PVLDB 2022) ran an online survey for four months that "attracted 175 participants, with three quarters of them being database practitioners and one quarter academics". For a query with a NOT IN subquery over data with NULLs, 15.4% agreed with the result SQL returned and 84.5% preferred a proper superset of it. A large majority, the authors write, "think that it filters out too many results", while users were generally satisfied with NOT EXISTS. Around 40% wanted a disequality between NULL and a constant to be false rather than unknown. The authors conclude that NULLs are "ubiquitous and relevant in real-life scenarios, but SQL's features designed to deal with them cause multiple problems".
• Shen et al. (peer-reviewed, FSE 2026) analysed 4602 wrong queries from four text-to-SQL systems, run with GPT-3.5 and GPT-4o on the BIRD and Spider development sets. It names "Ascending Sort with NULL" as a model error: "When sorting a column containing NULL values, the latter are typically regarded as the smallest value, which may lead to an unexpected NULL output when a concrete number is expected."
• Libkin and Peterfreund (arXiv paper, revised July 2024) describe how SQL evaluates conditions: it "only retains tuples for which conditions in WHERE evaluate to true", and three-valued logic is "much criticized for unintuitive behavior of queries and being a source of programmer mistakes".

The survey measures people, not models, and the error study covers 2024-era systems on public benchmarks. No study cited here measures how often LLM-written queries mishandle NULL on Shopify or GA4 data.

What does NULL do to a filter, a NOT IN and an average?

PostgreSQL says ordinary comparison operators yield null, "not true or false, when either input is null", and BigQuery says "all operators return NULL when one of the operands is NULL". A WHERE clause keeps only true. So a NULL channel passes neither = 'Direct' nor <> 'Direct', and a report of everything that is not Direct quietly leaves it out.

NOT IN is harsher. PostgreSQL says that if the list contains a NULL, "the result of the NOT IN construct will be null, not true". A new-customer query written as customers NOT IN past buyers returns no one if a single past-buyer ID is NULL.

AVG and SUM skip NULL. PostgreSQL's aggregate reference defines AVG this way: "Computes the average (arithmetic mean) of all the non-null input values." It also warns that "sum of no rows returns null, not zero as one might expect", so a day with no orders can turn a revenue total into NULL.

How do I check for NULL damage on my own data?

First see how missing is stored, because NULL, an empty string and a placeholder such as (not set) behave differently in a filter. Then run the four-way count and the list check. Swap in your own tables and columns.

-- 1. How "missing" is stored
SELECT channel, COUNT(*) AS orders
FROM orders_with_channel
GROUP BY channel
ORDER BY orders DESC;

-- 2. The four-way count: the first three must add up to the last
SELECT SUM(CASE WHEN channel = 'Direct'  THEN 1 ELSE 0 END) AS is_direct,
       SUM(CASE WHEN channel <> 'Direct' THEN 1 ELSE 0 END) AS not_direct,
       SUM(CASE WHEN channel IS NULL     THEN 1 ELSE 0 END) AS missing,
       COUNT(*)                                             AS all_rows
FROM orders_with_channel;

-- 3. NULLs in the list you use with NOT IN, and the safe rewrite
SELECT COUNT(*) AS nulls_in_list FROM past_buyers WHERE customer_id IS NULL;

SELECT COUNT(*) AS new_customers
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM past_buyers p WHERE p.customer_id = c.customer_id);

Read the output like this:

1. Pass. is_direct + not_direct + missing equals all_rows, and you know what missing should mean.
2. The first two add up to less than all_rows. The gap is the NULL rows, which any = or <> filter loses. Decide on purpose: give them their own bucket, exclude them and say so, or fix the tagging at the source.
3. nulls_in_list is above zero. NOT IN returns no rows against that list. Use NOT EXISTS.
4. COUNT(*) minus COUNT(column) is above zero. AVG and SUM skipped that many rows. If a missing value should count as zero, say so with COALESCE.

An LLM can help by rewriting NOT IN as NOT EXISTS and explaining three-valued logic in plain words. The four-way count is the test of its draft.

Where does a causal read fit?

Later, once the counts add up, a causal attribution read like Causality Engine's works on GA4's channel groups, Unassigned included, and its next step for Unassigned is to fix UTM tagging first.

Sources, 30 September 2026: Troubles with Nulls, Views from the Users (Toussaint, Guagliardo, Libkin and Sequeda, PVLDB, 2022); ICL-based text-to-SQL error study (Shen et al., FSE 2026); Handling SQL Nulls with Two-Valued Logic (Libkin and Peterfreund, arXiv, 2024); PostgreSQL, comparison functions, subquery expressions and aggregate functions (PostgreSQL Global Development Group, 2026); BigQuery operators (Google Cloud, 2026).

Related answers

• How a join drops or repeats orders
• Why an average of averages is not the total
• GA4 unassigned traffic
• UTM parameters