SQL Uses Three LIKE Conditionsfor United Country Search

by

Summary

A developer needed to find all countries whose names contain the word “united” and wrote three separate LIKE conditions. They asked whether a more compact alternative exists, such as using IN with wildcards. The core issue is that SQL’s IN operator does not support wildcard pattern matching — it only matches literal values. The three LIKE clauses are the correct and minimal approach for this requirement.

Root Cause

  • IN matches literal values only. The expression name IN ('united%', '%united', '%united%') treats each string as an exact value, not a pattern.
  • The developer’s three LIKE clauses each cover a different placement of “united” in the name:
    • 'united%' — name starts with “united”
    • '%united' — name ends with “united”
    • '%united%' — name contains “united” anywhere
  • There is no single SQL keyword that replaces three LIKE patterns with wildcards. You must write the LIKE conditions explicitly.

Why This Happens in Real Systems

  • Developers often try to shorten repetitive OR clauses by reaching for IN, EXISTS, or other set-based operators.
  • Pattern matching in SQL is fundamentally an operator-level feature, not a set-level feature.
  • Some databases (e.g., PostgreSQL, MySQL) support full-text search or regular expressions (~, REGEXP), but these introduce their own complexity and performance trade-offs.

Real-World Impact

  • Overly complex OR chains can hurt readability and make queries harder to maintain.
  • Using IN with literal wildcards silently returns zero rows, which can cause silent data loss if not tested.
  • In large tables, three separate LIKE scans with leading wildcards (%united) can be slow — but that is a performance concern, not a correctness one.

Example or Code

-- Correct approach: three explicit LIKE conditions
SELECT name
FROM world
WHERE name LIKE 'united%'
   OR name LIKE '%united'
   OR name LIKE '%united%';

-- This returns zero rows — IN does not support wildcards
SELECT name
FROM world
WHERE name IN ('united%', '%united', '%united%');

-- Alternative using REGEXP (MySQL/PostgreSQL)
SELECT name
FROM world
WHERE name REGEXP 'united';

-- Alternative using full-text search (MySQL)
SELECT name
FROM world
WHERE MATCH(name) AGAINST('+united' IN BOOLEAN MODE);

How Senior Engineers Fix It

  • Keep the three LIKE clauses — they are explicit, correct, and portable across databases.
  • If performance is a concern, add a full-text index or use REGEXP with a single pattern rather than multiple LIKEs.
  • Document the reasoning so future maintainers understand why three conditions exist.
  • Avoid using IN with wildcards — it is a common pitfall that produces empty result sets without any error.

Why Juniors Miss It

  • Juniors often assume IN works like a pattern-matching operator because it accepts strings.
  • They may not realize that IN performs exact equality comparisons, not pattern matching.
  • Reading the documentation for IN versus LIKE is easy to skip when trying to “simplify” a query quickly.
  • The temptation to reduce three OR clauses into one “cleaner” line leads to silent bugs — the query runs but returns no data.

Leave a Comment