Oracle select max id from table returns null value

Question

I have the query:

SELECT MAX(prod_id) FROM products;

It returns the maximum value if there are records. But, if I truncate table and run the same query I am unable to get the max id.

Answer 1

In case you want to query a table's column and suspect that the max function may return null, then you can return 0 in case null is encountered

SELECT NVL(MAX(P.PROD_ID), 0) AS MAX_VAL
 FROM PRODUCTS P

This will return at least 0 , if no value is encountered for the column that you mention ()

Answer 2

Yes, by truncating the table you have removed all data in it, with no need for a commit. Therefore there is no data in the table and the max of nothing is nothing.