Search
Document Center

# Simple Log Service:Comparison operators

Last Updated:Jun 05, 2024

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.

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. √ × 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. √ √ 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.

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

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.

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