Operators allow you to perform computations and comparisons between two sets of data within a SQL statement. SQL has different kinds of operators that can be classified into groups:
Arithmetic Operators:
Arithmetic operators allow you to perform arithmetic between two values. They are usually used with numbers but some engines allow you to use arithmetic operators with other types like TEXT or DATETIME. See your database's documentation for information on how each operator is interpreted for different data types.
The typical operators are:
+ Addition.
- Subtraction.
* Multiplication.
/ Division.
% Returns the remainder part of a division, also known as the modulo operator.
SELECT
name_first,
name_last,
(age - (age % 10)) / 10 AS decades_old
FROM users;
Comparison operators are used to compare two values to each other. We have seen that they are typically used in WHERE clauses but they can be used in many others as well.
An expression using comparison operators always evaluates to either TRUE or FALSE ( 1 or 0 in SQLite). You can try this yourself, e.g. SELECT 1 = 2;
The typical operators are:
= or == Equal to.
> Greater than.
< Less than.
>= Greater than or equal to.
<= Less than or equal to.
<> or != Not Equal.
Note that comparisons use the same collation as the ORDER BY clause. This means that types like TEXT can be compared using these operators.
This example shows >= being used with a date. Recall from Data Types that dates in SQLite are stored as ISO-8601 formatted strings. This works well with the collation rules for strings in SQLite because the year is stored first, then month, then day. It means that sorting and comparing dates and times works as intended naturally!
SELECT name_first, name_last, last_login
FROM users
WHERE last_login >= '2024-02-01';
Logical operators allow you to combine expressions based on logical operations. They can also be chained together to form more complex expressions.
... AND ... Match when both expressions are true.
... OR ... Match when either expression is true.
... BETWEEN ... AND ... Match when the expression falls between two values inclusively. This is shorthand for using the <= and >= operators.
... IS NULL or ... IS NOT NULL Match when the expression is NULL or not.
... LIKE '%mail.com' Match based on a string pattern. '%' is the wildcard character used to match zero or more of any character. '_' is the wildcard character used to match exactly one of any character.
... REGEXP '.*' Match based on a regular expression pattern. SQLite does not support regular expression matching without extra plugins.
SELECT name_first, name_last, age
FROM users
WHERE age BETWEEN 20 AND 30
AND last_login IS NOT NULL
AND name_first LIKE '%Char%';
If your data itself contains special characters like '_', you can specify an "escape" character using the ESCAPE keyword. Special characters like wildcards can be preceded by your chosen escape character to treat them literally. In this case, we match an underscore in the data instead of the underscore being used as a wildcard.
SELECT name_first, name_last
FROM users
WHERE name_first LIKE '\_Charlotte' ESCAPE '\';
Bitwise operators are called bitwise because they operate on the individual bits of the binary representation of a value's data type. They have some clever use cases so it is worth understanding how they work!
In SQLite, bitwise operations can only be applied to integer data types. Other databases may allow bitwise operations on other types, such as text representations of binary or hexadecimal numbers.
The typical operators are:
& Bitwise AND. Performs bitwise AND between two numbers.
| Bitwise OR. Performs bitwise OR between two numbers.
~ Bitwise NOT. Performs bitwise NOT to a single number.
<< Left shift. Shifts the input left by the number of bits specified after the operator.
>> Right shift. Shifts the input right by the number of bits specified after the operator.