WHERE — Marketing Cloud SQL reference

WHERE is where you tell the engine which rows to keep. In MC SQL it works like T-SQL — comparison operators, AND / OR / NOT, IN, BETWEEN, LIKE, IS NULL. The bugs aren't in the syntax. They're in three places: NULL handling (three-valued logic), NOT IN against large sets (performance), and operator precedence when you mix AND with OR (silent semantics).

Official syntax

-- Comparison operators: = != <> < > <= >=
SELECT SubscriberKey, EmailAddress
FROM master_subscribers
WHERE Status = 'Active'
  AND LastPurchase >= DATEADD(day, -90, GETDATE());

-- Logical: AND, OR, NOT (use parentheses when mixing)
SELECT SubscriberKey
FROM master_subscribers
WHERE Status = 'Active'
  AND (LoyaltyTier = 'gold' OR LoyaltyTier = 'silver');

-- IN / NOT IN against a literal list
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier IN ('gold', 'silver', 'platinum');

-- BETWEEN (inclusive on both ends)
SELECT SubscriberKey
FROM master_subscribers
WHERE LastPurchase BETWEEN '2026-01-01' AND '2026-03-31';

-- IS NULL / IS NOT NULL — never use = NULL
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier IS NULL;

-- LIKE for pattern matching (see the dedicated LIKE page)
SELECT SubscriberKey
FROM master_subscribers
WHERE EmailAddress LIKE '%@enterprise.com';

WHERE runs after the FROM / JOIN resolves rows but before GROUP BY and HAVING. Filter rows in WHERE; filter aggregates in HAVING.

Reference:

• Salesforce Help — Supported T-SQL syntax ↗
• Salesforce Help — SQL queries for Data Views (filtering examples) ↗

What survives in production

IS NULL, never = NULL

WHERE LoyaltyTier = NULL returns zero rows — every time, even if the column is NULL on millions of rows — because in three-valued logic NULL = NULL is unknown, not true. The same for != NULL and <> NULL.

-- BUG — silently misses every NULL row
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier = NULL;

-- CORRECT
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier IS NULL;

-- COMMON COMBINATION — "give me rows where LoyaltyTier is something
-- other than 'gold', INCLUDING rows where it's NULL"
SELECT SubscriberKey
FROM master_subscribers
WHERE LoyaltyTier <> 'gold' OR LoyaltyTier IS NULL;

The third example is the one most people forget. Without the explicit OR ... IS NULL, the NULL rows drop out of the result silently. See gotchas — #5.

Parenthesize when mixing AND and OR

AND binds tighter than OR. WHERE A OR B AND C parses as WHERE A OR (B AND C), which is almost never what the next dev reads it as. Always parenthesize.

-- AT RISK — depending on what the writer meant, this is wrong
WHERE Status = 'Active' OR Status = 'Pending' AND LoyaltyTier = 'gold'

-- The above parses as:
WHERE Status = 'Active' OR (Status = 'Pending' AND LoyaltyTier = 'gold')

-- If the intent was "gold subscribers who are active or pending",
-- write it explicitly:
WHERE (Status = 'Active' OR Status = 'Pending')
  AND LoyaltyTier = 'gold'

The parentheses don't change parsing when the precedence already matches your intent — but they do tell the next reader you thought about it. Always include them.

NOT IN against a subquery is a performance trap

NOT IN (SELECT key FROM big_table) reads cleanly, runs slowly, and gets dramatically worse as the subquery grows. The MC SQL optimizer doesn't reliably rewrite NOT IN into an anti-join.

-- AT RISK — NOT IN against a subquery, gets slower with scale
SELECT SubscriberKey
FROM master_subscribers
WHERE SubscriberKey NOT IN (
  SELECT SubscriberKey FROM suppression_list
);

-- DURABLE — anti-join via LEFT JOIN ... IS NULL
SELECT s.SubscriberKey
FROM master_subscribers s
LEFT JOIN suppression_list x
  ON s.SubscriberKey = x.SubscriberKey
WHERE x.SubscriberKey IS NULL;

NOT IN against a small literal list (NOT IN ('inactive', 'bounced', 'unsubscribed')) is fine. NOT IN (SELECT ...) against a Data Extension is the trap. See JOIN for the full anti-join idiom.

Push WHERE as close to the source as possible

When you stage in multiple Activities, filter in the first Activity that touches the source — not the last one. Each row that gets dropped early is a row that doesn't have to flow through joins, transformations, and the destination DE write.

-- AT RISK — pulls every row from master_subscribers across the join,
-- then filters at the end. Joins do unnecessary work.
INSERT INTO de_stg_target
SELECT s.SubscriberKey, p.LastPurchase
FROM master_subscribers s
INNER JOIN purchases p
  ON s.SubscriberKey = p.SubscriberKey
WHERE s.Status = 'Active';

-- DURABLE — stage active subscribers first, then join. The join
-- sees only the rows that survived the WHERE.
INSERT INTO de_stg_active_subs
SELECT SubscriberKey, EmailAddress
FROM master_subscribers
WHERE Status = 'Active';

INSERT INTO de_stg_target
SELECT s.SubscriberKey, p.LastPurchase
FROM de_stg_active_subs s
INNER JOIN purchases p
  ON s.SubscriberKey = p.SubscriberKey;

The MC SQL optimizer often pushes filters down for you, but "often" isn't "always" — and on a 30-min budget you don't get to find out which queries it didn't optimize.

Date filters: prefer day-count windows over month math

DATEADD(month, -3, GETDATE()) returns inconsistent results around month boundaries (Feb 28/29, Mar 31 → Feb 28, etc.). For WHERE filters that drive Send eligibility, use day counts.

-- AT RISK — month math, edge cases around year boundaries
WHERE LastPurchase >= DATEADD(month, -3, GETDATE())

-- STABLE — explicit day count, behaves the same every run
WHERE LastPurchase >= DATEADD(day, -90, GETDATE())  -- "last ~3 months"

If the business rule is "last 3 months", translate it to a day count once at design time and document it in a comment. See gotchas — #8.

Quick decision

Use WHERE when:

• Filtering rows from FROM / JOIN based on column values.
• Removing rows you don't need before they flow into joins, aggregates, or the destination DE.

Use HAVING instead of WHERE when:

• Filtering on the result of an aggregate (HAVING COUNT(*) > 1, HAVING SUM(x) >= 100). WHERE runs before grouping; HAVING runs after.

Reach for LEFT JOIN ... IS NULL instead of NOT IN when:

• The NOT IN argument is a subquery, not a small literal list.
• See JOIN for the anti-join pattern.

Reach for IS NULL instead of = NULL when:

• Always. = NULL is always wrong, even when it looks like it works (it returns zero rows, which can look "correct" until the day a NULL row should have been included).

Related

• Basics — the supported T-SQL subset
• SELECT — what WHERE filters
• FROM — sources WHERE runs against
• JOIN — anti-join idiom for the NOT IN performance pitfall
• MC SQL gotchas — #5 (NULL three-valued + length truncation), #8 (date arithmetic)

More reference pages incoming: LIKE · CASE · INSERT INTO · String / Date / Numeric / Conversion / Aggregate / Null Functions · Style Guide.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.