SQL Expressions
Introduction
A SQL expression is a combination of literals, column references, operators, functions, and other expressions that the database evaluates to produce a value or condition. Expressions perform calculations, transform text, compare values, classify rows, convert data types, summarize groups, and define rules throughout SQL.
quantity * unit_price
This arithmetic expression reads two column values, multiplies them, and produces one result for each input row. In a query it can be selected, filtered, ordered, grouped where supported, or assigned during an update.
Expressions are smaller than statements and live inside clauses. Understanding that relationship is essential: a clause defines a role in the statement, while expressions supply values and conditions within that role.
Expression, Value, Statement, and Clause
| Concept | Example | Meaning |
|---|---|---|
| Value | 100 | A specific scalar result |
| Expression | quantity * unit_price | Evaluates to a value |
| Predicate | total_amount > 100 | Evaluates to true, false, or unknown |
| Clause | WHERE total_amount > 100 | Uses a predicate to filter rows |
| Statement | SELECT ... FROM ... WHERE ... | Complete database instruction |
An expression produces a result but does not independently instruct the database to query or change data. The statement provides that complete instruction.
Expression Components
Most expressions are built from a small set of components:
(quantity * unit_price) - discount_amount
quantity, unit_price, and discount_amount are column references. Multiplication and subtraction are operators. Parentheses control grouping. The database evaluates the components according to precedence, data types, NULL rules, and product-specific semantics.
Literal Expressions
A literal represents a value written directly in SQL. Numeric literals may appear as integers or decimals, while character literals use single quotes.
42
19.95
'PAID'
'2026-10-31'
A quoted date-looking value is initially a character literal unless the context or explicit conversion gives it a date type. For clear and portable code, bind typed parameters or use supported typed literal and conversion syntax rather than relying on ambiguous implicit conversion.
Column Expressions
A column reference is one of the simplest expressions. It evaluates to the column value for the current row or group context.
SELECT customer_id, customer_name
FROM customers;
Qualify a column with its source alias when several sources expose the same name:
SELECT c.customer_id, o.order_id
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id;
Arithmetic Expressions
Arithmetic expressions combine numeric operands through operators such as addition, subtraction, multiplication, division, and product-supported remainder operations.
SELECT
subtotal_amount + tax_amount AS total_before_discount,
total_amount - discount_amount AS net_amount,
quantity * unit_price AS line_total,
total_amount / installment_count AS installment_amount
FROM invoices;
Result type and precision depend on operand types and database rules. Integer division can discard a fractional part on some systems, so financial and ratio calculations should use deliberate decimal types.
Operator Precedence
Multiplication and division are commonly evaluated before addition and subtraction. Parentheses make intended grouping explicit.
price + tax_rate * price
(price + shipping_amount) * quantity
The first expression applies tax to price before adding the original price. The second multiplies the combined price and shipping amount by quantity. Even when precedence already gives the desired result, parentheses can communicate business intent.
Real-World Price and Discount Expressions
SELECT
quantity * unit_price AS gross_amount,
quantity * unit_price * discount_rate AS discount_amount,
quantity * unit_price * (1 - discount_rate) AS net_amount
FROM order_items;
This design assumes discount_rate stores a fraction such as 0.15. If the column stores a percentage such as 15, the expression must divide by 100 using decimal arithmetic. Document the scale and enforce it with constraints.
Percentage Changes
salary * (1 + increase_percent / 100.0)
original_amount * (1 - discount_percent / 100.0)
The decimal literal 100.0 helps avoid unintended integer division in products where operand types influence division. Store money and rates using appropriate exact numeric types rather than floating-point types when exact decimal behavior is required.
Expression Aliases
An alias labels an expression in the result:
SELECT quantity * unit_price AS line_total
FROM order_items;
The alias improves readability for people, application mappings, reports, and later query clauses where alias visibility is supported. It is a result name, not a procedural variable. It does not store the value or make the alias available everywhere in the same query block.
String Expressions
String expressions can change case, trim whitespace, extract portions, replace text, or concatenate values. Function and concatenation syntax varies across databases.
SELECT
LOWER(email_address) AS normalized_email,
TRIM(customer_name) AS clean_name
FROM customers;
Some products use CONCAT(), some support the standard-style || operator, and SQL Server commonly uses + for string concatenation. NULL behavior can also differ, so shared code should use the target database's documented form.
Function Expressions
A function call is an expression that receives arguments and returns a value. Functions can be nested:
UPPER(TRIM(customer_name))
The database evaluates the inner TRIM first, then passes its result to UPPER. Deep nesting becomes difficult to test and read; a CTE or staged expression can expose intermediate values clearly.
Numeric Functions
SELECT
ABS(balance_difference) AS absolute_difference,
ROUND(total_amount, 2) AS rounded_total,
CEILING(required_capacity) AS capacity_units
FROM account_summary;
Function names, argument rules, and return types can differ by database. Rounding placement matters in finance: rounding each line before summing can produce a different total from rounding only the final sum.
Date and Time Expressions
Date expressions can return the current date or timestamp, extract components, add intervals, or calculate differences. Syntax differs substantially across products.
SELECT CURRENT_DATE AS report_date,
CURRENT_TIMESTAMP AS generated_at;
Do not assume that subtracting two date values or adding an integer has the same meaning everywhere. Define time zone, precision, calendar, and boundary behavior, then use the database's supported interval and date functions.
Comparison Expressions and Predicates
A comparison expression evaluates a relationship between operands. Common operators include equality, inequality, and ordering comparisons.
total_amount >= 100
order_status = 'PAID'
completed_at < :cutoff_time
customer_id <> :excluded_customer_id
These conditions are predicates. Because SQL includes NULL, a predicate may evaluate to unknown as well as true or false.
Three-Valued Logic
SQL uses true, false, and unknown for Boolean search conditions. A comparison involving NULL usually produces unknown because NULL means missing or unknown, not a specific value.
WHERE discount_amount > 0
If discount_amount is NULL, this condition is unknown. A WHERE clause retains only true rows, so both false and unknown rows are removed.
NULL Comparisons
Do not use equality to test for NULL:
-- Incorrect
WHERE deleted_at = NULL
-- Correct
WHERE deleted_at IS NULL
WHERE deleted_at IS NOT NULL
Some databases provide additional null-safe equality features, but their syntax is product-specific. Standard null checks remain essential.
Logical Expressions
AND, OR, and NOT combine or negate conditions.
WHERE is_active = TRUE
AND (customer_type = 'BUSINESS'
OR lifetime_value >= 10000)
AND NOT account_status = 'BLOCKED'
NOT, AND, and OR have precedence rules, but parentheses are clearer whenever multiple operators appear. NULL can propagate unknown through logical combinations, so test missing values explicitly when they have business meaning.
IN and NOT IN
WHERE order_status IN ('PAID', 'SHIPPED', 'DELIVERED')
IN expresses membership in a list or subquery result. NOT IN requires care when the list or subquery can contain NULL; the unknown result can cause no rows to qualify. A correlated NOT EXISTS often expresses anti-matching more safely.
BETWEEN and Range Expressions
WHERE total_amount BETWEEN 100 AND 500
BETWEEN includes both boundaries. Inclusive upper boundaries can be awkward for timestamps because a date literal may represent midnight rather than the end of a day. A half-open range is often clearer:
WHERE ordered_at >= :start_time
AND ordered_at < :next_period_start
LIKE and Pattern Expressions
WHERE customer_name LIKE 'A%'
WHERE product_code LIKE 'AB_12'
Percent matches any sequence of characters and underscore commonly matches one character. Escaping, case sensitivity, collation, and available pattern operators differ among databases. Parameterize user input and escape wildcard characters when a literal search is required.
The CASE Expression
CASE is an expression that returns a value. It can classify data, select among calculations, or conditionally contribute to an aggregate.
SELECT order_id,
CASE
WHEN total_amount >= 1000 THEN 'HIGH'
WHEN total_amount >= 100 THEN 'MEDIUM'
ELSE 'LOW'
END AS order_band
FROM orders;
This is searched CASE because each branch contains a condition. A simple CASE compares one expression against several values.
Simple CASE
CASE order_status
WHEN 'NEW' THEN 'Awaiting processing'
WHEN 'PAID' THEN 'Ready to ship'
WHEN 'CANCELLED' THEN 'Closed'
ELSE 'Unknown status'
END
Simple CASE is concise for equality comparisons. Searched CASE is required for ranges, compound predicates, or different columns.
CASE Evaluation Order
Conditions should be ordered from more specific or higher thresholds to broader ones. Otherwise an earlier branch can capture a row before a later branch is reached.
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'Needs improvement'
END
Design branches so they are understandable even without relying on subtle overlap. Also ensure branch results resolve to a compatible data type.
COALESCE and NULL Substitution
COALESCE returns the first non-null expression from its arguments.
COALESCE(preferred_name, legal_name, 'Unknown')
It is useful for fallback display and carefully defined defaults. Substitution changes meaning: replacing a missing discount with zero is valid only if the business rule says absence means no discount.
NULL Arithmetic
Arithmetic involving NULL normally produces NULL:
subtotal_amount + tax_amount + discount_amount
If discount_amount is missing, the whole result may become unknown. Apply COALESCE only to values whose absence has an explicit identity meaning:
subtotal_amount
+ COALESCE(tax_amount, 0)
- COALESCE(discount_amount, 0)
CAST and Explicit Conversion
CAST converts an expression to a target type:
CAST(order_id AS VARCHAR(30))
CAST(:input_date AS DATE)
CAST(quantity AS DECIMAL(12, 2)) / package_size
Explicit conversion makes intent visible and avoids some implicit-conversion surprises. Conversion can still fail when input is invalid, lose precision, or change index usage. Product-specific safe-conversion functions may return NULL instead of raising an error.
Implicit Conversion Risks
Databases may convert operands automatically when types differ. Comparing a numeric column to a character parameter can convert one side, fail for invalid input, or prevent an index seek.
Bind application parameters using the target column's type. Keep schema types consistent across joins and foreign keys. Do not depend on a conversion rule that may differ among databases or change with locale settings.
Constant and Mixed Expressions
A constant expression contains no column reference and can be evaluated independently of table rows. Examples include 2 + 3, CAST('2026-10-31' AS DATE), and a deterministic function applied only to literals. A mixed expression combines constants with row data, such as salary * 1.05.
The distinction helps during analysis. A constant expression may be folded once by the optimizer, while a row-dependent expression normally needs a value for each relevant row. This is an optimization possibility, not a guarantee about physical execution order.
Short-Circuit Evaluation Caution
Developers sometimes assume SQL evaluates logical terms from left to right and stops as soon as the result is known. The optimizer is generally free to transform and reorder predicates while preserving SQL semantics. Do not depend on a preceding condition to protect an unsafe conversion or calculation.
-- Do not assume the first condition always prevents the cast
WHERE input_text <> ''
AND CAST(input_text AS INTEGER) > 0
Validate data at ingestion, use constraints and appropriate types, or use the database's safe-conversion feature where available. The same caution applies to division, parsing, and user-defined functions with side effects.
Aggregate Expressions
Aggregate functions calculate one value from a set of rows.
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent,
AVG(total_amount) AS average_order,
MAX(ordered_at) AS latest_order_at
FROM orders
GROUP BY customer_id;
Most aggregates ignore NULL input values, while COUNT(*) counts rows. Understand each function's null behavior and result type.
Arithmetic with Aggregates
SUM(quantity * unit_price)
SUM(total_amount) / NULLIF(COUNT(*), 0)
ROUND(AVG(total_amount), 2)
These are expressions composed of aggregate functions, arithmetic operators, and scalar functions. Result precision and division semantics still matter.
Conditional Aggregation
CASE inside an aggregate can calculate several measures in one grouped query:
SELECT customer_id,
SUM(CASE
WHEN order_status = 'PAID' THEN total_amount
ELSE 0
END) AS paid_total,
SUM(CASE
WHEN order_status = 'REFUNDED' THEN total_amount
ELSE 0
END) AS refunded_total
FROM orders
GROUP BY customer_id;
The CASE expression returns a value for each row; SUM then aggregates those values within each customer group.
Expressions in SQL Clauses
| Clause | Expression example | Role |
|---|---|---|
SELECT | quantity * unit_price | Produce output value |
WHERE | total_amount > 100 | Filter rows |
JOIN ... ON | o.customer_id = c.customer_id | Match sources |
GROUP BY | DATE_TRUNC('month', ordered_at) | Define grouping key where supported |
HAVING | SUM(total_amount) > 1000 | Filter groups |
ORDER BY | LOWER(customer_name) | Define result ordering |
SET | quantity = quantity - 1 | Assign updated value |
WHERE and HAVING Expression Contexts
WHERE evaluates row-level predicates before grouping. HAVING evaluates group-level predicates after aggregation. Placing a non-aggregate filter in WHERE usually reduces the rows that must be grouped and states the intent more accurately.
SELECT customer_id, SUM(total_amount) AS paid_total
FROM orders
WHERE order_status = 'PAID'
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;
The status expression decides which rows enter each group. The aggregate expression in HAVING decides which completed groups appear in the result.
ORDER BY Expressions
An ordering expression determines presentation order without necessarily appearing in the select list, subject to database and query restrictions.
SELECT customer_id, customer_name
FROM customers
ORDER BY LOWER(customer_name), customer_id;
The second expression creates deterministic ordering when normalized names are equal. Without a complete tie-breaker, row order among equal sort keys is not guaranteed. Sorting by a function can also require extra work and may benefit from a matching expression index for large datasets.
Expressions in UPDATE and INSERT
UPDATE inventory
SET quantity_on_hand = quantity_on_hand - :sold_quantity,
updated_at = CURRENT_TIMESTAMP
WHERE product_id = :product_id;
INSERT INTO invoice_summary (invoice_id, total_amount)
SELECT invoice_id, SUM(quantity * unit_price)
FROM invoice_items
GROUP BY invoice_id;
Expressions in SELECT generate result values without changing stored data. Expressions in UPDATE assign new stored values. Transaction and concurrency behavior must be considered when an update depends on the current value.
Scalar Expressions
A scalar expression returns one value for the current row or context. Literals, column references, arithmetic calculations, scalar functions, and qualifying scalar subqueries are examples.
An aggregate expression returns one value per group or query, while a window expression returns a value per row based on a related set. The word scalar describes result cardinality in context, not simplicity.
Scalar Subqueries
A scalar subquery must return at most one row and one column:
SELECT c.customer_id,
(SELECT MAX(o.ordered_at)
FROM orders AS o
WHERE o.customer_id = c.customer_id) AS latest_order_at
FROM customers AS c;
If the subquery returns several rows, the database raises an error. If it returns no row, the scalar result is generally NULL. A correlated scalar subquery can be convenient but should be compared with a join or pre-aggregation for large workloads.
EXISTS Is Not a Scalar Value Subquery
EXISTS asks whether a subquery returns any row and produces a Boolean condition:
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
)
It does not require the subquery to return one value. Use it when existence is the requirement rather than extracting a scalar result.
Expression Data Types
Every expression has a data type determined from its components and database conversion rules. That type affects precision, comparison, sorting, formatting, storage, and application mapping.
A CASE expression must resolve its branch results to a compatible type. Mixing numbers and text can trigger implicit conversion or an error. Arithmetic involving decimal and integer operands can promote or narrow precision according to product rules. Inspect the result type instead of assuming it.
Division by Zero and NULLIF
A denominator of zero can raise an error. NULLIF can turn zero into NULL so the result becomes unknown rather than failing:
revenue_amount / NULLIF(order_count, 0)
This is appropriate only when a null result correctly means the ratio is undefined. Do not replace every division error with zero; that would invent a value.
Reusing Complex Expressions
Repeating business logic in several clauses increases maintenance risk. A CTE or derived table can calculate the expression once and expose a named result:
WITH priced_items AS (
SELECT order_id,
quantity * unit_price
- COALESCE(discount_amount, 0) AS net_amount
FROM order_items
)
SELECT order_id, net_amount
FROM priced_items
WHERE net_amount > 100
ORDER BY net_amount DESC;
This also avoids relying on a select-list alias in a logically earlier WHERE clause.
SARGability and Indexed Columns
A predicate is often called sargable when the database can use an index search efficiently. Wrapping an indexed column in a function can make a normal index less useful:
-- Potentially less index-friendly
WHERE LOWER(email_address) = LOWER(:email)
-- Range predicate aligned with the raw timestamp column
WHERE created_at >= :start_time
AND created_at < :end_time
Expression, functional, computed-column, or generated-column indexes can support normalized expressions where the product provides them. Verify with execution plans and realistic data.
Expressions in CHECK Constraints
CONSTRAINT ck_products_price_nonnegative
CHECK (unit_price >= 0)
CONSTRAINT ck_events_time_order
CHECK (ended_at IS NULL OR ended_at >= started_at)
A check expression enforces a row-level condition during writes. SQL's unknown behavior matters: many databases accept a row when a check condition is true or unknown, so columns requiring values also need NOT NULL.
Default Expressions
A default expression supplies a value when an insert omits a column and the schema permits the default:
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
A default does not normally replace an explicitly supplied NULL, and it does not retroactively populate existing rows unless a migration does so. Products differ in which functions, sequences, and references are allowed. Defaults are useful for creation timestamps, identifiers, and stable initial states, but they should not conceal required application decisions.
Generated and Computed Columns
Several databases support columns whose values are derived from an expression, using product-specific generated or computed-column syntax.
line_total AS (quantity * unit_price)
Some products can store the generated value; others calculate it when read. Restrictions may apply to data types, functions, determinism, indexing, and references to other columns. Generated columns centralize reusable row-level logic but should not hide volatile business policy.
Expressions in Views
A view can give a stable name to frequently reused expressions and present a consistent analytical interface:
CREATE VIEW order_item_totals AS
SELECT order_id,
product_id,
quantity * unit_price
- COALESCE(discount_amount, 0) AS net_amount
FROM order_items;
A regular view stores the query definition rather than independently storing each calculated result. Materialized or indexed-view features have different storage and refresh behavior. Changes to source columns, precision, or business definitions still require versioned review.
Deterministic and Non-Deterministic Expressions
A deterministic expression returns the same result for the same inputs under the same relevant settings. quantity * unit_price is usually deterministic. Current-time and random-number functions are non-deterministic because repeated evaluation can return different results.
CURRENT_TIMESTAMP
RANDOM()
Function names and determinism classification vary by database. Determinism affects generated columns, indexes, materialized results, replication, and test repeatability.
Window Function Expressions
Window functions calculate values across related rows while preserving each input row:
SELECT order_id,
customer_id,
total_amount,
SUM(total_amount) OVER (
PARTITION BY customer_id
) AS customer_total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC, order_id DESC
) AS recency_rank
FROM orders;
The OVER specification defines the window. Unlike GROUP BY, it does not collapse rows.
Logical Processing and Alias Scope
A select-list expression is logically evaluated after WHERE, grouping, and group filtering. Therefore, its alias is usually unavailable in WHERE but may be available in ORDER BY.
An alias is a name for a result expression, not a variable storing a reusable intermediate value. Use a CTE, derived table, lateral construct, or product-supported feature when an expression must be referenced in a later query block.
Expression Portability
Core arithmetic, comparison, CASE, COALESCE, and CAST concepts transfer well, but exact syntax often does not. String concatenation, date arithmetic, regular expressions, Boolean literals, safe conversion, generated columns, identifier quoting, and function names vary among MySQL, PostgreSQL, SQL Server, Oracle, SQLite, and other products.
Write for the actual target database and version. When portability is a requirement, isolate dialect-specific expressions, maintain compatibility tests, and avoid assuming that apparently similar functions share edge-case behavior.
Parameters and Expression Security
Values supplied by users or applications should be bound as parameters rather than concatenated into SQL text.
WHERE customer_id = :customer_id
AND ordered_at >= :start_time
Parameters protect the SQL structure from injection and let the driver send type information. They cannot usually replace identifiers, keywords, operators, or arbitrary expression fragments. Dynamic sorting and filtering should choose from an allowlisted set of known expressions instead of inserting unchecked text.
Real-World Payroll Expression
SELECT employee_id,
base_salary,
COALESCE(bonus_amount, 0) AS bonus_amount,
base_salary
+ COALESCE(bonus_amount, 0)
- COALESCE(deduction_amount, 0) AS net_pay
FROM payroll;
The expression makes the missing-value policy explicit. A payroll system must also define currency, period, precision, rounding, taxable components, and whether missing amounts truly mean zero.
Real-World Inventory Expression
CASE
WHEN quantity_on_hand <= 0 THEN 'OUT_OF_STOCK'
WHEN quantity_on_hand <= reorder_level THEN 'REORDER'
ELSE 'AVAILABLE'
END
The conditions are ordered from the most urgent state to the broader state. If negative quantity has a separate business meaning, add a more specific branch first.
Real-World Date Classification
CASE
WHEN due_at IS NULL THEN 'NO_DUE_DATE'
WHEN completed_at IS NOT NULL THEN 'COMPLETED'
WHEN due_at < CURRENT_TIMESTAMP THEN 'OVERDUE'
ELSE 'OPEN'
END
This expression handles missing values before comparing time and gives completion precedence over overdue status. Time zone and timestamp type must match the business requirement.
Expression Evaluation Trees
A complex expression can be understood as a tree. Leaves are literals or column values. Inner nodes are operators and function calls. Parentheses define explicit subtrees.
ROUND(
(quantity * unit_price)
- COALESCE(discount_amount, 0),
2
)
First the multiplication and fallback value are evaluated, then subtraction occurs, and finally rounding produces the displayed result. Breaking the tree into named CTE columns is useful during debugging.
Debugging Expressions
Test each subexpression independently and display intermediate values. Verify input types, NULL rows, boundary values, negative values, zero denominators, large values, precision, time zones, and implicit conversions.
SELECT quantity,
unit_price,
discount_amount,
quantity * unit_price AS gross_amount,
COALESCE(discount_amount, 0) AS applied_discount,
quantity * unit_price
- COALESCE(discount_amount, 0) AS net_amount
FROM order_items
WHERE order_id = :test_order_id;
This makes the evaluation path observable instead of testing only the final number.
Testing Expression Behavior
Expression tests should cover representative and adversarial values. For arithmetic, include zero, negative values, maximum supported values, fractional precision, and rounding boundaries. For strings, include empty text, whitespace, Unicode, case variations, wildcard characters, and values longer than expected. For time, include period boundaries, leap days, daylight-saving transitions where relevant, and mixed time zones.
For every nullable input, test both NULL and a real value. For CASE, exercise each branch and the ELSE path. For predicates, verify rows expected to be true, false, and unknown.
Analyzing Expression Performance
Use the database's execution-plan tools to see whether predicates use indexes, whether expressions cause conversions, and where sorting, hashing, or repeated computation occurs. Compare estimated behavior with actual row counts and timing on realistic data.
Do not remove readable expressions based on assumptions alone. A database may fold constants, inline a CTE, simplify predicates, or reuse work. Measure before changing the design, and recheck after schema statistics, indexes, or data distribution change.
Common Expression Mistakes
Frequent errors include confusing an expression with a clause, omitting parentheses, using = NULL, forgetting that null arithmetic returns null, writing string values with identifier quotes, triggering integer division, relying on implicit conversion, and wrapping indexed columns in functions without an index strategy.
Other problems include repeating business logic in many queries, hardcoding unexplained constants, ordering overlapping CASE branches incorrectly, ignoring result types, and treating aliases like procedural variables.
Expression Categories
| Category | Example | Result |
|---|---|---|
| Literal | 100 | Constant value |
| Column | total_amount | Current column value |
| Arithmetic | quantity * unit_price | Numeric value |
| Predicate | total_amount > 100 | True, false, or unknown |
| Conditional | CASE ... END | Selected branch value |
| Aggregate | SUM(total_amount) | One value per group |
| Scalar subquery | (SELECT MAX(...)) | At most one value |
| Window | ROW_NUMBER() OVER (...) | One value per result row |
Formatting Complex Expressions
Place major operators and CASE branches on separate lines, indent nested functions, and use aliases that describe business meaning.
CASE
WHEN order_status = 'PAID' THEN
ROUND(
subtotal_amount
+ COALESCE(tax_amount, 0)
- COALESCE(discount_amount, 0),
2
)
ELSE 0
END AS recognized_revenue
If formatting cannot make the logic understandable, move parts into named expressions, a view, a generated column, or tested application/domain logic.
Practical Review Checklist
- Identify the expected result type and whether the expression is scalar, aggregate, predicate, or windowed.
- Use parentheses to make precedence and business grouping explicit.
- Define how
NULLshould behave instead of replacing it automatically. - Use exact numeric types and deliberate rounding for money.
- Guard only genuinely undefined denominators with
NULLIF. - Prefer explicit conversion when types differ.
- Order overlapping
CASEbranches from specific to broad. - Use half-open timestamp ranges where they fit the requirement.
- Avoid repeating complex business expressions across many queries.
- Check index use when functions wrap search columns.
- Test boundary values, missing values, precision, locale, and time zones.
- Format and alias expressions so intermediate meaning remains visible.
Interview Questions and Answers
What is a SQL expression? It is a combination of values, columns, operators, functions, or subqueries that evaluates to a value or condition.
What is the difference between an expression and a clause? An expression produces a value; a clause is a functional statement section that uses expressions.
What happens when arithmetic includes NULL? The result is normally NULL unless the missing value is explicitly handled.
Is CASE a control-flow statement? In SQL queries, CASE is an expression that returns a value.
What is a scalar subquery? It is a subquery used as an expression that must return no more than one row and one column.
Why can LOWER(column) hurt performance? A normal index on the raw column may not support the transformed search unless the database rewrites it or has a matching expression index.
What is the difference between GROUP BY and a window function? Grouping collapses rows into groups; a window expression calculates across related rows while retaining each row.
Why is explicit CAST useful? It makes the intended type visible and reduces reliance on product-specific implicit conversion.
Key Takeaway
SQL expressions are the value-producing building blocks inside statements. They range from literals and column references to arithmetic, predicates, functions, CASE, casts, aggregates, scalar subqueries, and window calculations.
SQL EXPRESSION =
INPUT VALUES + OPERATORS + FUNCTIONS + EVALUATION RULES
Reliable expressions require deliberate data types, parentheses, NULL semantics, conversion rules, precision, and index awareness. Build complex logic from understandable parts, test intermediate results, and reuse important business calculations through named and governed structures rather than duplicating them.