Expressions
Tags: beginners, opengauss, sql
Published: 2023-03-06 04:27:11

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dcwcnf8ftqtn41ns1h17.png)

· 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kgx2rb9wsk8g57i4o1jr.png)

**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::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ig1xlshqu25m4mgxlo97.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j0vg0iorq6flx52x26bm.png)

· DECODE

Figure 2 shows the syntax of a **DECODE** expression.

Figure 2 decode::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5ffe2zsqbnbfr22rxmf.png)

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gm2xeifv4boe3hlugbz4.png)

· COALESCE

Figure 3 shows the syntax of a **COALESCE** expression.

Figure 3 coalesce::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pmuu8z8gcpchghq5szj6.png)

**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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gwy642qn0s1ml2fwquvj.png)

· NULLIF

Figure 4 shows the syntax of a **NULLIF** expression.

Figure 4 nullif::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dcb0gdrecrp68kjit6k7.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m7u0ageo38c682ugvyjm.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9i44kc11bnanryb24n8d.png)

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

Figure 5 shows the syntax of a **GREATEST** expression.

Figure 5 greatest::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j24k55nguhv8to34j5yw.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4tqv59fumqzcdnfgzdm1.png)

Figure 6 shows the syntax of a **LEAST** expression.

Figure 6 least::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vmvkuovivtqmgapevhkl.png)

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vx9uqifgcx8gxrorzkga.png)

· NVL

Figure 7 shows the syntax of an NVL expression.

Figure 7 nvl::=

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/76dvpcp3kea40t0yj6k1.png)

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

Example:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/isjegg5bfyrrrwsdu5vc.png)