Expressions, Functions, NULL and Conditional Logic

Introduction

SQL is not limited to retrieving stored values. A database query can calculate, transform, validate, classify and format data. These operations are expressed through expressions, operators, built-in functions, conditional logic and predicates. They are essential in application development because business rules often concern derived values rather than individual columns: an order total, a customer's age, a subscription's validity, or whether an identifier has an acceptable format.

This chapter uses broadly portable SQL. Function names and regular-expression syntax vary among database products, so implementation-specific alternatives are identified where necessary. Examples use tables such as customers, orders and subscriptions; these are illustrative relations rather than assumptions about a particular database schema.

Learning objectives

By the end of this chapter, you should be able to:

• construct expressions using arithmetic, comparison, logical and concatenation operators;
• distinguish operator precedence and control evaluation with parentheses;
• use LIKE, escaping and regular expressions for pattern validation;
• convert values safely between common data types;
• apply string, numeric and date/time functions;
• explain NULL and SQL's three-valued logic;
• write robust CASE expressions and validation rules;
• analyse portability, performance, data-quality and security implications of transformations.

1. Expressions and operators

An expression is a combination of literals, column references, functions, operators and subexpressions that produces a value. For example:

SELECT quantity * unit_price AS line_amount
FROM order_items;

The expression quantity * unit_price is evaluated for each row. Its result is not necessarily stored; it is computed as part of query execution.

1.1 Main operator classes

SQL data types influence operator behaviour. Integer division, for example, may discard a fractional part in some systems. To request a decimal result, cast at least one operand:

SELECT CAST(quantity AS DECIMAL(12,2)) / package_size AS average_units
FROM products;

Division by zero is an error in many systems and must be prevented through validation or conditional logic.

1.2 Precedence and parentheses

Operators are not generally evaluated from left to right without regard to precedence. Multiplication and division normally precede addition and subtraction; comparison occurs after arithmetic; logical NOT generally precedes AND, which precedes OR. Exact precedence can vary, so parentheses are the safest way to communicate intent.

-- Ambiguous to a reader and easy to misinterpret
WHERE status = 'OPEN' OR status = 'PENDING' AND priority = 'HIGH'

-- Explicit: high priority is required for either status
WHERE (status = 'OPEN' OR status = 'PENDING')
  AND priority = 'HIGH'

A query should express the business rule rather than depend on a reader remembering a vendor's precedence table.

2. Comparisons, ranges and pattern matching

Comparison predicates produce TRUE, FALSE or, when NULL is involved, UNKNOWN. Range tests include:

WHERE order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01'

The half-open interval includes 1 January and excludes 1 February. It is often safer than testing <= '2025-01-31 23:59:59', because timestamps may have fractional seconds.

BETWEEN a AND b is inclusive at both ends and is equivalent to value >= a AND value <= b when none of the relevant values is NULL. For time intervals, explicit lower and upper predicates usually communicate boundary policy more clearly.

2.1 LIKE patterns

LIKE supports two principal wildcards:

• % matches zero or more characters;
• _ matches exactly one character.

Thus, LIKE 'AB%' finds values beginning with AB, while LIKE '__-___' means two arbitrary characters, a hyphen, and three arbitrary characters. It does not mean “two letters, a hyphen, and three letters.”

If a rule requires letters, use a regular expression where the DBMS supports one, or combine LIKE with character tests. For example, in PostgreSQL:

WHERE code ~ '^[A-Za-z]{2}-[A-Za-z]{3}$'

The anchors ^ and $ require the entire value to match, and {2} and {3} specify exact counts. Other systems use functions such as REGEXP_LIKE(code, '^[A-Za-z]{2}-[A-Za-z]{3}$'). Regular expressions are not fully portable, so the target DBMS documentation must be consulted.

To search for a literal % or _, use an escape character when supported:

WHERE description LIKE '%10!%%' ESCAPE '!'

Here !% represents a literal percent sign.

Case sensitivity depends on collation and DBMS settings. A robust application should define whether codes are case-sensitive and should not assume that LIKE behaves identically across systems.

3. Type conversion

Type conversion changes a value's data type. Explicit conversion is preferable to relying on implicit conversion because it documents intent and reduces dependence on session settings.

SELECT CAST(unit_price AS DECIMAL(12,2)) AS price,
       CAST(order_date AS DATE) AS order_day
FROM orders;

Common conversions include character-to-number, number-to-character, character-to-date, date-to-timestamp and timestamp-to-date. Conversion can fail when input is malformed. Some DBMSs provide safe forms such as TRY_CAST; otherwise, validate the input before conversion or handle errors in application code.

Formatting and conversion are different operations. A date is a temporal value; a formatted date such as '31/01/2025' is text. Storing dates as text makes ordering, arithmetic and validation more difficult. Store values using suitable native types and format them at the presentation boundary.

Precision matters. A decimal monetary amount should normally use an exact numeric type such as DECIMAL(p,s), where p is total precision and s is digits after the decimal point. Binary floating-point types can introduce representation differences and are not ideal for exact currency accounting.

4. String functions

String functions support cleansing, searching and presentation. Common examples include:

SELECT TRIM(full_name) AS cleaned_name,
       UPPER(country_code) AS country_code_upper,
       LOWER(email_address) AS normalized_email,
       CHAR_LENGTH(full_name) AS name_length,
       SUBSTRING(product_code FROM 1 FOR 4) AS prefix
FROM customers;

Function signatures differ: some systems use SUBSTRING(text, start, length), and some use LEN rather than CHAR_LENGTH. CHAR_LENGTH counts characters, whereas byte length can differ in multibyte encodings.

COALESCE(a, b, c) returns the first non-NULL argument. It is useful for display defaults:

SELECT COALESCE(preferred_name, legal_name, 'Unnamed') AS display_name
FROM customers;

It should not be used to conceal missing data when absence has analytical meaning. An empty string and NULL are distinct in many systems, although some DBMSs treat them similarly.

String normalization may include trimming whitespace, standardizing case and applying a documented Unicode or locale policy. Normalization should be performed consistently; otherwise, joins and uniqueness checks can produce surprising results.

5. Numeric functions

Typical numeric functions include ABS, ROUND, CEILING or CEIL, FLOOR, MOD, POWER and SQRT.

SELECT ROUND(unit_price * quantity, 2) AS rounded_amount,
       ABS(balance_change) AS magnitude
FROM order_items;

Rounding policy is a business rule. Rounding each line before summing can produce a different result from summing exact lines and rounding once. For example, if two exact line amounts are 1.005 and 1.005, rounding each to two decimal places under a conventional half-up policy gives 1.01 + 1.01 = 2.02, whereas summing first gives 2.010 and rounding once gives 2.01. The organisation must specify the policy and numeric type; DBMS rounding conventions can differ for halfway values.

Aggregates such as SUM, AVG, MIN and MAX operate across rows and interact with NULL: most ignore NULL inputs, while COUNT(*) counts rows and COUNT(column) counts non-NULL values. This distinction is important when interpreting incomplete data.

6. Date and time functions

Dates and timestamps support arithmetic, extraction and comparison. Portable date literals include:

SELECT DATE '2025-03-08' AS reference_day,
       CURRENT_DATE AS today,
       CURRENT_TIMESTAMP AS current_moment;

CURRENT_DATE and CURRENT_TIMESTAMP are standard SQL constructs and require no undefined system table. Their exact evaluation semantics and time-zone behaviour depend on the DBMS and transaction context.

Examples of extraction include:

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Adding an interval is expressed portably in many systems as:

SELECT order_date + INTERVAL '7' DAY AS due_date
FROM orders;

However, interval syntax varies. Month arithmetic requires special care because months have different lengths. For example, adding one month to a date near month-end may be handled differently by different products. Test boundary cases such as leap days and month-end dates.

For elapsed time, compare timestamps or use a DBMS-specific difference function. Do not infer that a calendar month always equals a fixed number of days. Time zones and daylight-saving transitions also mean that a local-clock difference may not equal an elapsed duration measured in UTC.

7. NULL and three-valued logic

NULL represents an absent, unknown or inapplicable value; it is not automatically zero, an empty string or a false value. SQL therefore uses three truth values: TRUE, FALSE and UNKNOWN.

For ordinary comparisons:

• 5 = NULL is UNKNOWN;
• NULL = NULL is UNKNOWN;
• NULL IS NULL is TRUE.

Use IS NULL and IS NOT NULL, never = NULL or <> NULL, to test missingness.

The core truth behaviour is:

NOT UNKNOWN is still UNKNOWN. In a WHERE clause, only TRUE passes; both FALSE and UNKNOWN are rejected. This explains why a predicate such as credit_limit > 0 excludes rows where credit_limit is NULL.

COALESCE can replace a missing value for a specific purpose, but doing so changes semantics. In analytics, report both the value and completeness where appropriate. NULL handling in aggregates also requires care: AVG(score) averages known scores, not all rows. If missing scores should be treated as zero, that must be explicitly justified rather than assumed.

SQL's NOT IN is a common trap. If the list contains NULL, comparisons can become UNKNOWN, potentially returning no rows. NOT EXISTS is often safer for anti-joins because it expresses existence directly.

8. CASE expressions and conditional transformation

CASE implements conditional logic inside a query. The searched form is:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ELSE result_default
END

Conditions are evaluated in order, and the first true condition supplies the result. Therefore, overlapping conditions must be ordered deliberately. Include an ELSE unless omission and resulting NULL are intentional.

SELECT order_id,
       CASE
         WHEN total_amount >= 1000 THEN 'Premium'
         WHEN total_amount >= 500  THEN 'Standard'
         WHEN total_amount >= 0    THEN 'Basic'
         ELSE 'Invalid amount'
       END AS order_segment
FROM orders;

The simple form compares one expression:

CASE status
  WHEN 'P' THEN 'Paid'
  WHEN 'C' THEN 'Cancelled'
  ELSE 'Other'
END

All result branches should have compatible types. A CASE expression can classify data, implement conditional aggregation and encode controlled business rules, but complex rules are often better maintained in a reference table or a view rather than duplicated across application queries.

Conditional aggregation is particularly useful:

SELECT customer_id,
       SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) AS paid_total,
       COUNT(CASE WHEN status = 'PAID' THEN 1 END) AS paid_orders
FROM orders
GROUP BY customer_id;

The COUNT expression counts only rows for which the CASE returns a non-NULL value.

9. Worked application: subscription validation

Consider a subscriptions table with start_date, end_date, plan_code and customer_email. The business rule is:

1. both dates must be present;
2. the subscription must have a positive duration, so end_date must be later than start_date;
3. the plan code must contain exactly two letters, a hyphen and three letters;
4. the email must not be absent.

A PostgreSQL-style validation query is:

SELECT subscription_id,
       CASE
         WHEN start_date IS NULL OR end_date IS NULL
           THEN 'INVALID: missing date'
         WHEN end_date <= start_date
           THEN 'INVALID: non-positive duration'
         WHEN plan_code IS NULL
           THEN 'INVALID: missing plan code'
         WHEN plan_code !~ '^[A-Za-z]{2}-[A-Za-z]{3}$'
           THEN 'INVALID: plan format'
         WHEN customer_email IS NULL
           THEN 'INVALID: missing email'
         ELSE 'VALID'
       END AS validation_result
FROM subscriptions;

The condition end_date <= start_date is essential. It rejects both an end date before the start date and equal dates, thereby rejecting zero-length subscriptions. The regular expression is also essential: LIKE '__-___' would permit digits, punctuation and other characters in the letter positions.

A portable alternative is to use a database's regular-expression predicate, such as REGEXP_LIKE, or to validate the code in application logic before insertion. The database should additionally enforce structural rules where possible, for example:

CHECK (end_date IS NULL OR start_date IS NULL OR end_date > start_date)

This constraint alone does not require both dates to be present; separate NOT NULL constraints or a combined rule are needed when presence is mandatory. Validation in a query is useful for diagnosis, while constraints provide stronger protection against invalid writes.

10. Applied case: order-quality dashboard

Illustrative case. A retailer receives orders from several applications. Product names contain inconsistent whitespace and case, some discount values are NULL, and managers need a monthly dashboard separating valid and suspect transactions.

Problem. A report that treats a missing discount as a known zero, converts malformed quantities implicitly, and uses an inclusive timestamp endpoint can misstate revenue and month totals.

Approach. The data team first defines a quality policy: missing discount means “not recorded,” not necessarily zero; quantity must be a positive integer; order timestamps are stored in UTC; monthly periods use a half-open interval. A query then uses explicit casts, TRIM and UPPER for product labels, COALESCE only where the policy explicitly defines a default, and CASE for classification. Invalid rows are counted separately rather than silently discarded.

A conceptual query is:

SELECT
  UPPER(TRIM(product_name)) AS normalized_product,
  SUM(CASE WHEN quantity > 0 AND unit_price >= 0
           THEN quantity * unit_price ELSE 0 END) AS gross_value,
  COUNT(CASE WHEN quantity <= 0 OR unit_price < 0 THEN 1 END) AS invalid_rows,
  COUNT(CASE WHEN discount IS NULL THEN 1 END) AS missing_discount_rows
FROM order_items
WHERE order_timestamp >= TIMESTAMP '2025-01-01 00:00:00'
  AND order_timestamp <  TIMESTAMP '2025-02-01 00:00:00'
GROUP BY UPPER(TRIM(product_name));

Outcome. The dashboard distinguishes calculated value from data-quality exceptions and avoids double-counting boundary timestamps. Data stewards can investigate invalid rows, while analysts can interpret missing discounts explicitly.

Learning points. Transformation rules must be documented; missingness must not be silently converted into business facts; temporal boundaries should be precise; and validation should occur close to the data source as well as in application code.

11. Performance, governance and professional practice

Functions in a WHERE predicate can prevent an index from being used efficiently. For example, WHERE UPPER(email) = '[email protected]' may require an expression index or a pre-normalized column. A sargable range such as order_timestamp >= ... AND order_timestamp < ... is generally preferable to extracting the month from every row.

Do not concatenate untrusted input into SQL expressions. Use parameterized queries; this is a primary defence against SQL injection. Dynamic identifiers require stricter allow-list validation because ordinary parameters cannot replace all identifiers.

Centralize rules that affect regulatory or financial reporting. A view, constraint, reference table or tested stored routine may be more reliable than repeating a CASE expression in many reports. Version-control SQL transformations and test boundary cases: NULL, zero, negative values, equal dates, leap days, month ends, empty strings, Unicode text and malformed patterns.

AI-assisted query generation can accelerate drafting, but generated SQL must be reviewed for dialect assumptions, unsafe concatenation, incorrect NULL logic, hidden data leakage and unsupported functions. The database's execution plan and representative test data remain authoritative.

Limitations and dialect variation

SQL is standardized, but implementations differ in regular expressions, date intervals, concatenation, implicit casts, rounding, collations, empty-string treatment and evaluation optimizations. A CASE expression should not be used to assume that an unsafe expression is always physically skipped in every optimizer context; prevent errors through valid predicates, safe conversion functions or constraints. Always test against the production DBMS, its version and its collation/time-zone configuration.

Chapter Summary

Expressions allow SQL to calculate values from columns, literals and functions. Arithmetic, comparison and logical operators must be combined with explicit parentheses when business meaning could be unclear. LIKE provides simple wildcard matching, but _ means any single character; it does not enforce letters. Regular expressions or equivalent validation are required for strict formats.

Explicit type conversion improves clarity and portability, while native numeric and temporal types preserve correct operations. String, numeric and date functions support cleansing and analysis, but rounding, time zones and month arithmetic require documented policies. NULL represents missing or unknown information and introduces three-valued logic; IS NULL, careful aggregates and cautious use of COALESCE are essential. CASE provides ordered conditional transformation, and validation rules must handle boundary conditions such as equal dates. Finally, robust SQL combines correct logic with constraints, parameterization, testing, indexing strategy and awareness of DBMS-specific behaviour.

Key Terms

Expression: A SQL construct that evaluates to a value.

Operator: A symbol or keyword that performs an operation on values.

Predicate: An expression used to test a condition.

Precedence: The rules determining the order in which operators are evaluated.

Pattern matching: Comparing text with wildcards or regular expressions.

Regular expression: A formal pattern language for describing text formats.

Type conversion: Changing a value from one data type to another.

Casting: Explicit conversion using a construct such as CAST.

NULL: A marker for absent, unknown or inapplicable information.

Three-valued logic: SQL logic whose outcomes are TRUE, FALSE and UNKNOWN.

COALESCE: A function returning the first non-NULL argument.

CASE: A conditional SQL expression.

Sargability: The extent to which a predicate can use an index efficiently.

Half-open interval: An interval including its lower bound but excluding its upper bound.

Collation: Rules governing character comparison and ordering.

Explicit conversion: A conversion stated directly by the query writer.

Conditional aggregation: Aggregation over values selected by a condition.

Data validation: Testing whether values satisfy defined quality rules.

Review Questions

Short Answer Questions

1. What is the difference between LIKE '%' and LIKE '_'?
2. Why should IS NULL be used instead of = NULL?
3. What is the purpose of explicit type conversion?
4. How does COUNT(*) differ from COUNT(column) when the column contains NULL?
5. Why is a half-open interval useful for filtering timestamp data?

Long Answer Questions

1. Explain SQL three-valued logic and show how UNKNOWN affects a WHERE clause.
2. Compare LIKE and regular expressions for data validation, including portability limitations.
3. Discuss how rounding policy and numeric data types can affect financial calculations.
4. Explain searched and simple CASE expressions and give appropriate uses for each.
5. Analyse the risks of implicit type conversion and propose professional safeguards.

Analytical / Application Questions

1. Design a validation query for a booking table requiring non-NULL start and end timestamps, with the end strictly later than the start. Explain how your query handles equal timestamps.
2. A report uses WHERE EXTRACT(MONTH FROM order_timestamp) = 1. Rewrite it for January 2025 using an index-friendly range and explain why your version is preferable.
3. A product code rule is “exactly three uppercase letters, a slash, and two digits.” Explain why LIKE '___/__' is insufficient and provide a regular-expression solution for a DBMS that supports REGEXP_LIKE.

Multiple Choice Questions with Answers

Question 1

Which predicate correctly tests whether a value is missing?

A. value = NULL

B. value <> NULL

C. value IS NULL

D. value = 'NULL'

Correct Answer: C. value IS NULL

Question 2

What does the underscore wildcard in a LIKE pattern match?

A. Zero or more characters

B. Exactly one arbitrary character

C. Only one letter

D. A literal underscore only

Correct Answer: B. Exactly one arbitrary character

Question 3

Which condition rejects both equal and earlier subscription end dates?

A. end_date < start_date

B. end_date >= start_date

C. end_date <= start_date

D. end_date = start_date

Correct Answer: C. end_date <= start_date

Question 4

What is the result of TRUE AND UNKNOWN in SQL three-valued logic?

A. TRUE

B. FALSE

C. UNKNOWN

D. An empty string

Correct Answer: C. UNKNOWN

Question 5

Which function returns the first non-NULL argument?

A. NULLIF

B. COALESCE

C. ISNULLONLY

D. FIRST_VALUE_ONLY

Correct Answer: B. COALESCE

Question 6

Which expression defines a half-open January 2025 timestamp interval?

A. ts > TIMESTAMP '2025-01-01' AND ts <= TIMESTAMP '2025-01-31'

B. ts >= TIMESTAMP '2025-01-01' AND ts < TIMESTAMP '2025-02-01'

C. ts BETWEEN TIMESTAMP '2025-01-01' AND TIMESTAMP '2025-02-01'

D. EXTRACT(MONTH FROM ts) = 1 AND EXTRACT(YEAR FROM ts) = 2025

Correct Answer: B. ts >= TIMESTAMP '2025-01-01' AND ts < TIMESTAMP '2025-02-01'

Question 7

Why is LIKE '__-___' insufficient for a two-letter, hyphen, three-letter code?

A. LIKE cannot test text

B. The hyphen is always treated as a wildcard

C. Underscores match arbitrary characters, not specifically letters

D. The pattern requires six hyphens

Correct Answer: C. Underscores match arbitrary characters, not specifically letters

Question 8

Which aggregate counts all rows, including rows where a particular column is NULL?

A. COUNT(*)

B. COUNT(column_name)

C. AVG(column_name)

D. SUM(column_name)

Correct Answer: A. COUNT(*)

Question 9

In a searched CASE expression, which result is used when multiple WHEN conditions are true?

A. The last true condition

B. The first true condition

C. All true results concatenated

D. A random true condition

Correct Answer: B. The first true condition

Question 10

Which practice most directly reduces SQL injection risk when user input supplies a value?

A. Concatenating the input into SQL text

B. Using a parameterized query

C. Converting the input to uppercase

D. Applying TRIM only

Correct Answer: B. Using a parameterized query