See the expressions page of the SQLite3 documentation for much more information.
For single values
C/Java vs SQL
Many C/Java operators work with SQL; however, there are some notable exceptions. Here are some things to watch out for.
- Boolean operators need to be spelled out, e.g.,
||operator concatenates two strings.
<>to test if two expressions are not equal.
- Both the single equal sign,
=, and the double equal sign,
==, can be used to test for equality.
- Generally when one of the operands is
NULL, the result will be
- However, the operators
IS NOTcan test for equality even with
And here are a few things that are different but can be avoided.
- SQL has a tertiary
BETWEENoperator that can test the range of a number, e.g.,
5 BETWEEN 3 AND 7is true.
SQL has a similar
CASEoperator which resembles the
casestatement of C/Java, e.g.,
CASE x WHEN x > 0 THEN x ELSE -x.
- Like in C,
0is considered false and
1is considered true; however,
'6x'is also considered true.
- SQL has a rather wordy cast operators, e.g.,
CAST('6x' AS INTEGER).
SQL has a number of core
functions that can be applied to single values.
The most common, by far, seems to be
SQL uses the
LIKE operator for string matches.
% matches zero or more characters
_ matches one character
By default, the string matching operator is not
'SQL' LIKE 'S%'
'SQL' LIKE 'S_L'
'SQL' LIKE 'sql'
are all true.
Some SQL implementations even support a Unix-style glob.
As you would expect for a relational database application language, there are many operators that work on tables.
SQL has a few set operators for testing a value against the
result of a table returned from a
EXISTSis a unary operator that tests if a table has any elements.
NOT INare binary operators that test for membership in a table.
are applied to values from a table to generate a single value.
All of these ignore
NULL values inside the table.
The SQL standard provides modified comparisons
that test a single value
against all the values of a table.
This is done by placing
a comparison operators.
Modified comparisons are not supported in
- SQL has a
- To be compatible with all versions of SQL, you should surround
strings with single quotes. To include a single quote within a string,
use the Pascal convention, e.g.,
- The floating point numbers of C/Java and SQL are the same.
- SQL does not support C/Java style octal and hexadecimal literals.
You can use the
SELECT statement of SQL to test your expressions.
Here are some examples.
SELECT 5 + 6 ; SELECT 1 AND 0 ; SELECT 1 OR 0 ; SELECT 1 <> 0 ; SELECT 1 = 0 ; SELECT 1 == NULL ; SELECT 0 == NULL ; SELECT 1 IS NULL ; SELECT 5 BETWEEN 3 AND 7 ; SELECT 0 AND 1 + 5 ; SELECT (0 AND 1) + 5 ; SELECT ABS(-5) ; SELECT 'SQL' LIKE 'S%' ; SELECT 'SQL' LIKE 'S_L' ; SELECT 'SQL' LIKE 'sql' ; SELECT 'banananana' GLOB 'ba[^t]*' ; SELECT CAST(5 as integer) ; SELECT CAST('5' as integer) ; SELECT CAST('x' as integer) ; SELECT CAST('1x' as integer) ; SELECT 'ok' ; SELECT 070 ; CREATE TABLE t(a INTEGER) ; INSERT INTO t VALUES(2) ; INSERT INTO t VALUES(3) ; INSERT INTO t VALUES(5) ; INSERT INTO t VALUES(7) ; SELECT 5 IN t ; SELECT 4 IN t ; SELECT COUNT(a) FROM t ; SELECT AVG(a) FROM t ; SELECT MAX(a) FROM t; SELECT 14 | 5 ; SELECT 14 & 5 ; SELECT 14 >> 2 ;