Comparison Operators

Comparison operators compare values and return boolean results. They form the foundation of filtering conditions in the WHERE clause and join conditions in the JOIN clause.

Syntax

<value1> <operator> <value2>

Available Operators

Equals (=)

Tests if two values are equal.

Syntax:

<value1> = <value2>

Examples:

name = 'John'
age = 30
status = :statusParameter
customer.address.city = 'New York'

Not Equals (!=)

Tests if two values are not equal.

Syntax:

<value1> != <value2>

Examples:

category != 'Electronics'
status != 'inactive'
price != :targetPrice

Greater Than (>)

Tests if the first value is greater than the second value.

Syntax:

<value1> > <value2>

Examples:

price > 100
age > 18
createdDate > '2023-01-01'

Greater Than or Equal To (>=)

Tests if the first value is greater than or equal to the second value.

Syntax:

<value1> >= <value2>

Examples:

price >= 100
age >= 18
rating >= :minimumRating

Less Than (<)

Tests if the first value is less than the second value.

Syntax:

<value1> < <value2>

Examples:

price < 50
age < 65
expireDate < :currentDate

Less Than or Equal To (⇐)

Tests if the first value is less than or equal to the second value.

Syntax:

<value1> <= <value2>

Examples:

price <= 50
age <= 65
endDate <= '2023-12-31'

Type Compatibility

Comparison operators require compatible types on both sides:

String values can only be compared with other strings
Numeric values (int, long, float, double) can be compared with other numeric values
Boolean values can only be compared with other booleans
Date/timestamp values can only be compared with other date/timestamp values

Comparison with NULL

Comparison operators (=, !=, >, etc.) do not work correctly with NULL values. Use the IS NULL / IS NOT NULL operators instead:

-- Incorrect: does not find rows where email is NULL
email = NULL

-- Correct: finds rows where email is NULL
email IS NULL

Usage in WHERE Clause

Comparison operators are commonly used in the WHERE clause to filter results:

SELECT * FROM products
WHERE price > 100 AND category = 'Electronics'

Usage in JOIN Conditions

Comparison operators define how tables are joined together:

SELECT c.name, o.id
FROM customers AS c
JOIN orders AS o ON o.customerId = c.id

Notes

String comparisons are case-sensitive
Date/time comparisons work with ISO format strings ('YYYY-MM-DD', 'YYYY-MM-DDThh:mm:ss')
When comparing values of different but compatible numeric types, implicit conversion may occur
Field paths can be used on either side of a comparison operator

Related Features

Operators Overview - All available operators
Logical Operators - AND, OR, NOT operators
WHERE clause - Using comparisons for filtering
IS NULL / IS NOT NULL - Testing for NULL values
Data Types - Type compatibility information