Expressions

An expression is similar to a formula. You can use it in a query statement to search for the result set of a specified condition in a database.

Simple Expressions

· Logical expressions

Logical Operators lists the operators and computation rules of logical expressions.

· Comparison expressions

Operators lists the common comparison operators.

In addition to comparison operators, you can also use the following sentence structures:

· BETWEEN operator

a BETWEEN x AND y is equivalent to a >= x AND a <= y.

a NOT BETWEEN x AND y is equivalent to a < x OR a > y.

· To check whether a value is NULL, use:

expression IS NULL

expression IS NOT NULL

or an equivalent (non-standard) sentence structure:

expression ISNULL

expression NOTNULL

· is distinct from/is not distinct from

· is distinct from

If the data types and values of A and B are not completely the same, the value is true.

If the data types and values of A and B are completely the same, the value is false.

Null values are considered the same.

· is not distinct from

If the data types and values of A and B are not completely the same, the value is false.

If the data types and values of A and B are completely the same, the value is true.

Null values are considered the same.

· Pseudocolumn (ROWNUM)

ROWNUM is a pseudocolumn that returns a number indicating the row number of the result obtained from the query. The value of ROWNUM in the first row is 1, the value of ROWNUM in the second row is 2, and so on. The return type of ROWNUM is numeric. ROWNUM can be used to limit the total number of rows returned by a query. For example, the following statement limits the maximum number of records returned from the customer_t1 table to 3.

Condition Expressions

Data that meets the requirements specified by conditional expressions are filtered during SQL statement execution.

Conditional expressions include the following types:

· CASE

CASE expressions are similar to the CASE statements in other programming languages.

Figure 1 shows the syntax of a CASE expression.

Figure 1 case::=

A CASE clause can be used in a valid expression. condition is an expression that returns a value of Boolean type.

· If the result is true, the result of the CASE expression is the required result.
· If the result is false, the following WHEN or ELSE clauses are processed in the same way.
· If every WHEN condition is false, the result of the expression is the result of the ELSE clause. If the ELSE clause is omitted and has no match condition, the result is NULL.
Example:

Note: If the tpcds schema does not exist, create the tpcds schema and then create the table.

· DECODE

Figure 2 shows the syntax of a DECODE expression.

Figure 2 decode::=

Compare each following compare(n) with base_expr. value(n) is returned if a compare(n) matches the base_expr expression. If base_expr does not match each compare(n), the default value is returned.

Example:

· COALESCE

Figure 3 shows the syntax of a COALESCE expression.

Figure 3 coalesce::=

COALESCE returns its first not-NULL value. If all the parameters are NULL, NULL is returned. This value is replaced by the default value when data is displayed. Like a CASE expression, COALESCE only calculates the parameters that are needed to determine the result. That is, parameters to the right of the first non-null parameter are not calculated.

Example:

Note: If the tpcds schema does not exist, create the tpcds schema and then create the table.

openGauss=# CREATE TABLE tpcds.c_tabl(description varchar(10), short_description varchar(10), last_value varchar(10)) ;

openGauss=# INSERT INTO tpcds.c_tabl VALUES('abc', 'efg', '123');
openGauss=# INSERT INTO tpcds.c_tabl VALUES(NULL, 'efg', '123');

openGauss=# INSERT INTO tpcds.c_tabl VALUES(NULL, NULL, '123');

openGauss=# SELECT description, short_description, last_value, COALESCE(description, short_description, last_value) FROM tpcds.c_tabl ORDER BY 1, 2, 3, 4;
description | short_description | last_value | coalesce
-------------+-------------------+------------+----------
abc | efg | 123 | abc
| efg | 123 | efg
| | 123 | 123
(3 rows)

openGauss=# DROP TABLE tpcds.c_tabl;

If description is not NULL, the value of description is returned. Otherwise, parameter short_description is calculated. If short_description is not NULL, the value of short_description is returned. Otherwise, parameter last_value is calculated. If last_value is not NULL, the value of last_value is returned. Otherwise, none is returned.

· NULLIF

Figure 4 shows the syntax of a NULLIF expression.

Figure 4 nullif::=

Only if the value of value1 is equal to that of value2 can NULLIF return NULL. Otherwise, value1 is returned.

Example:

Note: If the tpcds schema does not exist, create the tpcds schema and then create the table.

If the value of value1 is equal to that of value2, NULL is returned. Otherwise, the value of value1 is returned.

· GREATEST (maximum value) and LEAST (minimum value)

Figure 5 shows the syntax of a GREATEST expression.

Figure 5 greatest::=

You can select the maximum value from any numerical expression list.

Figure 6 shows the syntax of a LEAST expression.

Figure 6 least::=

You can select the minimum value from any numerical expression list.

Each of the preceding numeric expressions can be converted into a common data type, which will be the data type of the result.

The NULL values in the list will be omitted. The result is NULL only if the results of all expressions are NULL.

Example:

· NVL

Figure 7 shows the syntax of an NVL expression.

Figure 7 nvl::=

If the value of value1 is NULL, the value of value2 is returned. Otherwise, the value of value1 is returned.

Example: