Home SQL Query for NULL and Max Value – What Works?

Databases

SQL Query for NULL and Max Value – What Works?

Need SQL to return only rows with MAX or NULL values? Learn the best portable SQL approach for querying nullable columns.

byDev Solutions

January 16, 2026

SQL query terminal showing how to handle NULLs with MAX function using COALESCE and CASE WHEN, highlighting best practices for developers

🧮 SQL aggregate functions like MAX() ignore NULLs unless you specifically deal with them.
⚠️ Using col = NULL always fails. You must use IS NULL to handle NULLs correctly.
⚖️ How SQL handles nulls changes a bit between MySQL, PostgreSQL, SQL Server, and SQLite.
🧠 CASE and COALESCE give clear, strong ways to include NULLs in SQL conditional queries.
🚀 Good indexing and fallback logic greatly improve how complex SQL NULL queries perform.

When you work with raw SQL data, dealing with NULL values and numeric queries like MAX() can get complicated fast. When trying to make code work on different database systems, developers often find behavior is not the same and run into tricky problems. This section will show how to write a good SQL conditional query. It will get the highest value and also deal with NULLs. This makes sure your logic works on PostgreSQL, MySQL, SQL Server, SQLite, and other systems.

Understanding SQL NULLs in Aggregate Contexts

In SQL, a NULL value means a value is missing or unknown. This is not like zero or an empty string. This difference matters a lot when using aggregate functions like MAX(), SUM(), or AVG(). These functions purposely ignore NULLs during math.

Look at this query for a table called grades:

SELECT MAX(score) FROM grades;

If the score column has [95, NULL, 80], the query gives 95. The NULL does not change the result. MAX() skips it entirely. This behavior follows ANSI SQL rules. It makes sure aggregates only show values that are known.

But if the whole column only has [NULL, NULL], then MAX(score) will give back NULL. This shows that no known values exist. This can cause surprising results in real apps that do not plan for this situation.

Knowing how NULLs are treated is very important for anyone writing SQL conditional queries. If you do not check for them correctly, SQL NULL query conditions can fail without a warning. Or they can give wrong results, especially in very important logic.

Defining the Problem: "Select MAX If Present, Otherwise Include NULL"

Developers often deal with this basic problem:

“How can I pick the row(s) with the highest number, and if no such number exists, still make sure rows with NULLs are not missed?”

Simply using MAX() is not enough. Here is what can go wrong:

MAX() ignores NULLs—if every value is NULL, the function returns NULL.
Comparisons like WHERE col = MAX(col) exclude NULLs because NULL = NULL is never true.
WHERE col = NULL will always fail due to SQL's three-valued logic.

We want to write strong, portable SQL conditional queries. These queries should always pick the highest value that is there. Or, if only NULLs exist, they should handle it well.

SQL Portability: Why It Matters

SQL engines are slightly different in how they handle NULLs and sort things. Let's look at how major RDBMS deal with these cases:

MySQL defaults to ignoring NULLs and places them first in an ascending sort. ORDER BY NULL is often disallowed or misunderstood.
PostgreSQL sorts NULLs last in ascending sorts and first in descending sorts by default. However, it also supports NULLS FIRST/NULLS LAST explicitly.
SQL Server orders NULLs as the lowest possible value by default (in ascending order), unless explicitly overridden.
SQLite treats NULLs as larger than any numeric value in DESC ordering unless specified.

When you want your SQL NULL query to act the same on different systems, writing ANSI-compliant SQL conditional queries is very important. ANSI SQL is the language standard that most RDBMS try to follow. And following it makes your code easier to move and keep up-to-date.

That means avoiding special functions or odd syntax. Instead, use things like CASE, COALESCE, and subqueries whenever you can.

Core Solution: Conditional SQL to Capture Max or NULL

Here is a short query pattern that works well on many systems:

SELECT * 
FROM your_table
WHERE your_column = (SELECT MAX(your_column) FROM your_table)
   OR your_column IS NULL;

How It Works:

The inner query calculates the MAX of the column, excluding any NULLs.
The outer query selects rows where your_column matches that MAX value.
It also includes a fallback clause: OR your_column IS NULL.

If there are numbers, we will get the highest one(s). If everything is NULL, the outer IS NULL makes sure we get those entries. This logic gives a good balance. It is clean, easy to see, and follows standards.

Handling Edge Cases

1. Multiple Rows With the Same Max

MAX() may not be unique. For example, if three users scored 100, the query returns all such rows unless further restricted.

Use ROW_NUMBER() or LIMIT to get fewer results:

SELECT * FROM (
  SELECT *, 
         ROW_NUMBER() OVER (ORDER BY your_column DESC NULLS LAST) AS rn
  FROM your_table
) t
WHERE rn = 1;

This gets just one row with the highest value. In PostgreSQL, NULLS LAST makes sure sorting puts NULLs at the bottom. This means the highest number is first.

Different DBMS syntax variants include:

SQL Server: Replace with TOP 1 and an ORDER BY.
Oracle: Use FETCH FIRST 1 ROWS ONLY.

2. Dataset Has Only NULL Entries

When all rows are NULL, MAX() returns NULL. The original condition col = (SELECT MAX(...)) fails without a warning because col = NULL is not valid.

This is where OR col IS NULL is very useful. It clearly gets NULL rows when only NULLs are left to show.

Enhancing With COALESCE for Fallback Logic

Sometimes, you need to make sure the SQL max value uses a placeholder when no real number is there. Use COALESCE():

SELECT * 
FROM your_table
WHERE COALESCE(your_column, -1) = (
  SELECT MAX(COALESCE(your_column, -1)) FROM your_table
);

Caveat:

This pattern works only when the fallback value (e.g., -1) can't possibly occur in your dataset.

Pros:

Simple to write.
Makes sure the query has a predictable result.

Cons:

Makes things unclear if {fallback} can be a real value.
Could fail without warning, leading to wrong rows matched.

✅ Use this in places where rules are very strict and your column has clear rules.

Using CASE WHEN for Complex Logic

If your query logic becomes more complex or less direct, using a CASE expression makes it easier to read and control:

SELECT *
FROM your_table
WHERE CASE 
        WHEN your_column IS NULL THEN TRUE
        WHEN your_column = (SELECT MAX(your_column) FROM your_table) THEN TRUE
        ELSE FALSE
      END;

Why this works well:

Works fully on all platforms.
Easy to extend. For example, add a status = 'active' check.
Makes logic easy for people to read, which is very helpful when reviewing code.

Alternatives: ORDER BY and LIMIT

Sometimes, a query that aims for speed is the better answer:

SELECT *
FROM your_table
ORDER BY your_column DESC NULLS LAST
LIMIT 1;

Efficient and direct, but not perfect.

Issues to consider:

If every row is NULL, you get NULL but maybe not the expected rows.
Behavior of NULLS LAST varies unless explicitly coded (PostgreSQL supports it natively).
Some DBMS require different methods: SQL Server uses TOP, Oracle prefers FETCH FIRST.

When to use it: This method works best when you are making pagination, dashboards, or summaries. Here, speed is more important than small details of exactness.

Performance Consideration: Indexing With NULLs

Indexing strategies greatly change how quickly your SQL NULL query runs.

Key behaviors per RDBMS:

PostgreSQL includes NULLs in B-tree indexes by default.

SQL Server supports filtered indexes. For example:

CREATE INDEX idx_non_null ON your_table(your_column) WHERE your_column IS NOT NULL;

MySQL includes NULLs in BTREE indexes and can optimize NULL checks depending on engine (InnoDB vs MyISAM).

Strategy Guidelines:

Check if you often query NULLs. If yes, think about partial indexes or filtering.
To make MAX() faster, make sure your_column has an index.
Indexed NULLs let your SQL conditional query work well with millions of rows.

DOs and DON'Ts When Using NULL and Aggregates

Here is a useful list for good ways of doing things:

✅ DO:

Use IS NULL for filtering NULLs properly.
Test with datasets containing mixed NULL and numeric rows.
Use CASE, ROW_NUMBER(), or COALESCE for full control.

❌ DON'T:

Rely on col = NULL. It will never work as expected.
Assume NULL behaves like zero. It doesn't.
Ignore the impact of sorting NULLs incorrectly in ORDER BY.

Common Developer Mistakes

Mistakes while handling SQL NULL queries happen even to skilled engineers:

Using WHERE col = MAX(col) without a plan for NULLs.
Thinking NULL = 0 in aggregates.
Getting results wrong because they do not understand ternary logic (TRUE, FALSE, and UNKNOWN).
Writing code for one vendor that breaks when moving to another system.
Ignoring how string columns find "max" differently than number columns.

Debugging and Inspection Tips

If you are not sure how your data acts, use this checking query:

SELECT 
  COUNT(*) AS total_rows,
  COUNT(your_column) AS not_null_rows,
  MAX(your_column) AS max_value
FROM your_table;

Why this helps:

Gives you a starting point for how NULLs are spread.
Shows if MAX() returned NULL because all values are NULL or for another reason.

For full testing:

Try testing your table with extreme data: all NULLs, no NULLs, and mixed values.
Use automated tests with checks in systems like dbt, SQLTest, or scripts that work with CI/CD pipelines.

Summary Table: Comparison of Query Techniques

Technique	Best Use Case	Portability	Benefits	Drawbacks
`col = MAX(col) OR col IS NULL`	Standard queries with NULL consideration	High	Simple, easy to see	May return multiple rows
`COALESCE(your_column, -1)`	Need a clear fallback value	Medium	Fast, compact	Risky unless fallback is unique
`CASE WHEN` logic	Multi-condition, human-readable logic	High	Very flexible	Verbose
`ORDER BY your_column DESC NULLS LAST`	Top-row extractions and dashboards	Medium	Effective performance	Needs explicit NULL handling
`ROW_NUMBER()` ranking	Return top 1 cleanly across ties	High	Best for predictable result	Requires subquery

Developer Takeaways & Final Checklist

✅ Understand your data, especially how NULLs are spread and what they mean.
✅ Test your SQL conditional query with tricky cases and on different RDBMS systems.
✅ Use standard SQL instead of special fixes, unless performance needs a different way.
✅ Check your performance. Bad indexing makes aggregates slow.
✅ Make your queries easy to debug and easy for future developers to read.

A smart way to handle SQL max value queries and NULLs brings benefits in strength, ability to grow, and long-term upkeep. Whether you are building reports, data flows, or very important business programs, correctly handling aggregates and nulls is very important.