COALESCE returns a Non-empty value in multiple values

COALESCE (expression_1, expression_2, ..., expression_n) refer to each parameter expression in turn, and then stop and return the value if it encounters a non-null value. If all of the expressions are NULL, a null value will eventually be returned. Using coalesce is that most expressions that contain null values will eventually return null values. Directory

1 basic information ▪ syntax ▪ parameters ▪ return type ▪ action ▪ note

2 Example

3 Basic Overview ▪ dictionary interpretation ▪ network interpretation1 Basic Information Editors GrammarNote: The connection operator "| |" is a notable exception. For example, a null value plus any value is NULL, and null values multiply by any value, and so on.Parametersexpression An expression of any type. n indicates that a placeholder for multiple expressions can be specified. All expressions must be of the same type, or they can be implicitly converted to the same type.DatabaseMysqlreturn typeReturns the same value as a expression.functionReturns the first non-empty expression in an expression, such as the following statement: SELECT COALESCE (null,null,3,4,5) from dual its return result is: 3NotesIf all the arguments are NULL, COALESCE returns a null value. COALESCE (expression1,... N) is equivalent to this case function: case when (expression1 isn't NULL) THEN expression1 ... When (Expressionn are not NULL) THEN expressionn ELSE NULL2 Sample Edits In the following example, a wages table containing three columns of information about an employee's annual wage income is displayed: hourly_wage, salary, and commission. However, each employee can only accept one payment method. To determine the total payroll paid to all employees, use the COALESCE function to accept non-null values found in Hourly_wage, salary, and commission. SET NOCOUNT on Go with Master IF EXISTS (SELECT table_name from INFORMATION_SCHEMA. TABLES WHERE table_name = ' wages ') DROP TABLE wages Go CREATE TABLE Wages (emp_id tinyint identity, hourly_wage decimal N ull, Salary decimal null, commission decimal NULL, Num_sales tinyint null) go INSERT wages VALUES (10.00, NULL, NULL, NULL Insert Wages VALUES (20.00, NULL, NULL, NULL) Insert wages VALUES (30.00, NULL, NULL, NULL) Insert wages VALUES (40.00, NUL L, NULL, NULL insert wages values (NULL, 10000.00, NULL, NULL) Insert wages values (NULL, 20000.00, NULL, NULL) insert wage s VALUES (null, 30000.00, NULL, NULL) Insert wages values (NULL, 40000.00, NULL, NULL) Insert wages values (NULL, NULL, 15000 , 3 Insert wages values (NULL, NULL, 25000, 2) Insert wages values (NULL, NULL, 20000, 6) Insert wages values (NULL, NULL, 1 4000, 4) go SET NOCOunt off Go SELECT CAST (COALESCE (Hourly_wage * m, salary, Commission * num_sales) as money) as "total salary" from W Ages go Below is the result set: Total Salary------------20800.0000 41600.0000 62400.0000 83200.0000 10000.0000 20000.0000 30000.0000 40 000.0000 45000.0000 50000.0000 120000.0000 56000.0000

Turn from: Http://baike.baidu.com/view/3380590.htm?fr=aladdin