Functions

ABS Function:

ABS returns the absolute value. The output is positive even if the input is negative:

  ABS(x) = x if x>=0
ABS(x) = -x if x<0

ABS can be useful for testing values that are "close". For example this query shows each country that has area that is roughly 70 thousand. The value 70000 is the target value, 500 is the "tolerance" so the test ABS(area-70000)<500 style="" border="1">name area Georgia 69700 Ireland 70182

CASE

CASE allows you to return different values under different conditions.

If there no conditions match (and there is not ELSE) then NULL is returned.

  CASE WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE def_value
END

SELECT name, population
 ,CASE WHEN population<1000000>
name
 
population
 
result
 
 
 
Monaco
 
32000
 
small
 
 
 
Liechtenstein
 
34000
 
small
 
 
 
Bahamas
 
321000
 
small
 
 
 
The Maldives
 
338000
 
small
 
 
 
Brunei
 
374000
 
small
 
 
 
Malta
 
397000
 
small
 
 
 
Armenia
 
3000000
 
medium