Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
The COALESCE() function
The GREATEST() & LEAST() functions
11. The GREATEST() and the LEAST() with NULL
The NULLIF() function


Okay. But what if a value in the list of arguments in GREATEST() or LEAST() functions is NULL? They omit NULL and return the largest or the least values for those different than NULL. The query looks like this:

  LEAST(market2_price, market3_price) AS smallest_price
FROM product;

For the product with ID of 5 it returns 1.99. This comes from the market2_price column because the market3_price stores NULL; therefore it is ignored.

NULL is returned only if all arguments are NULL.


For the product with ID of 3 show its name and the largest_price in all three shops.