MySQL Operators and Expressions

Expressions combine values

An SQL expression computes a value from constants, columns, operators, and functions. Expressions appear in a SELECT list, a WHERE condition, an ORDER BY clause, and other parts of a statement. Arithmetic operators such as addition, subtraction, multiplication, and division can calculate derived values. Comparison operators such as equals, not-equals, less-than, and greater-than produce conditions. Understanding their result and type behavior helps avoid subtle errors, especially when values can be NULL or have different data types.

Use logical operators to compose conditions. AND requires both sides to be true; OR accepts either side; NOT negates a condition. Parentheses are valuable even when operator precedence is known because they communicate intent to reviewers. For example, “active and in either category” should be written as active = TRUE AND (category_id = 4 OR category_id = 7). Without parentheses, an OR branch can accidentally bypass the active condition.

Comparison, ranges, and membership

Use IN for membership in a known set, BETWEEN for an inclusive range, and LIKE for simple string patterns. BETWEEN includes both endpoints, which matters for date/time boundaries and numeric ranges. An IN list is often clearer than a chain of equality checks, but a very large dynamic list may require a different design. Use IS NULL to test for NULL rather than equality, because NULL represents an unknown or missing value and does not behave like an ordinary value.

SELECT product_id, category_id, price
FROM products
WHERE active = TRUE
  AND category_id IN (4, 7)
  AND price >= 10
  AND price < 80;

This form uses a half-open price range, which is often convenient when adjacent ranges must not overlap. It also makes the intended relationship between predicates clear: every returned row is active, belongs to one of the selected categories, and falls in the specified price interval. The database can still choose an access plan based on indexes, statistics, and data distribution.

Pattern matching and index implications

LIKE supports wildcard matching: percent represents zero or more characters, while underscore represents one character. A prefix pattern such as 'lamp%' may be able to use a conventional B-tree index depending on collation and query conditions. A leading wildcard such as '%lamp' generally prevents a normal index from seeking by the beginning of the value. For language-aware search, relevance ranking, or tokenization, consider MySQL full-text search rather than assuming LIKE is a complete search engine.

String comparisons depend on collation. A case-insensitive collation may treat two strings differing only in letter case as equal. This behavior also affects unique indexes, so test the collation against real identifiers and user expectations. Do not try to solve these requirements with ad hoc LOWER calls without checking their performance and Unicode behavior.

Type conversion and NULL logic

MySQL can convert values during comparison, but implicit conversions can yield surprising results—for instance, comparing a numeric column with a text value that is not a clean number. Keep parameter types aligned with their columns and validate inputs in the application. Explicit casts are useful when the conversion is intentional, but applying a conversion to an indexed column may change the access path. Test the plan instead of assuming that an expression remains index-friendly.

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN because of NULL. WHERE keeps only rows where the condition is true, so a row with an unknown comparison is filtered out. NOT does not turn UNKNOWN into TRUE. This is why missing-value handling should be designed rather than treated as ordinary equality. The topic NULL values and three-valued logic explores these cases in detail.

Write conditions that are easy to reason about

Use explicit parentheses, compatible types, and named expressions where they improve readability. Check boundary values and NULL cases in tests. For dynamic application input, bind values with prepared statements; never concatenate untrusted input into a SQL expression. If the user chooses a sort direction or field, map that choice through a fixed allowlist because identifiers are not ordinary bind values. These practices improve both correctness and security.

Operators are building blocks; reliable SQL comes from understanding how they combine, how NULL and types affect their results, and how predicates relate to real data. Continue with filtering and sorting, NULL handling, and safe parameterized queries.