COALESCE, ALIASES, Using NOT | Learn SQL from Scratch 9 | Kovolff
Channel: prelearn
Date: 2020-10-15

In this video we use some data cleaning methods to create an improved view

COALESCE
To remove NULLs we use the function COALESCE
i.e. COALESCE(“iron”, ‘’) replaces all NULLs with an empty string
COALESCE(CAST(“iron” AS DECIMAL), 0) replaces all NULLs with 0

Dealing with Aliases
When aliasing your fields, the alias name will not be recognized by your WHERE statements, but is recognized by ORDER BY.

To quickly solve this problem, just encase your query within an outer query
SELECT * FROM 
(
 SELECT 
  "product"
  , "country"
  , "brand"
  , "label"
  , ROUND(COALESCE(CAST("kcal" AS DECIMAL), 0), 1) AS "kcals"
  , ROUND(COALESCE(CAST("protein" AS DECIMAL), 0), 1) AS "proteins"
  , ROUND(COALESCE(CAST("carbs" AS DECIMAL), 0), 1) AS "carbohydrates"
  , ROUND(COALESCE(CAST("fat" AS DECIMAL), 0), 1) AS "fats"
  , ROUND(COALESCE(CAST("iron" AS DECIMAL), 0), 1) AS "fe"
  , ROUND(COALESCE(CAST("salt" AS DECIMAL), 0), 1) AS "nacl"
 FROM fundamental_infos
) AS gen_infos

WHERE kcals = 0 AND proteins = 0

NOT
If you can build a WHERE statement of the items you do not want, then use NOT to get the items you require.

SELECT * FROM 
(
 SELECT 
  "product"
  , "country"
  , "brand"
  , "label"
  , ROUND(COALESCE(CAST("kcal" AS DECIMAL), 0), 1) AS "kcals"
  , ROUND(COALESCE(CAST("protein" AS DECIMAL), 0), 1) AS "proteins"
  , ROUND(COALESCE(CAST("carbs" AS DECIMAL), 0), 1) AS "carbohydrates"
  , ROUND(COALESCE(CAST("fat" AS DECIMAL), 0), 1) AS "fats"
  , ROUND(COALESCE(CAST("iron" AS DECIMAL), 0), 1) AS "fe"
  , ROUND(COALESCE(CAST("salt" AS DECIMAL), 0), 1) AS "nacl"
 FROM fundamental_infos
) AS fundamentals

WHERE NOT(kcals = 0 AND proteins = 0 AND carbohydrates = 0 AND fats = 0 AND fe = 0 AND nacl = 0)

#sql #alias #coalesce #not