Retrieval, Predicates, NULL, Sorting and Built-in Functions

1. Retrieving Data with SELECT

SELECT describes the result required from one or more data sources:

SELECT product_id, product_name, price
FROM product;

The select list contains output expressions. FROM supplies source rows. A semicolon terminates the statement in many clients.

SQL is declarative. The query states which result is required, while the optimizer chooses an access plan. Physical row order, index order and insertion order are not part of the result unless ORDER BY is present.

2. Selecting Columns and Expressions

The select list may contain columns, constants, arithmetic and function calls:

SELECT product_name,
       price,
       price * 1.18 AS price_with_tax
FROM product;

An alias gives an output expression a readable label. AS is optional for column aliases but often improves clarity.

SELECT * returns every currently visible column. It is useful for exploration but fragile in application code because schema changes alter the result shape and transfer unnecessary data. Name the columns required by the caller.

3. Logical Query Processing

A useful conceptual order for a simple query is:

FROM forms source rows;
WHERE filters individual rows;
GROUP BY forms groups;
HAVING filters groups;
SELECT computes output expressions;
DISTINCT removes duplicate output rows;
ORDER BY orders the result;
LIMIT restricts returned rows.

The optimizer can execute differently as long as the observable result is equivalent. This logical order explains why a select-list alias is not generally available in WHERE but may be available in ORDER BY.

4. Filtering with WHERE

WHERE retains only rows for which its predicate evaluates TRUE:

SELECT product_id, product_name
FROM product
WHERE price >= 100;

Rows producing FALSE or UNKNOWN are removed. This rule is central to NULL behavior.

Comparison operators include =, <>, !=, <, <=, > and >=. Character comparison follows collation rules. Date comparisons should use valid date values rather than display-formatted strings.

5. Boolean Operators and Precedence

AND requires both conditions to be true. OR requires at least one true condition. NOT negates a truth value under three-valued logic.

AND has higher precedence than OR:

WHERE status = 'ACTIVE'
   OR status = 'PENDING' AND balance > 0

This groups the AND condition first. Parentheses should make intended business logic explicit:

WHERE (status = 'ACTIVE' OR status = 'PENDING')
  AND balance > 0

Do not rely on a reader remembering precedence in a complex predicate.

6. BETWEEN

BETWEEN tests an inclusive range:

WHERE price BETWEEN 100 AND 500

This is equivalent to price >= 100 AND price <= 500 for ordinary non-NULL values.

For a timestamp range representing a full day, an inclusive upper endpoint can be awkward because fractional seconds may exist. A half-open range is robust:

WHERE created_at >= '2026-02-10'
  AND created_at <  '2026-02-11'

NOT BETWEEN negates the range under NULL's three-valued logic.

7. IN

IN compares an expression with a list:

WHERE status IN ('NEW', 'PAID', 'SHIPPED')

It is clearer than a chain of equality conditions joined by OR. An IN operand can also be a subquery.

NOT IN has an important NULL issue. If the comparison set contains NULL and no equal value is found, the result can remain UNKNOWN rather than TRUE. For absence against a subquery, NOT EXISTS is usually safer because it tests whether matching rows exist directly.

8. Pattern Matching with LIKE

LIKE matches text patterns:

% matches a sequence of zero or more characters;
_ matches exactly one character.

WHERE product_name LIKE 'Pro%' WHERE code LIKE 'A_7'

The character set and collation affect comparison, including case sensitivity. To search for literal wildcard characters, use an escape convention supported by the statement and mode.

A pattern beginning with a wildcard often prevents efficient use of an ordinary leading-column B-tree index because no fixed starting range is known.

9. NULL and Three-Valued Logic

NULL represents missing, unknown or inapplicable information. It is not a value that equals itself in ordinary comparison.

salary = NULL

does not test absence. The comparison evaluates UNKNOWN. Use:

salary IS NULL
salary IS NOT NULL

SQL predicates can be TRUE, FALSE or UNKNOWN. WHERE retains only TRUE. Therefore a condition and its ordinary negation do not necessarily divide all rows when NULL is possible.

10. NULL in Compound Predicates

The important truth patterns are:

TRUE AND UNKNOWN produces UNKNOWN;
FALSE AND UNKNOWN produces FALSE;
TRUE OR UNKNOWN produces TRUE;
FALSE OR UNKNOWN produces UNKNOWN;
NOT UNKNOWN remains UNKNOWN.

Suppose discount is NULL. discount > 0 is UNKNOWN, so WHERE discount > 0 removes the row. WHERE NOT (discount > 0) also produces UNKNOWN and removes it. To include absent discounts, state that rule:

WHERE discount <= 0 OR discount IS NULL

11. NULL-Safe Expressions

COALESCE returns the first non-NULL argument:

SELECT product_name,
       COALESCE(discount, 0) AS effective_discount
FROM product;

It is useful for display and calculations when a defined substitute matches the domain. It does not change stored data.

NULLIF(a, b) returns NULL when a equals b, otherwise a. It can avoid division by zero:

total / NULLIF(quantity, 0)

IFNULL is a MySQL two-argument convenience, while COALESCE is standard and accepts several arguments.

12. DISTINCT

DISTINCT removes duplicate rows from the selected result:

SELECT DISTINCT city
FROM customer;

With several expressions, uniqueness applies to the entire selected combination:

SELECT DISTINCT city, state
FROM customer;

DISTINCT can hide an incorrect join that accidentally multiplies rows. Use it only when duplicate elimination is part of the required result, after understanding why duplicates arise.

13. Ordering Results

ORDER BY defines result order:

SELECT product_name, price
FROM product
ORDER BY price DESC, product_name ASC;

The second key orders rows whose prices compare equal. ASC is the default; DESC reverses direction.

Ordering can use selected aliases:

SELECT price * quantity AS line_total
FROM order_line
ORDER BY line_total DESC;

NULL placement and collation behavior should be checked for the database version and desired result. A CASE expression can define explicit NULL positioning.

14. Deterministic Ordering

If ORDER BY values are equal, the relative order among those rows is unspecified. Pagination based on nonunique order can therefore show repeated or skipped rows between executions.

Add a unique tie-breaker:

ORDER BY created_at DESC, order_id DESC

This produces a total deterministic order. It also supports keyset pagination using the last seen pair, which is often more stable and efficient than a large OFFSET.

15. LIMIT and OFFSET

MySQL LIMIT restricts the number of returned rows:

SELECT order_id, ordered_at
FROM order_header
ORDER BY ordered_at DESC, order_id DESC
LIMIT 20;

An offset can skip rows:

LIMIT 20 OFFSET 40

LIMIT without ORDER BY returns an arbitrary eligible subset. Large offsets may still require scanning and discarding many preceding rows. Keyset pagination applies a predicate based on the last ordered key and avoids that growing skip.

16. CASE Expressions

CASE derives a value conditionally:

SELECT order_id,
       CASE
           WHEN total >= 10000 THEN 'HIGH'
           WHEN total >= 5000  THEN 'MEDIUM'
           ELSE 'STANDARD'
       END AS order_band
FROM order_header;

The searched form evaluates predicates in order and returns the result for the first true branch. If no branch matches and ELSE is absent, the result is NULL.

CASE is an expression, so it can appear in SELECT, ORDER BY, aggregation and many other expression contexts.

17. String Functions

Common MySQL string functions include:

UPPER and LOWER for letter case conversion;
CONCAT and CONCAT_WS for joining text;
SUBSTRING for extracting part of a string;
TRIM for removing surrounding characters or spaces;
REPLACE for substitution;
LOCATE for finding a substring;
LEFT and RIGHT for end portions.

SELECT UPPER(full_name), CONCAT(city, ', ', state) FROM customer;

Most scalar functions return NULL when a required argument is NULL, though each function's documented behavior should be checked.

18. String Length

MySQL LENGTH returns the number of bytes in the encoded string. CHAR_LENGTH, also named CHARACTER_LENGTH, returns the number of characters.

For a multibyte Unicode value, these results can differ:

SELECT LENGTH(name), CHAR_LENGTH(name)
FROM customer;

Use byte length for storage and protocol concerns and character length for user-facing character counts. Visual symbols can contain multiple Unicode code points, so even character count is not always the same as perceived grapheme count.

19. Numeric Functions

Common numeric functions include:

ABS for absolute value;
ROUND for rounding;
CEIL and FLOOR for upward and downward integral boundaries;
MOD for remainder;
POW for exponentiation;
SQRT for square root.

SELECT price, ROUND(price * 1.18, 2) AS taxed_price FROM product;

Type matters. Exact DECIMAL calculations and approximate floating calculations have different precision behavior. Rounding for display is different from defining the legal rounding rule for financial posting.

20. Date and Time Functions

CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP obtain current temporal values. YEAR, MONTH, DAY, HOUR and related functions extract components.

DATE_ADD and DATE_SUB perform interval arithmetic:

SELECT DATE_ADD(ordered_at, INTERVAL 7 DAY)
FROM order_header;

DATEDIFF returns a day difference between dates. TIMESTAMPDIFF supports units and timestamp-like values. DATE_FORMAT creates display text, while STR_TO_DATE parses text according to a format.

Keep columns as temporal values for filtering and ordering. Format them only at an output boundary.

21. Functions and Index Use

Wrapping an indexed column in a function can make a predicate non-sargable:

WHERE YEAR(ordered_at) = 2026

A range preserves direct search potential:

WHERE ordered_at >= '2026-01-01'
  AND ordered_at <  '2027-01-01'

The first form may be supported by a matching functional index in appropriate versions, but the plan must be verified. Express predicates as ranges on stored indexed values when practical.

22. Data Modification Predicates

WHERE has the same filtering importance in UPDATE and DELETE:

UPDATE product
SET price = price * 1.05
WHERE category_id = 7;

DELETE FROM session_log
WHERE created_at < '2025-01-01';

Omitting WHERE targets all rows. Before a high-impact change, run a SELECT with the same predicate, start a transaction when supported, inspect affected counts and preserve a recovery path.

23. Expression Types and Conversion

MySQL may convert values to a common type for comparison or arithmetic. Implicit conversion can produce surprising results, particularly between text and numbers.

Use CAST when the intended conversion should be explicit:

CAST(text_amount AS DECIMAL(12, 2))

Better still, store values in their correct types. Repeated casting in queries often signals a schema or ingestion problem.

24. Writing Reliable Retrieval Queries

Name the required columns, state predicates with parentheses and handle NULL according to its real meaning. Use DISTINCT only for required result uniqueness. Add ORDER BY whenever order matters and include a unique tie-breaker for pagination.

Keep searchable columns unwrapped where possible, use correct temporal ranges and examine execution plans for important queries. A correct SELECT is defined by its values and explicit order, not by the row order observed in one convenient execution.