SQLite is a powerful relational database management system that supports various operators for manipulating data. SQLite operators are symbols, keywords or special characters that are used to perform various operations on data. Here are some commonly used SQLite operators:
Comparison Operators
Comparison operators are used to compare two values and return a Boolean value (true or false) based on the comparison. The most commonly used comparison operators in SQLite are:
Equal to (=)
Not equal to (!= or <>)
Greater than (>)
Greater than or equal to (>=)
Less than (<)
Less than or equal to (<=)
Logical Operators
Logical operators are used to combine multiple conditions and return a Boolean value based on the result of the combination. The most commonly used logical operators in SQLite are:
AND Returns true if both conditions are true
OR Returns true if either condition is true
NOT Returns the opposite Boolean value of the condition
Arithmetic Operators
Arithmetic operators are used to perform mathematical operations on numeric values. The most commonly used arithmetic operators in SQLite are:
Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)
Modulus (%)
Bitwise Operators
Bitwise operators are used to perform bitwise operations on binary values. The most commonly used bitwise operators in SQLite are:
Bitwise AND (&)
Bitwise OR (|)
Bitwise XOR (^)
Bitwise NOT (~)
Left shift (<<)
Right shift (>>)
String Operators
String operators are used to perform operations on string values. The most commonly used string operators in SQLite are:
Concatenation (||)
Length (LENGTH)
Substring (SUBSTR)
Replace (REPLACE)
Trim (TRIM)
Other Operators
BETWEEN Used to filter results that fall within a specified range of values.
LIKE Used for pattern matching to filter results based on certain characters or strings.
IN Used to filter results based on a specified list of values.
UNION Used to combine the result sets of two or more SELECT statements.
UNION ALL Similar to UNION, but includes all duplicates.
INTERSECT Used to combine the result sets of two or more SELECT statements and returns only the common rows.
EXCEPT Used to combine the result sets of two SELECT statements and returns only the rows that are unique to the first SELECT statement.
EXISTS Used to check the existence of a value in a subquery.
NOT EXISTS Used to check the non-existence of a value in a subquery.
GLOB Used for pattern matching, similar to LIKE, but with a different syntax.
MATCH Used to search for text within a column using a full-text search engine.
REGEXP Used for pattern matching using regular expressions.
COLLATE Used to specify the collation sequence used to sort strings in a query.
ISNULL Used to filter results where a column is null.
IS NOT NULL Used to filter results where a column is not null.
AND Used to combine multiple conditions in a WHERE clause.
OR Used to specify multiple conditions where any of them can be true in a WHERE clause.
ESCAPE Used to specify an escape character for special characters in a LIKE clause.
NOT Used to negate a condition in a WHERE clause.
SQLite operators are a powerful tool for manipulating data and performing various operations on it. Understanding and using these operators effectively can help you to write complex queries and optimize your database performance.