I'm working with a MySQL database and have a table named orders. This table includes columns such as order_number and customer_number. I need to retrieve the maximum customer_number from the orders table.
To achieve this, I'm using the following SQL query:
SELECT MAX(customer_number) AS customer_number
FROM orders;
This query works perfectly when the orders table contains data, returning the highest customer_number available. However, when the orders table is empty (contains no rows), the query returns a result set with a single row and a NULL value for customer_number.
Example Output (when table is empty):
| customer_number |
| --------------- |
| null |
In my application logic, I would prefer to receive an empty result set (i.e., no rows returned at all) when the orders table is empty, instead of a single row with a NULL value. This would more clearly signal that there are no customer numbers available because there are no orders.
Is there a way in MySQL to modify my query or use a different approach so that it returns an empty result set when the orders table is empty, rather than returning a single row with a NULL value from the MAX() function?
Use a HAVING condition to filter the result set:
SELECT MAX(customer_number) AS customer_number
FROM orders
HAVING COUNT(*) > 0;
But normally you should expect an aggregated query without GROUP BY to return a single row. When processing the result, is it really eaier to check for no row than checking for the row contents being NULL?