Menu
compare
SELECT distinct u_id,timestamp as time
FROM my_table;
and
SELECT distinct u_id,max(timestamp) as time
FROM my_table;
When my table has no rows at all (or if I add a where clause that matches no rows):
The former returns an empty results set (which is what I expect) while the later returns a single row that has null as the value for both its fields.
Can someone please explain to me why does the second one acts as it does?
811
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Query: SELECT * FROM Student WHERE Name IS NOT NULL AND Department IS NOT NULL AND Roll_No IS NOT NULL; To exclude the null values from all the columns we used AND operator.
One way of avoiding returning null is using the Null Object pattern. Basically you return a special case object that implements the expected interface. Instead of returning null you can implement some kind of default behavior for the object. Returning a null object can be considered as returning a neutral value.
SQL's coalesce turns a null value into another value. The example returns the result of the expression, unless it is null , then it returns zero ( 0 ). Coalesce takes an arbitrary number of arguments and returns the first not null value or null if all arguments are null .
Observing "union all" is dropping records with all null/empty fields.
MySQL documentation says
MAX() returns NULL if there were no matching rows.
And if you have no data then it just returns both values as NULL.
If you want the second query return the empty resultset too, then you must filter out the NULL values for example with HAVING clause that you can use with aggregate functions:
SELECT DISTINCT u_id, MAX(timestamp) as time FROM my_table GROUP BY u_id HAVING time IS NOT NULL;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
