MIN

Hook: MIN is the SQL version of asking, “What is the smallest ticket in the pile?” — and interviewers love it because the tiny details around NULL and grouping reveal whether you really understand aggregates.

Question: What does MIN do in SQL?

Answer: MIN is an aggregate function that returns the smallest non-NULL value from a column or expression. It works with numbers, dates, and other comparable types, and it ignores NULL values. If there are no matching rows, or every value is NULL, the result is NULL.

Interview-Ready Answer: I use MIN when I want the lowest non-NULL value in a result set, like the cheapest price or earliest date. It’s an aggregate, so it can work on the whole table or one value per group with GROUP BY. One important detail is that MIN ignores NULLs, and if there are no qualifying rows, it returns NULL rather than zero or an error.

What MIN is

Detailed Explanation: Think of MIN as a scanner that walks through values and keeps the smallest one it has seen so far. In SQL, an aggregate is a function that reduces many rows into one value, such as MIN, MAX, SUM, or AVG.

How it works under the hood

1. The database reads the rows that match your FROM and WHERE clauses.
2. For each row, it evaluates the target expression, such as salary or order_date.
3. If the value is NULL, it is skipped.
4. The engine compares the remaining values using the column’s sort rules, called a collation for text or the type’s natural order for numbers and dates.
5. The smallest value found becomes the final result.
6. If you add GROUP BY, the database repeats the same process separately for each group and returns one result per group.

When to use it

• Find the lowest price, earliest signup date, smallest balance, or minimum score.
• Find the minimum per category, such as lowest salary per department.
• Use it in HAVING to keep only groups that meet a threshold, such as departments whose minimum salary is above 50,000.

Why it is different from sorting

Important nuance: many optimizers can make MIN very fast with a B-tree index on the column, because they may jump straight to the first relevant index entry instead of scanning every row. Without a helpful index or with extra filters, assume a full scan and linear work.

Edge cases interviewers love

• NULL handling: MIN ignores NULLs. If all rows are NULL, the result is NULL.
• Empty result sets: If your WHERE clause matches no rows, the aggregate still returns one row with NULL.
• Text values: For strings, the minimum depends on the database’s text ordering rules, which may be case-sensitive or case-insensitive depending on collation.
• Grouped queries: MIN gives one result per group, not one result per table.

Memory Hook: “MIN is the smallest apple in the basket; NULL apples don’t count, and an empty basket gives you no apple at all.”

Real-World Example: In a checkout service, product managers often want the lowest price ever seen for each item to detect accidental pricing bugs. A query with MIN(price) can quickly show the cheapest recorded price per SKU, which helps catch a bad import before customers see it.

What goes wrong when someone misunderstands MIN? A developer assumes NULL means “zero” and writes logic that treats “no data” as “free.” In production, the dashboard suddenly reports a minimum price of 0 or a missing minimum is displayed as a real value, triggering false alerts, confusing logs, and support tickets from merchants who think their catalog was corrupted.

Symptom-wise, you might see query results with blank minimums, mismatched totals between reports, or logs showing that a filtered dataset had no rows while the application displayed a numeric default. The fix is to remember that MIN returns NULL when there is nothing non-NULL to compare, and the application should handle that explicitly.

Follow-up & Tricky Questions:

• Follow-up: How is MIN different from ORDER BY ... LIMIT 1? MIN returns only the smallest value and is an aggregate; ORDER BY ... LIMIT 1 returns the smallest row after sorting or a top-N optimization. If you need the whole row tied to the minimum value, sorting or a window function is often more appropriate.
• Follow-up: Does MIN ignore NULL? Yes, it skips NULL values entirely. If every value is NULL or the filtered set is empty, the result is NULL.
• Follow-up: Can MIN be used with GROUP BY? Yes, and that is one of its most common uses. It gives one minimum per group, such as the lowest salary per department.
• Follow-up: Can I use MIN in HAVING? Yes. HAVING filters groups after aggregation, so you can write conditions like HAVING MIN(salary) > 50000.
• Follow-up: Does MIN work on text? Often yes, but the result depends on the database’s text ordering rules and collation. That means alphabetical order is not always identical across systems.
• Tricky: If a table has no rows, does MIN return 0? No. Aggregate functions with no matching rows return NULL, not zero.
• Tricky: If the smallest value is NULL, does MIN return NULL? No. MIN skips NULLs, so it returns the smallest non-NULL value if one exists.
• Tricky: Is MIN always slower than ORDER BY ... LIMIT 1? Not always. A good index can make MIN extremely fast, and some optimizers can answer it without scanning the whole table.

Common Mistakes:

• Thinking NULL counts as the smallest value. Correction: MIN ignores NULL; it only compares real values.
• Using a default like 0 for “no data.” Correction: an empty result set returns NULL, so handle that explicitly in SQL or application code.
• Forgetting GROUP BY changes the meaning. Correction: without grouping, you get one minimum for the whole filtered set; with grouping, you get one per group.
• Assuming text minimum is universal. Correction: string ordering depends on collation, so results can differ by database or locale.

Memory Hook: “Find the smallest, skip the blanks, and remember that empty means NULL.”

Cheat Sheet:

• MIN(expr) returns the smallest non-NULL value.
• Works on the whole result set or per group with GROUP BY.
• Ignores NULL; all-NULL or empty input returns NULL.
• Usually linear time, but indexes can make it much faster.
• ORDER BY ... LIMIT 1 is for getting the whole row, not just the value.

Practice Tasks:

• Write a query that finds the earliest created_at date in a table.
• Find the minimum price per category, then filter to categories whose minimum price is above 10 using HAVING.
• Insert rows with only NULL values and verify that MIN returns NULL.