SQLite - Operators

SQLite - Operators

What is an Operator?

An operator is a reserved word or character used primarily in a WHERE clause to perform operations, such as comparisons and arithmetic. Operators are the building blocks for filtering data and creating complex conditions in your queries.

SQLite supports four main types of operators:

• Arithmetic operators
• Comparison operators
• Logical operators
• Bitwise operators

SQLite Arithmetic Operators

These operators perform mathematical calculations on numeric values.

Operator	Description	Example
+	Addition	SELECT 10 + 5; returns 15
-	Subtraction	SELECT 10 - 5; returns 5
*	Multiplication	SELECT 10 * 5; returns 50
/	Division	SELECT 10 / 5; returns 2
%	Modulus (remainder of division)	SELECT 10 % 3; returns 1

SQLite Comparison Operators

These operators compare two expressions and return a boolean value (true or false). They are essential for WHERE clauses.

Operator	Description	Example
= or ==	Equal to. Both are aliases.	... WHERE price = 100;
!= or <>	Not equal to. Both are aliases.	... WHERE status != 'shipped';
>	Greater than	... WHERE quantity > 50;
<	Less than	... WHERE age < 18;
>=	Greater than or equal to	... WHERE score >= 90;
<=	Less than or equal to	... WHERE discount <= 0.5;

SQLite Logical Operators

These operators combine multiple boolean conditions to form more complex filters.

Operator	Description
AND	Returns true if all conditions are true. ... WHERE price < 100 AND in_stock = 1
OR	Returns true if any of the conditions are true. ... WHERE country = 'USA' OR country = 'Canada'
NOT	Reverses the result of a condition. ... WHERE NOT country = 'Germany'
BETWEEN	Checks if a value is within a range (inclusive). ... WHERE price BETWEEN 50 AND 100 is equivalent to price >= 50 AND price <= 100
IN	Checks if a value matches any value in a list. ... WHERE status IN ('pending', 'processing')
LIKE	Performs pattern matching on strings using wildcards (% for any sequence of characters, _ for a single character).
GLOB	Similar to LIKE, but uses Unix-style wildcards (* for any sequence, ? for a single character) and is case-sensitive.
IS NULL	Checks if a value is NULL. ... WHERE shipping_date IS NULL
IS NOT NULL	Checks if a value is not NULL. ... WHERE shipping_date IS NOT NULL
EXISTS	Checks for the existence of rows in a subquery. Often used for efficient joins.
||	String Concatenation. SELECT 'Hello' || ' ' || 'World'; returns ‘Hello World’. Note: In standard SQL, this is a logical OR.

SQLite Bitwise Operators

Bitwise operators perform operations on the individual bits of integer values. They are used in specialized applications like manipulating permission flags.

Assume integer A = 60 (binary 00111100) and B = 13 (binary 00001101):

Operator	Description	Example
& (Bitwise AND)	Sets a bit to 1 if it is 1 in both operands.	(A & B) results in 12 (binary 00001100)
| (Bitwise OR)	Sets a bit to 1 if it is 1 in either operand.	(A | B) results in 61 (binary 00111101)
~ (Bitwise NOT)	Inverts all the bits of the operand.	(~A) results in -61, due to two’s complement representation of signed integers.
<< (Left Shift)	Shifts bits to the left, filling with zeros on the right.	A << 2 results in 240 (binary 11110000)
>> (Right Shift)	Shifts bits to the right, discarding bits from the right.	A >> 2 results in 15 (binary 00001111)