SQL Filtering, Grouping, Aggregates, Sorting and Conditional Logic

SELECT query

A SELECT query declares the rows and expressions required from one or more data sources

WHERE predicate

WHERE retains only rows for which its predicate evaluates TRUE; FALSE and UNKNOWN rows are rejected.

NULL and three-valued logic

NULL represents a missing or inapplicable value, so comparisons can yield UNKNOWN and must be tested with IS NULL rather than equality.

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.

ORDER BY

ORDER BY sorts a query result by one or more expressions with specified ascending or descending direction.

aggregate function

An aggregate function reduces a set of rows to a value such as COUNT, SUM, AVG, MIN or MAX.

GROUP BY

GROUP BY forms one result group for each distinct grouping-key combination so aggregate functions can summarize rows within each group.

GROUP BY partitions filtered rows into groups with equal grouping expressions:

SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS mean_salary FROM employee GROUP BY department_id;

The result contains one row per department_id group. NULL grouping values normally form one group together.

Grouping does not inherently sort output. Use ORDER BY when order matters.

HAVING

HAVING filters groups after grouping and aggregation, whereas WHERE filters input rows before groups are formed.

CASE expression

A SQL expression that evaluates conditions in order and returns the result belonging to the first matching branch.

set operation

UNION, INTERSECT and EXCEPT combine compatible query results according to set or multiset rules.

query evaluation order

The logical processing sequence resolves sources and joins, filters rows, groups them, filters groups, projects values and finally orders or limits output.

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.

Logical Processing Order

For a grouped query, a useful conceptual order is:

FROM and JOIN form source rows;
WHERE filters those rows;
GROUP BY partitions the survivors;
aggregate functions summarize each group;
HAVING filters groups;
SELECT produces output expressions;
DISTINCT removes duplicate result rows;
ORDER BY orders them;
LIMIT restricts output.

This order describes query meaning, not a mandatory physical algorithm. The optimizer may push safe predicates, choose indexes or reorder joins while preserving semantics.

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.

HAVING After Grouping

HAVING filters formed groups:

SELECT department_id, COUNT() AS active_count, AVG(salary) AS mean_salary FROM employee WHERE status = 'ACTIVE' GROUP BY department_id HAVING COUNT() >= 5;

Only groups containing at least five active employees remain. WHERE cannot directly test COUNT(*) because aggregation has not occurred at that logical stage.

MySQL may allow selected aliases in HAVING:

HAVING active_count >= 5

Using the aggregate expression directly is more portable.

Conditional Aggregation

CASE inside an aggregate computes several metrics in one grouping pass:

SELECT department_id, COUNT(*) AS total_count, SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_count, SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive_count FROM employee GROUP BY department_id;

The CASE returns 1 or 0 for each row and SUM totals the selected category.

For conditional amounts:

SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END)

Decide whether an unmatched group should yield zero or NULL and choose ELSE accordingly.

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.

WHERE Before Grouping

WHERE filters detail rows before groups are formed:

SELECT department_id, COUNT(*) AS active_count, AVG(salary) AS mean_salary FROM employee WHERE status = 'ACTIVE' GROUP BY department_id;

The population is active employees. Inactive employees do not participate in counts or averages.

Use WHERE for row-level conditions that do not require aggregates. Early filtering also reduces the rows that later stages must process.

Building Trustworthy Aggregations

Choose COUNT(*) for rows and COUNT(column) for known values. Use stable identifiers as grouping keys. Put detail predicates in WHERE and group predicates in HAVING. Make NULL policy explicit with COALESCE only where the domain supports a replacement.

Most serious aggregation errors occur before the aggregate function: an unintended join multiplies rows, a predicate selects the wrong population or grouping merges distinct entities. Reason from row grain through each logical stage and the final summary becomes explainable and testable.

Grouped Select Lists

After grouping, each output row represents an entire group. A selected expression must normally be:

a grouping expression;
an aggregate over the group; or
functionally dependent on grouped keys where the database can establish that dependency.

Selecting an arbitrary ungrouped employee_name has no single defined value for a department group. MySQL's ONLY_FULL_GROUP_BY mode rejects nondeterministic grouped queries and should remain enabled.

If the desired value is “any name,” state why and use an intentional operation. Usually the query question has not been defined correctly.

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.

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.

Grouping by Several Columns

Several expressions define groups by their complete combination:

SELECT department_id, job_code, COUNT(*) FROM employee GROUP BY department_id, job_code;

An employee belongs to the group identified by both values. Grouping by department alone answers a different question.

Use stable identifiers. Grouping only by a nonunique department name can merge different departments that share a label. Group by department_id and include the name where it is functionally determined and accepted by strict SQL rules.

GROUP BY with Time

Reports often group by temporal periods:

SELECT YEAR(ordered_at) AS order_year, MONTH(ordered_at) AS order_month, SUM(total_amount) FROM order_header GROUP BY YEAR(ordered_at), MONTH(ordered_at);

Grouping only by month number combines January across all years. Include every period component needed for identity.

For filtering, use a range on the original datetime column so an index can be used. Functions in GROUP BY may still require computation or a suitable generated/functional index.

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.

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.

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

WHERE and HAVING Are Not Interchangeable

Consider:

WHERE salary >= 50000

This removes lower salaries before calculating an average.

HAVING AVG(salary) >= 50000

This calculates the average over all eligible salaries and then removes groups with a lower average.

Moving a condition between WHERE and HAVING can change both group membership and aggregate values. Put each condition at the stage matching its meaning.

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.

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.

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.

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.

From Detail Rows to Summary Values

An aggregate function consumes a collection of rows and produces one summary value. Common aggregates answer questions such as how many orders exist, what their total value is and what the highest salary is.

SELECT COUNT(*) AS order_count, SUM(total_amount) AS total_sales, AVG(total_amount) AS mean_order, MIN(total_amount) AS smallest_order, MAX(total_amount) AS largest_order FROM order_header;

Without GROUP BY, the filtered input is treated as one group and the query normally returns one summary row.

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.

ROLLUP

MySQL supports WITH ROLLUP to add hierarchical subtotal and grand-total rows:

SELECT region, category, SUM(amount) FROM sale GROUP BY region, category WITH ROLLUP;

Rollup-generated NULL-like grouping output must be distinguished from actual NULL grouping values using the product's grouping facilities where available. Applications should label subtotal levels explicitly rather than assuming every NULL means “total.”

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.

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.

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.

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.

SUM and AVG

SUM adds non-NULL expression values. AVG computes their arithmetic mean, also ignoring NULL:

SELECT SUM(salary), AVG(salary) FROM employee;

AVG(salary) equals SUM(salary) divided by COUNT(salary), not COUNT(*), when salary can be NULL. Treating an absent salary as zero requires an explicit domain decision:

AVG(COALESCE(salary, 0))

This changes the population and result. NULL should not be replaced merely to avoid understanding it.

Numeric type and scale affect aggregate precision. Money should generally use exact DECIMAL columns and appropriate result handling.

Pre-Aggregation

Aggregate a many-side relation before joining when the parent result requires one row per parent:

SELECT o.order_id, o.customer_id, COALESCE(l.line_total, 0) AS line_total FROM order_header AS o LEFT JOIN ( SELECT order_id, SUM(quantity * unit_price) AS line_total FROM order_line GROUP BY order_id ) AS l ON l.order_id = o.order_id;

The derived table has one row per order_id, so joining it to an order no longer multiplies parent rows.

Empty Input

For an ungrouped aggregate query over no input rows, COUNT returns 0. SUM, AVG, MIN and MAX ordinarily return NULL because no non-NULL value exists from which to derive a result.

SELECT COALESCE(SUM(amount), 0) FROM payment WHERE customer_id = 999;

COALESCE is suitable when the domain defines the total of no payments as zero. Preserve NULL when absence should remain distinguishable from a calculated zero.

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.

Checking a Summary Query

Validate a report systematically:

state what one source row represents;
state the population selected by WHERE;
inspect join cardinalities and sample joined rows;
state what one output group represents;
verify NULL treatment for every aggregate;
test empty groups, unmatched parents and duplicate values;
compare totals against a simpler independent query.

Do not add DISTINCT until the reason for duplicates is understood.

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.

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.

Ordering Grouped Results

ORDER BY can use aggregates or their aliases:

SELECT department_id, AVG(salary) AS mean_salary FROM employee GROUP BY department_id ORDER BY mean_salary DESC, department_id;

This requests highest average first and uses department_id as a deterministic tie-breaker.

GROUP BY does not promise the same order even when a current execution happens to emit groups that way.

COUNT Variants

COUNT(*) counts input rows regardless of NULL values:

SELECT COUNT(*) FROM employee;

COUNT(expression) counts rows for which the expression is non-NULL:

SELECT COUNT(salary) FROM employee;

The difference counts rows with NULL salary:

COUNT(*) - COUNT(salary)

COUNT(DISTINCT salary) counts distinct non-NULL salary values. It does not count employees and should not be used when the question asks for people rather than salary levels.

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.

Grain

The grain of a relation or result states what one row represents. ORDER_HEADER has one row per order. ORDER_LINE has one row per order line. A join of them has one row per matching order-line relationship.

Before writing aggregates, state the intended result grain:

one row for the whole system;
one row per department;
one row per customer per month;
one row per product category.

Then identify the input grain. Many inflated reports come from grouping a result whose row meaning was never stated.

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.

Aggregation After Joins

Joins form the input before aggregation. In a one-to-many join, a parent row appears once per matching child.

Suppose ORDER_HEADER.total_amount is 1000 and the order has four lines. Joining header to lines produces four rows containing the same 1000. SUM(order_header.total_amount) then produces 4000.

This is not an aggregate defect. The query supplied a multiplied population. Aggregate each side at its natural grain before joining, sum child line amounts instead or query parent totals without the child join.

Aggregate Types and Precision

Aggregate result types depend on input type and DBMS rules. SUM over integers may widen, while AVG can return a decimal or approximate type depending on its argument.

Applications should inspect actual result metadata and choose compatible host-language types. Avoid converting a precise database decimal to binary floating point when exact financial behavior is required.

Large counts may exceed a small application integer even when individual table keys do not.

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.

Outer Joins and Counts

A left join keeps parents without children by supplying NULL child columns:

SELECT d.department_id, COUNT(e.employee_id) AS employee_count FROM department AS d LEFT JOIN employee AS e ON e.department_id = d.department_id GROUP BY d.department_id;

COUNT(*) would count the preserved department row even when no employee matched, producing 1. COUNT(e.employee_id) counts only non-NULL matched employee identifiers and correctly produces 0.

Distinct Aggregation

DISTINCT can apply inside an aggregate:

COUNT(DISTINCT customer_id)

This counts different non-NULL customers rather than orders.

SUM(DISTINCT amount)

adds each distinct amount once, which is rarely a correct way to repair duplicate rows from a join. Two legitimate payments may have the same amount. Fix join cardinality instead of discarding equal values.

MIN and MAX

MIN and MAX return the smallest and largest non-NULL values according to type and collation:

SELECT MIN(hired_on), MAX(hired_on) FROM employee;

They work with dates and text as well as numbers. A maximum name is the greatest name under the selected collation, not the row containing the greatest salary.

To retrieve a complete row associated with an extreme value, use an ordered query, subquery, join or window-function design. Selecting MAX(salary) and an unrelated employee_name does not establish that the name belongs to the maximum salary.