T-SQL query Advanced: A column based logical expression

Introduction

T-SQL is not just a language for querying a database, or a language that can manipulate data. Column-based case expressions are one of them, unlike other query statements that can be substituted for each other (for example, queries implemented with subqueries can also be implemented by joins), and case expressions are largely irreplaceable in the control of columns based logic. The case expression is explained in detail in the following article.

Brief introduction

A logical expression based on a column is actually a case expression. Can be used after Select,update,delete,set and in,where,order by and having clauses. Because this is a T-SQL query, it's only about the use of case expressions in the SELECT clause and the ORDER BY clause.

The case expression implements functionality similar to the IF in the programming language ... THEN ... else logic. Only case expressions in T-SQL do not control the flow of T-SQL programs, but are used as columns based logic.

A simple case expression is as follows:

I already know the name of the employee ID, I want to get the employee ID, and I show the employee ID as the name, and the employee ID I don't know shows Unknow:

Selecttop 4 EmployeeID when
	1 THEN ' Careyson ' when
	2 THEN ' Jack ' when
	3 THEN ' Tom '
	ELSE ' unknow '
	Endas Namelist,employeeid from
  [adventureworks].[ HumanResources]. [Employee]
  by EmployeeID

The results appear as follows:

In the code above, the case followed by the selected column name, followed by when the value obtained is EmployeeID this column, then after the value is corresponding to the previous when the column, the actual results shown in the value.

The actual case expression can be divided into two types:

Case Simple Expression: Compares an expression with a simple set of expressions to determine the result.

case search expression searched Expression: Computes a set of Boolean expressions to determine the result.

These two case expressions are described below.

Case simple expression (case simply Expression)

In the case simple expression, the entire expression will only take a column of values to make the corresponding judgment, the above query example is a simple box expression, you can use the following figure to indicate:

Case expressions can also be written in this way:

Selecttop 4 namelist=case EmployeeID when
	1 THEN ' Careyson ' when
	2 THEN ' Jack ' when
	3 THEN ' Tom '
	ELSE ' UNK Now '
	End,employeeid from
  [adventureworks].[ HumanResources]. [Employee]
  by EmployeeID

The above code and the previous code to achieve the same effect, from this code can be seen, the result of the case expression is actually limited to a column, which is why the case expression is "based on the logical expression of the column"

Because the value of the case expression is limited to one column, the value data type after then must be the same or compatible, or it will be an error.

In the above statement, there is also an optional "ELSE" statement that can be omitted, but the best thing to do is to keep else, otherwise all values that are not within the range of the matching value will be "NULL."