In this part of the SQLite tutorial, we will cover SQLite expressions.
Wikipedia defines an expression in a programming language as a combination of values, variables, operators, and functions that are interpreted (evaluated) according to the particular rules of precedence and of association for a particular programming language, which computes and then produces (returns, in a stateful environment) another value. The expression is said to evaluate to that value.
A literal value is a constant of some kind. Literal values may be integers, floating point numbers, strings, BLOBs, or NULLs.
sqlite> SELECT 3, 'Wolf', 34.5; 3|Wolf|34.5
Here we return three literals: namely integer, string, and floating point constants.
sqlite> .nullvalue NULL sqlite> SELECT NULL; NULL
.nullvalue command tells SQLite to show
NULL. SQLite shows empty strings for
values by default. The
NULL value is a literal too.
sqlite> SELECT x'345eda2348587aeb'; x'345eda2348587aeb' -------------------
BLOB literals are string literals containing hexadecimal data
and preceded by a single "x" or "X" character.
Operators are used to build expressions. SQL operators are very similar to mathematical operators. SQLite supports unary and binary operators. Binary operators work with two operands, unary work with one. An operator may have one or two operands. An operand is one of the inputs (arguments) of an operator.
SQLite supports five broad categories of operators:
- Arithmetic operators
- Boolean operators
- Relational operators
- Bitwise operators
- Other operators
SQLite supports the following binary operators:
|| * / % + - << >> & | < <= > >= = == != <> IS IN LIKE GLOB BETWEEN AND OR
Operators are arranged according to the precedence.
|| operator has the highest order of precedence,
OR operator the lowest.
These are the unary prefix operators:
- + ~ NOT
+ operator is a no-op. It does not do anything. The
- operator changes positive values to negative
and vice versa.
sqlite> SELECT -(3-44); 41
The result is 41. The other two operators will be discussed later.
Arithmetic operators understood by SQLite are multiplication, division, addition, subtraction, and modulo.
sqlite> SELECT 3*3/9; 1
These are the multiplication and division operators that we know from mathematics.
sqlite> SELECT 3 + 4 - 1 + 5; 11
We show the addition and subtraction operators.
sqlite> SELECT 11 % 3; 2
% operator is called the modulo operator. It finds the
remainder of division of one number by another. The
11 % 3,
11 modulo 3 is 2, because 3 goes into 11 three times with a remainder of 2.
With boolean operators we perform logical operations. SQLite has three
Boolean operators return true or false. In SQLite, 1 is true, 0 is false.
AND operator evaluates to true if both operands are true.
sqlite> SELECT 0 AND 0, 0 AND 1, 1 AND 0, 1 AND 1; 0|0|0|1
The first three operations evaluate to false, the last one to true.
sqlite> SELECT 3=3 AND 4=4; 1
Both operands are true, so the result is true (1).
The OR operator evaluates to true if at least one of the operands is true.
sqlite> SELECT 0 OR 0, 0 OR 1, 1 OR 0, 1 OR 1; 0|1|1|1
The first operation evaluates to false, other operations evaluate to true.
NOT operator is a negation operator. It makes true false
and false true.
sqlite> SELECT NOT 1, NOT 0; 0|1 sqlite> SELECT NOT (3=3); 0
Relational operators are used to compare values.
|strictly less than|
|less than or equal to|
|greater than or equal to|
|not equal to|
These operators always result in a boolean value.
sqlite> SELECT 3*3 == 9, 9 = 9; 1|1
== are equality operators.
sqlite> SELECT 3 < 4, 3 <> 5, 4 >= 4, 5 != 5; 1|1|1|0
Usage of the relational operators is known from mathematics.
Decimal numbers are natural to humans. Binary numbers are native to computers. Binary, octal, decimal, or hexadecimal symbols are only notations of the same number. Bitwise operators work with bits of a binary number. We have binary logical operators and shift operators.
The bitwise and operator performs bit-by-bit comparison between two numbers. The result for a bit position is 1 only if both corresponding bits in the operands are 1.
00110 & 00011 = 00010
The first number is a binary notation of 6, the second is 3 and the result is 2.
sqlite> SELECT 6 & 3; 2 sqlite> SELECT 3 & 6; 2
The bitwise or operator performs bit-by-bit comparison between two numbers. The result for a bit position is 1 if either of the corresponding bits in the operands is 1.
00110 | 00011 = 00111
The result is 00110 or decimal 7.
sqlite> SELECT 6 | 3; 7
The bitwise shift operators shift bits to the right or left.
number << n : multiply number 2 to the nth power number >> n : divide number by 2 to the nth power
These operators are also called arithmetic shift.
00110 >> 00001 = 00011
We shift each of the bits of the number six to the right. It is equal to dividing the six by 2. The result is 00011 or decimal 3.
sqlite> SELECT 6 >> 1; 3
00110 << 00001 = 01100
We shift each of the bits of the number six to the left. It is equal to multiplying
the number six by 2. The result is
01100 or decimal 12.
sqlite> SELECT 6 << 1; 12
The bitwise negation operator changes each 1 to 0 and 0 to 1. It is also called the tilda operator.
sqlite> SELECT ~7; -8 sqlite> SELECT ~-8; 7
The operator inverts all bits of a number, 7. One of the bits also determines whether the number is negative. If we negate all the bits one more time, we get number 7 again.
Other SQLite operators include ||, IN, LIKE, GLOB, and BETWEEN.
sqlite> SELECT 'wolf' || 'hound'; wolfhound
|| operator is a string concatenation operator.
It simply joins strings.
We can use the
IN operator in two cases.
sqlite> SELECT 'Tom' IN ('Tom', 'Frank', 'Jane'); 1
Here we check if the string value 'Tom' is in the list of names that follows
IN operator. The return value is a boolean value.
For the following examples, we recapitulate what we have in the
sqlite> SELECT * FROM Cars; Id Name Price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
In the second case, the
IN operator allows us
to specify multiple values in a
sqlite> SELECT * FROM Cars WHERE Name IN ('Audi', 'Hummer'); Id Name Price ---------- ---------- ---------- 1 Audi 52642 7 Hummer 41400
Cars table, we choose cars that are listed after
LIKE operator is used in the
to search for a specified pattern in a column.
sqlite> SELECT * FROM Cars WHERE Name LIKE 'Vol%'; Id Name Price ---------- ---------- ---------- 4 Volvo 29000 8 Volkswagen 21600
Here we select cars whose names begin with 'Vol'. The percent sign (%) matches an arbitrary number of characters (including zero characters).
sqlite> SELECT * FROM Cars WHERE Name LIKE '____'; Id Name Price ---------- ---------- ---------- 1 Audi 52642
An underscore character (_) matches any single character. Here we select a car name that has exactly four characters; there are four underscores.
GLOB operator is similar to the
LIKE, but it
uses the Unix file globbing syntax for its wildcards. Also,
is case sensitive, unlike
sqlite> SELECT * FROM Cars WHERE Name GLOB '*en'; Id Name Price ---------- ---------- ---------- 6 Citroen 21000 8 Volkswagen 21600
Here we have cars, whose names end with 'en' characters.
sqlite> SELECT * FROM Cars WHERE Name GLOB '????'; Id Name Price ---------- ---------- ---------- 1 Audi 52642
Here we again select a car name that has exactly four characters.
sqlite> SELECT * FROM Cars WHERE Name GLOB '*EN'; sqlite> SELECT * FROM Cars WHERE Name LIKE '%EN'; Id Name Price ---------- ---------- ---------- 6 Citroen 21000 8 Volkswagen 21600
These two statements demonstrate that
LIKE is case insensitive
GLOB is case sensitive.
BETWEEN operator is equivalent to a pair of comparisons;
BETWEEN b AND c is equivalent to
a>=b AND a<=c.
sqlite> SELECT * FROM Cars WHERE Price BETWEEN 20000 AND 55000; Id Name Price ---------- ---------- ---------- 1 Audi 52642 4 Volvo 29000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
In this SQL statement, we have selected cars that cost between 20000 and 55000 units.
The rules of operator precedence specify which operators are evaluated first. The precedence level is necessary to avoid ambiguity in expressions.
What is the outcome of the following expression, 28 or 40?
3 + 5 * 5
Like in mathematics, the multiplication operator has a higher precedence than addition operator. So the outcome is 28.
(3 + 5) * 5
To change the order of evaluation, we can use parentheses. Expressions inside parentheses are always evaluated first.
sqlite> SELECT 3+5*5, (3+5)*5; 28|40
The first expression evaluates to 28 because multiplication operator has a higher precedence than addition. In the second example, we have used parentheses to change the order of evaluation. So the second expressions evaluates to 40.
Here we put again the list of operators in SQLite.
unary + - ~ NOT || * / % + - << <> & | < <= > >= = == != <> IS IN LIKE GLOB BETWEEN AND OR
The operators on the same row have the same level of precedence. The precedence grows from bottom to top.
Sometimes precedence is insufficient for determining the outcome of an expression. A second set of rules, called rules of associativity, determine the order of evaluation of operators with the same precedence level.
9 / 3 * 3
What is the outcome of this expression, 9 or 1? The multiplication,
deletion and the modulo operator
are left to right associated. So the expression is evaluated this
(9 / 3) * 3 and the result is 9.
sqlite> SELECT 9 / 3 * 3; 9
The associativity rule is left to right.
sqlite> SELECT 0 AND 0 OR 1; 1
The associativity rule is again left to right. If it was right to left, the result would be 0.
Arithmetic, boolean, relational, and bitwise operators are all left to right associated.
In this part of the SQLite tutorial, we have covered the SQLite expressions.