This article describes how to use the T-SQL Expressions in SQL Server database. We will discuss in detail the three main types of SQL Server expressions namely CASE, COALESCE and NULLIF.

Types of Expressions

There are three main types of SQL Server Expressions as follows:
CASE
COALESCE
NULLIF

What is CASE expression in SQL Server?

The CASE statement in SQL Server returns a specific output of value based on the condition specified by the user. The SQL Server CASE statement has at least one pair of WHEN-THEN statements.

The WHEN statement consists of the condition to be applied to the table and tested, and the THEN statement return the input expression once the WHEN statement returns TRUE. This also has an ELSE statement, but it is optional. The ELSE statement has the activity to be done once any of the WHEN statements don't return TRUE. Finally, the case statement ends with the END keyword.

The syntax for the CASE statement

SELECT column1, column2,
CASE WHEN condition_expression THEN result_expression
ELSE else_result_expression
END AS column_name
FROM Table_name

Example

SELECT name, price,
CASE WHEN price = 30 THEN 'B1'
ELSE 'B2' END AS case_result
FROM books2;

In SQL Server you can use the CASE statement in WHERE clause.

What is COALESCE expression in SQL Server?

COALESCE expression in SQL Server has string manipulations. The method of obtaining another form using the existing data is called String Manipulation.

COALESCE expression will return the data type which has the highest precedence data type. If the entered expressions are nullable, the output will also be nullable.

The syntax for COALESCE statement

COALESCE ( expression [ 1…n ] )

The properties of COALESCE

Expressions should be of the same type.
There can be multiple expressions.
First, check the integer, consisting of a character expression after the integer, which will give an integer as an output.

Examples:

SELECT COALESCE (NULL,' T1', 'T2');
SELECT COALESCE (NULL,100,25,30,50);
SELECT COALESCE (NULL,NULL,22,NULL,NULL);
SELECT COALESCE (NULL,NULL,NULL,NULL,10,'Test');
SELECT COALESCE (NULL,NULL,NULL,NULL,NULL,'Test');

What is the NULLIF expression

NULLIF expression in SQL Server returns a NULL value when the given two parameters are identical, and if it is not identical, it will return the value of the first parameter.

The syntax for NULLIF statement

NULLIF(expression_1, expression_2)

Example

SELECT id,
NULLIF(current_month, previous_month) AS LastLog
FROM logs;