Operators

Published May 10, 2021Updated Dec 21, 2022

Operators are unique keywords used to perform arithmetic, comparison, and logical operations. They are commonly used in the WHERE clause of an expression.

Arithmetic Operators

Arithmetic operators are used to perform arithmetic on numeric types:

+: Addition
-: Subtraction
*: Multiplication
/: Division
%: Modulo (remainder)

Example

-- Addition
10 + 5
-- Subtraction
10 - 5
--  Multiplication
10 * 5
-- Division
10 / 5
-- Modulo
10 % 5

Comparison Operators

Comparison operators can be used to compare two values:

=: Equal to
>: Greater than
<: Less than
>=: Greater than or equal to
<=: Less than or equal to
!=: Not equal
<>: Not equal to

Example

SELECT *
FROM students
WHERE gpa > 25;

Logical Operators

Logical operators can be used to combine multiple conditions such as AND, OR, NOT or perform operations such as NOT and BETWEEN.

Operators

AND: Tests if all conditions in a given expression evaluate to TRUE.
BETWEEN: Selects values, inclusively of beginning and end values, within a given range. BETWEEN works with numbers, text, or date data types.
Equals To: Queries for items equal to a specified condition.
EXISTS: Tests a subquery and returns TRUE if at least one record satisfies it.
GREATER THAN: Queries for items that are greater than a given value
IF: Applies conditional logic to queries and returns a value.
IIF: Performs inline conditional checks within SELECT statements to return different values based on a condition.
IN: Allows the user to specify multiple values in the WHERE clause.
IS NOT NULL: Checks if a value is not NULL.
IS NULL: Checks if a value is NULL.
LIKE: Returns `TRUE` if its first text argument matches the wildcard pattern in its second argument.
NOT: Queries for items in an expression that return NOT TRUE for some condition(s).
NOT EQUAL TO: Compares two values and returns true if they are not equal.
NULL-SAFE EQUAL: Performs an equality comparison similar to the = operator, using the logic of an XNOR gate.
OR: Test if any condition in a given expression evaluates to TRUE.
SIMILAR TO: Used to compare a string to a regular expression