Scalar value expressions: A scalar data value expressions provides more data manipulation and calculations for individual data values. Here are the types of scalar value expressions in SQL:
CAST expression: CAST expression is used to convert different data types. It is generally used to convert data in a table where a column is defined with incorrect data type and to convert data which is not supported by host language. It is also used to remove differences among data types in two tables.
SELECT NAME, CAST EMPL_NUM AS VARCHAR, HIRING_DATE AS VARCHAR FROM EMPLOYEES
CASE expression: Case expression is similar to if, then, else statement found in many languages. For a CASE expression, first condition is checked, if it’s TRUE then value of case expression is result of first expression. If it’s false, then next expression is checked. If result of second expression is TRUE then value of case expression is result of second expression. If its FALSE, then next expression is checked, and so on.
SELECT OFFICE, CASE WHEN PERKS > 500 THEN ‘M’
WHEN CREDIT_LIMIT > 1000 THEN ‘N’
WHEN CREDIT_LIMIT > 2000 THEN ‘O’
COALESCE expression: COALESCE expression is a special type of CASE expression where a value is checked for not NULL. If it’s not NULL then it becomes value of COALESCE expression.
The below given statement:
SELECT NAME, CASE WHEN (PERKS IS NOT NULL) THEN PERKS
WHEN (BENEFITS IS NOT NULL) THEN BENEFITS
can be written by COALESCE as:
SELECT NAME, COALESCE (PERKS, SALES, 0) FROM EMPLOYEES
NULLIF expression: In a NULLIF expression, first value is compared to second, if they are equal, a NULL value is generated, else value of expression is first value.
SELECT CITY, SUM(SALES) FROM COMPANIES, EMPLOYEES WHERE COMPANY = (NULLIF EMPL_NUM, 0) GROUP BY CITY