SQL WHERE Builder

About SQL WHERE Builder

This SQL WHERE builder constructs valid SQL WHERE clauses from a simple line-by-line format. Each condition uses pipe-delimited syntax (field|operator|value) and the tool handles proper quoting, escaping, and operator formatting automatically.

It is useful for rapid query prototyping, generating admin panel filters, creating documentation examples, debugging complex conditions, teaching SQL syntax, and building dynamic query builders for applications.

Input Format Structure

Each condition occupies one line with pipe-separated fields:

Format: field|operator|value

Examples:
  id|=|123
  status|IN|'active','pending'
  name|LIKE|'%Smith%'
  deleted_at|IS NULL|
  price|>=|10.00
  category|NOT IN|'archived','draft'

Output (with AND connector):
  WHERE
    id = 123
    AND status IN ('active','pending')
    AND name LIKE '%Smith%'
    AND deleted_at IS NULL
    AND price >= 10.00
    AND category NOT IN ('archived','draft')

Supported SQL Operators

Value Formatting Rules

Automatic Value Handling:

1. Numeric values (integers, decimals):
   Input:  price|>=|100
   Output: price >= 100

2. String values (auto-quoted):
   Input:  name|=|John
   Output: name = 'John'

3. Already quoted values (preserved):
   Input:  status|IN|'active','pending'
   Output: status IN ('active','pending')

4. IN/NOT IN lists (wrapped in parentheses):
   Input:  role|IN|'admin','user','guest'
   Output: role IN ('admin','user','guest')

5. IS NULL / IS NOT NULL (no value needed):
   Input:  deleted_at|IS NULL|
   Output: deleted_at IS NULL

6. Single quote escaping:
   Input:  name|=|O'Brien
   Output: name = 'O''Brien'

Complete Query Examples

Example 1: User Filter Query
Input conditions (AND mode):
  status|=|active
  role|IN|'admin','moderator'
  created_at|>=|2024-01-01

Generated WHERE:
  WHERE
    status = 'active'
    AND role IN ('admin','moderator')
    AND created_at >= '2024-01-01'

Full query:
  SELECT id, username, email, role
  FROM users
  WHERE
    status = 'active'
    AND role IN ('admin','moderator')
    AND created_at >= '2024-01-01'
  ORDER BY created_at DESC;

Example 2: Product Search
Input conditions (AND mode):
  price|BETWEEN|10 AND 100
  category|NOT IN|'discontinued'
  stock|>|0
  name|LIKE|'%widget%'

Generated WHERE:
  WHERE
    price BETWEEN 10 AND 100
    AND category NOT IN ('discontinued')
    AND stock > 0
    AND name LIKE '%widget%'

Example 3: Soft Delete Pattern
Input conditions (AND mode):
  deleted_at|IS NULL|
  user_id|=|42

Generated WHERE:
  WHERE
    deleted_at IS NULL
    AND user_id = 42

SQL Operator Precedence

When combining AND and OR conditions, operator precedence affects evaluation:

Precedence Order (highest to lowest):
1. NOT
2. AND
3. OR

Example without parentheses:
  WHERE status = 'active' OR status = 'pending' AND role = 'admin'
  -- Evaluated as:
  WHERE status = 'active' OR (status = 'pending' AND role = 'admin')

Example with explicit grouping:
  WHERE (status = 'active' OR status = 'pending') AND role = 'admin'

Best Practice:
Always use parentheses when mixing AND and OR
to make the intended logic explicit and avoid
bugs from misunderstood precedence.

Common WHERE Clause Patterns

SQL Dialect Differences

Different SQL databases have variations in WHERE clause syntax:

String Concatenation:
  Standard SQL: 'Hello' || ' ' || 'World'
  SQL Server:    'Hello' + ' ' + 'World'
  MySQL:         CONCAT('Hello', ' ', 'World')

Case-Insensitive LIKE:
  PostgreSQL:    name ILIKE '%john%'
  MySQL:         name LIKE '%john%' (default case-insensitive)
  SQL Server:    name LIKE '%john%' COLLATE Latin1_General_CI_AS

Boolean Values:
  PostgreSQL:    is_active = TRUE
  MySQL:         is_active = 1
  Oracle:        is_active = 1

LIMIT/OFFSET:
  PostgreSQL:    LIMIT 10 OFFSET 20
  SQL Server:    OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
  Oracle:        OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
  MySQL:         LIMIT 20, 10

NULL Handling:
  All databases: IS NULL, IS NOT NULL (standard)
  Oracle:        NVL(column, default)
  SQL Server:    ISNULL(column, default)
  PostgreSQL:    COALESCE(column, default)

Performance Considerations

• Index usage: WHERE clauses on indexed columns are faster. Avoid functions on indexed columns (e.g., WHERE YEAR(created_at) = 2024 prevents index use).
• LIKE with leading wildcard: WHERE name LIKE '%Smith' cannot use indexes. WHERE name LIKE 'Smith%' can use indexes.
• OR vs IN: WHERE x = 1 OR x = 2 OR x = 3 is often optimized same as WHERE x IN (1,2,3).
• NOT IN with NULLs: NOT IN lists containing NULL can return unexpected results. Use NOT EXISTS for subqueries.
• Type matching: Ensure comparison types match (don't compare string column to unquoted number).

Security Best Practices

• Use parameterized queries: Never concatenate user input into SQL strings in production code.
• Validate input types: Ensure numeric inputs are actually numeric before using in queries.
• Limit string length: Prevent buffer overflow attacks by limiting input string lengths.
• Escape special characters: This tool escapes single quotes, but production code should use proper parameter binding.
• Use stored procedures: For complex queries, encapsulate logic in stored procedures with validated parameters.