Syntax and examples of comparison operators - Simple Log Service

Comparison operators are used to compare parameter values. The values that are of the following data types can be compared: double, bigint, varchar, timestamp, and date. This topic describes the syntax of comparison operators. This topic also provides examples on how to use the comparison operators.

Overview of comparison operators

The following table describes the comparison operators that are supported by Simple Log Service.

Important If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.

Operator	Syntax	Description	Supported in SQL	Supported in SPL
Relational operators	x < y	If x is less than y, true is returned.	√	√
	x > y	If x is greater than y, true is returned.	√	√
	x <= y	If x is less than or equal to y, true is returned.	√	√
	x >= y	If x is greater than or equal to y, true is returned.	√	√
	x = y	If x is equal to y, true is returned.	√	√
	x <> y	If x is not equal to y, true is returned.	√	√
	x != y	If x is not equal to y, true is returned.	√	√
ALL operator	x relational operator ALL(subquery)	If x meets all conditions, true is returned.	√	×
ANY operator	x relational operator ANY(subquery)	If x meets one of the conditions, true is returned.	√	×
BETWEEN operator	x BETWEEN y AND z	If x is between y and z, true is returned.	√	√
DISTINCT operator	x IS DISTINCT FROM y	If x is not equal to y, true is returned.	√	×
DISTINCT operator	x IS NOT DISTINCT FROM y	If x is equal to y, true is returned.	√	×
LIKE operator	x LIKE pattern [escape 'escape_character']	Matches a specified character pattern in a string. The string is case-sensitive.	√	√
SOME operator	x relational operator SOME(subquery)	If x meets one of the conditions, true is returned.	√	×
GREATEST operator	GREATEST(x, y...)	Obtains the greater value of x and y.	√	×
LEAST operator	LEAST(x, y...)	Obtains the smaller value of x and y.	√	×
NULL operator	x IS NULL	If x is null, true is returned.	√	√
NULL operator	x IS NOT NULL	If x is not null, true is returned.	√	√

Relational operators

Relational operators compare x and y. If the condition is met, true is returned.

Syntax

Syntax	Description
x < y	x is less than y.
x > y	x is greater than y.
x <= y	x is less than or equal to y.
x >= y	x is greater than or equal to y.
x = y	x is equal to y.
x <> y	x is not equal to y.
x != y	x is not equal to y.

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
y	The value of this parameter is of a data type that supports comparison.

Return value type

The Boolean type.

Examples

Example 1: Query logs from the previous day.

Query statement

* |
SELECT
  *
FROM  log
WHERE
  __time__ < to_unixtime(current_date)
  AND __time__ > to_unixtime(date_add('day', -1, current_date))

Query and analysis results

Example 2: E-commerce Company A uses the mobile and client_ip fields in access logs to find the customers whose phone numbers are from a different place than the IP addresses used to access the website of the company.
- Sample field
```
mobile:1881111****
client_ip:192.168.2.0
```
- Query statement
```
* |
SELECT
  mobile,
  client_ip,
  count(*) AS PV
WHERE
  mobile_city(mobile) != ip_to_city(client_ip)
  AND ip_to_city(client_ip) != ''
GROUP BY
  client_ip,
  mobile
ORDER BY
  PV DESC
```
- Query and analysis results

ALL operator

The ALL operator determines whether x meets all conditions. If all conditions are met, true is returned.

Syntax

x relational operator ALL(subquery)

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
Relational operator	The value of this parameter is a relational operator. Valid values: < > <= >= = <> != Important The ALL operator must follow the relational operator. Relational operators: < > <= >= = <> !=
subquery	The value of this parameter is an SQL subquery.

Return value type

The Boolean type.

Examples

Check whether each request related to instance i-01 is responded with the status code 200.

Sample field
```
instance_id:i-01
status:200
```

Query statement

* | select 200 = ALL(select status where instance_id='i-01')

Query and analysis results

ANY operator

The ANY operator determines whether x meets one of the conditions. If one of the conditions is met, true is returned.

Syntax

x relational operator ANY(subquery)

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
Relational operator	The value of this parameter is a relational operator. Valid values: < > <= >= = <> != Important The ANY operator must follow the relational operator. Relational operators: < > <= >= = <> !=
subquery	The value of this parameter is an SQL subquery.

Return value type

The Boolean type.

Examples

Check whether any request related to instance i-01 is responded with the status code 200.

Sample field
```
instance_id:i-01
status:200
```

Query statement

* | SELECT 200 = ANY(SELECT status WHERE instance_id='i-01')

Query and analysis results

BETWEEN operator

The BETWEEN operator determines whether x is between y and z. If the condition is met, true is returned. y and z specify a closed interval.

Syntax

x BETWEEN y AND z

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
y	The value of this parameter is of a data type that supports comparison.
z	The value of this parameter is of a data type that supports comparison.

Important

The data types of x, y, and z must be the same.
If the value of x, y, or z contains null, null is returned.

Return value type

The Boolean type.

Examples

Example 1: Determine whether the value of the status field is within the [200,299] range.
- Query statement
```
* | SELECT status BETWEEN 200 AND 299
```
- Query and analysis results
Example 2: Determine the number of logs whose value of the status field is not within the [200,299] range.
- Query statement
```
* | SELECT count(*) AS count FROM log WHERE status NOT BETWEEN 200 AND 299
```
- Query and analysis results

DISTINCT operator

The DISTINCT operator determines whether x is equal to y.

Syntax

IS DISTINCT FROM: If x is not equal to y, true is returned.
```
x IS DISTINCT FROM y
```
IS NOT DISTINCT FROM: If x is equal to y, true is returned.
```
x IS NOT DISTINCT FROM y
```

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
y	The value of this parameter is of a data type that supports comparison.

Unlike the = and <> operators, the DISTINCT operator can be used to perform comparison on null.

x	y	x = y	x <> y	x IS DISTINCT FROM y	x IS NOT DISTINCT FROM y
1	1	true	false	false	true
1	2	false	true	true	false
1	null	null	null	true	false
null	null	null	null	false	true

Return value type

The Boolean type.

Examples

Compare 0 against null.

Query statement
```
* | select 0 IS DISTINCT FROM null
```
Query and analysis results

LIKE operator

The LIKE operator matches a specified character pattern in a string. The string is case-sensitive.

Syntax

x LIKE pattern [escape 'escape_character']

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
pattern	The value of this parameter is the character pattern, which can contain strings or wildcard characters. The following wildcard characters are supported: Percent sign (%): indicates an arbitrary number of characters. Underscore (_): indicates a single character.
escape_character	The value of this parameter is a character expression that is used to escape the wildcard characters in the character pattern.

Note

The LIKE operator is used to query logs based on exact match. For more information, see How do I query logs by using exact match?

Return value type

The Boolean type.

Examples

SQL

Example 1: Query the logs whose value of the request_uri field ends with file-8 or file-6.
- Sample field
```
request_uri:/request/path-2/file-6
```
- Query statement
```
*|SELECT * WHERE request_uri LIKE '%file-8' OR request_uri LIKE '%file-6'
```
- Query and analysis results
Example 2: Check whether the value of the request_uri field ends with file-6.
- Sample field
```
request_uri:/request/path-2/file-6
```
- Query statement
```
* | SELECT request_uri LIKE '%file-6'
```
- Query and analysis results

SPL

Example 1: Query the logs whose value of the request_uri field ends with file-8 or file-6.
- Sample field

request_uri:/request/path-2/file-6

SPL statement

*|WHERE request_uri LIKE '%file-8' OR request_uri LIKE '%file-6'

SPL results

Example 2: Check whether the value of the request_uri field ends with file-6.
- Sample field

request_uri:/request/path-2/file-6

SPL statement

* | extend a = request_uri LIKE '%file-6'

SPL results

SOME operator

The SOME operator determines whether x meets one of the conditions. If one of the conditions is met, true is returned.

Syntax

x relational operator SOME(subquery)

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
Relational operator	The value of this parameter is a relational operator. Valid values: < > <= >= = <> != Important The SOME operator must follow the relational operator. Relational operators: < > <= >= = <> !=
subquery	The value of this parameter is an SQL subquery.

Return value type

The Boolean type.

Examples

Check whether any request related to instance i-01 is processed for less than 20s.

Sample field
```
instance_id:i-01
request_time:16
```

Query statement

* | SELECT 20 > SOME(SELECT request_time WHERE instance_id='i-01')

Query and analysis results

GREATEST operator

The GREATEST operator obtains the greater value of x and y.

Note

The GREATEST operator is used for horizontal comparison, and the max function is used for vertical comparison.

Syntax

GREATEST(x, y...)

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
y	The value of this parameter is of a data type that supports comparison.

Return value type

The double type.

Examples

Compare the values of the request_time and status fields in the same log to obtain the greater value.

Sample field
```
request_time:38
status:200
```

Query statement

* |  SELECT GREATEST(request_time,status)

Query and analysis results

LEAST operator

The LEAST operator obtains the smaller value of x and y.

Note

The LEAST operator is used for horizontal comparison, and the min function is used for vertical comparison.

Syntax

LEAST(x, y...)

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.
y	The value of this parameter is of a data type that supports comparison.

Return value type

The double type.

Examples

Compare the values of the request_time and status fields in the same log to obtain the smaller value.

Sample field
```
request_time:77
status:200
```
Query statement
```
* |  SELECT LEAST(request_time,status)
```
Query and analysis results

NULL operator

The NULL operator determines whether x is null.

Syntax

IS NULL: If x is null, true is returned.
```
x IS NULL
```
IS NOT NULL: If x is not null, true is returned.
```
x IS NOT NULL
```

Parameters

Parameter	Description
x	The value of this parameter is of a data type that supports comparison.

Return value type

The Boolean type.

Examples

Example1: Determine whether the value of the status field is null.
- Query statement
```
* | select status IS NULL
```
- Query and analysis results
Example 2: Determine the number of logs whose status field is not empty.
- Query statement
```
* | SELECT count(*) AS count FROM log WHERE status IS NOT NULL
```
- Query and analysis results