Building Complex Filters with AND, OR, and NOT

"Show me orders from the Northeast where revenue is over $100, OR the customer is Gold tier - but exclude anything that's been cancelled." That's three conditions, two logical operators, and a negation. In a spreadsheet, you'd be nesting IF statements or wrestling with Advanced Filter dialogs. In SQL, you'd be writing a WHERE clause with parentheses and hoping you got the precedence right.

ExploreMyData has a visual Condition Builder that handles this. You add conditions, group them, toggle between AND and OR, and see the generated SQL in real time. No syntax to remember.

Starting simple: a single condition

Open the Filter operation from the toolbar. The Condition Builder starts with one empty condition. You pick three things: a column, an operator, and a value.

The operators change depending on the column type:

• Text columns: is, is not, starts with, ends with, contains, is Empty, is NOT Empty
• Numeric columns: =, !=, >, <, >=, <=
• Date columns: is, before, after, on or before, on or after

For example: column = "revenue", operator = ">", value = "100". That gives you WHERE revenue > 100. Simple enough.

Adding more conditions: AND vs OR

Click "Add condition" to add a second rule. By default, conditions are joined with AND - meaning all conditions must be true. There's a toggle between AND and OR at the top of the condition group.

AND means "all of these must be true." Revenue > 100 AND region is "Northeast" - both must match.

OR means "at least one of these must be true." Revenue > 100 OR tier is "Gold" - either one is enough.

The generated SQL updates live as you build. Two AND conditions:

WHERE revenue > 100 AND region = 'Northeast'

Switch to OR:

WHERE revenue > 100 OR region = 'Northeast'

Nesting groups for complex logic

Here's where it gets interesting. What if you need: "(revenue > 100 AND region = 'Northeast') OR (tier = 'Gold')"? That requires grouping. The first two conditions are AND'd together, and that entire group is OR'd with the third condition.

In the Condition Builder, click "Add group" to create a nested group of conditions. Each group has its own AND/OR toggle. You can nest groups inside groups for arbitrarily complex logic.

The generated SQL reflects the nesting with parentheses:

WHERE (revenue > 100 AND region = 'Northeast') OR (tier = 'Gold')

This is the same query that would take careful parenthesis placement if you wrote it by hand. The visual builder makes the grouping explicit.

Keep vs Remove: the NOT operation

The Filter operation has two modes: keep matching rows, or remove matching rows. Remove mode wraps your entire condition in a NOT, effectively generating WHERE NOT (...).

Going back to the original question: "exclude cancelled orders." You could add a condition status is not 'cancelled' using the "is not" operator. Or you could create a separate filter step in "remove" mode where the condition is status is 'cancelled'. Both produce the same result, but sometimes the "remove" framing reads more naturally - you're saying "take these out" instead of "keep everything except these."

Putting it all together

Let's build the full original query: "orders from the Northeast where revenue > $100, OR Gold tier customers, excluding cancelled orders."

Step 1: Add a Filter operation in "keep" mode.

1. Create a group with AND: region = "Northeast" AND revenue > 100
2. Set the top-level operator to OR
3. Add a condition: tier = "Gold"

This generates: WHERE (region = 'Northeast' AND revenue > 100) OR tier = 'Gold'

Step 2: Add a second Filter operation in "remove" mode.

1. Add a single condition: status = "cancelled"

This generates: WHERE NOT (status = 'cancelled')

Two pipeline steps, clear logic, and you can read exactly what each step does in the sidebar. If the business logic changes - say "Gold" becomes "Gold or Platinum" - you edit the first step and add the new condition. The pipeline rebuilds.

Using text operators effectively

The text-specific operators deserve a callout because they solve common filtering problems:

• starts with - filter emails by domain prefix, product codes by category prefix
• ends with - find file names by extension, URLs by path suffix
• contains - search for keywords in description fields, partial name matches
• is Empty / is NOT Empty - find rows with missing values without checking for specific NULLs or blank strings

These are more readable than writing LIKE '%keyword%' patterns by hand. And "is Empty" handles both NULL and empty string cases, which is a common source of bugs in hand-written SQL. If you're doing more targeted text work, the text extraction patterns post covers regex-based approaches.

When filters aren't enough

The Condition Builder covers the vast majority of filtering needs. But if you need something it doesn't support - like subqueries, BETWEEN with computed bounds, or regex matching - you can always drop into the SQL operation and write a custom WHERE clause. The visual builder and raw SQL work together in the same pipeline.

Build your filter →