谓词
between
between 词在“where”子句中为值指定了包含边界。
语法
select * from ...
where val between lower_bound and upper_bound这个表达式等同于：
select * from ...
where val >= lower_bound and val <= upper_bound“not”运算符可以通过“between”添加到表达式中：
select * from ...
where val not between lower_bound and upper_bound这等同于：
select * from ...
where val < lower_bound or val > upper_bound示例
create table t(i integer, d date);
insert into t values([3,5],['2017-01-01','2017-02-01']);
select * from t where i between 1 and 3;
i d
------------------------------------------------------------------------------
3 01/01/2017 00:00:00
Selected records: 1
select * from t where d between '2017-01-01' and '2017-01-30';
i d
------------------------------------------------------------------------------
3 01/01/2017 00:00:00
Selected records: 1
select * from t where d between '2017-01-01' and now;
i d
------------------------------------------------------------------------------
3 01/01/2017 00:00:00
5 02/01/2017 00:00:00
Selected records: 2like
like谓词在“where”子句中为字符串值指定模式。与模式中指定的字符进行比较时区分大小写；即“A”不等于“a”。如果需要不区分大小写的比较，可以使用“ilike”运算符。模式可以是包含或不包含通配符“”或“%”的字符串。“%”是任意字符组合或无字符的占位符。下划线“”接受确切位置上的任何字符。
语法
select * from ...
where s like pattern
select * from ...
where s ilike pattern可以使用 like 或 ilike 将 not 运算符添加到表达式中：
select * from ...
where s not like pattern
select * from ...
where s not ilike pattern示例
create table names(s char(20));
insert into names values(['Smith','Jones','Black','Clark','Adams']);
select * from names;
s
------------------------------------------------------------------------------
Smith
Jones
Black
Clark
Adams
Selected records: 5
select * from names where s like 'A';
s
------------------------------------------------------------------------------
Selected records: 0
select * from names where s like 'A%';
s
------------------------------------------------------------------------------
Adams
Selected records: 1
select * from names where s like 'a%';
s
------------------------------------------------------------------------------
Selected records: 0
select * from names where s ilike 'a%';
s
------------------------------------------------------------------------------
Adams
Selected records: 1
select * from names where s like '%k%';
s
------------------------------------------------------------------------------
Black
Clark
Selected records: 2
select * from names where s like '%i%';
s
------------------------------------------------------------------------------
Smith
Selected records: 1
select * from names where s like '%a%';
s
------------------------------------------------------------------------------
Black
Clark
Adams
Selected records: 3
select * from names where s not like '%a%';
s
------------------------------------------------------------------------------
Smith
Jones
Selected records: 2
select * from names where s ilike '__a%';
s
------------------------------------------------------------------------------
Black
Clark
Adams
Selected records: 3
select * from s where s ilike '_o%';
s
------------------------------------------------------------------------------
Jones
Selected records: 1如需更多示例，请参阅 xSQL SDK 示例。
与序列字段的使用
like 运算符也可用于对序列类型字符串字段进行比较。例如，考虑以下表定义和初始化：
create table tt(i sequence(int), j sequence(char(20)));
insert into tt values(1, ['Smith','Jones','Black','Clark','Adams']);我们现在可以执行如下所示的选择语句：
select j = 'Adams' from tt;
#1
------------------------------------------------------------------------------
{0, 0, 0, 0, 1}
Selected records: 1此选择语句返回序列 j 的位掩码，对于满足等于条件的元素用 1 标记。同样，我们可以像下面这样使用 like 操作符：
select j like 'A%';
#1
------------------------------------------------------------------------------
{0, 0, 0, 0, 1}
Selected records: 1同样，此 select 语句返回序列 j 的位掩码，将满足 like 条件的元素标记为 1。使用 like 运算符的另一种变体如下：
select * from tt where j like 'A%';
i j
------------------------------------------------------------------------------
{1} {Smith, Jones, Black, Clark, Adams}
Selected records: 1此选择操作会搜索所有 tt 记录，其中序列 j 的某个元素以“A”开头。
in
in 操作符在 WHERE 子句中指定要匹配的一组值。该组值可以使用括在圆括号内的显式值指定，也可以使用 SELECT 语句指定。
语法
select * from ...
where val in (val1, .., valN)
select * from ...
where val in (select valX from ... where ...)这个表达式的第一种形式等价于：
select * from ...
where val = val1 or val = val2 .. or val = valN可以使用“in”将“not”运算符添加到表达式中：
select * from ...
where val not in (val1, .., valN)示例
create table names(s char(20));
insert into names values(['Smith','Jones','Black','Clark','Adams']);
select * from s where s in ('Jones','Smith');
s
------------------------------------------------------------------------------
Smith
Jones
Selected records: 2
select * from names where s not in ('Jones','Smith');
s
------------------------------------------------------------------------------
Black
Clark
Adams
Selected records: 3
select * from s where s in (select s from names where s like 'A%');
s
------------------------------------------------------------------------------
Adams
Selected records: 1更多示例请参见 xSQL SDK 示例中的内容。
exists
exists谓词指定要在where子句中匹配的值的结果集。
语法
select * from ...
where exists (select valX from ... where ...)可以使用“in”将“not”运算符添加到表达式中：
select * from ...
where not exists (select valX from ... where ...)示例
create table dep(id int, name char(20));
create table emp(name char(20), deptId int);
insert into dep values([1,2,3],['Sales','Customer Service','Technical Support']);
insert into emp values(['Smith','Jones','Black','Clark','Adams'], [3,3,1,2,1]);
select name from emp where exists (select * from dep where id = emp.deptId and dep.id = 1);
name
------------------------------------------------------------------------------
Black
Adams
Selected records: 2更多示例请参见 xSQL SDK 示例库。
prefix_match and exact_match
前缀匹配（prefix_match）和精确匹配（exact_match）谓词用于 Patricia 尝试索引。它们在 where 子句中为字符串值指定前缀。 有关 Patricia 尝试索引和搜索方法的更多详细信息，请参阅 Patricia 尝试索引页面。
语法
select * from ... where s prefix_match '123'
select * from ... where s exact_match '123'示例
create table T(s string);
create index idx_s on T(s) using ptree;
insert into T(s) values('123');
insert into T(s) values('12345678');
insert into T(s) values('111222333');
select * from T where s prefix_match '123';
s
------------------------------------------------------------------------------
123
Selected records: 1
select * from T where s prefix_match '12345678';
s
------------------------------------------------------------------------------
12345678
123
Selected records: 2
select * from T where s exact_match '123';
s
------------------------------------------------------------------------------
123
12345678
Selected records: 2
select * from T where s exact_match '12345678';
s
------------------------------------------------------------------------------
12345678
Selected records: 1