#
第八十八章 SQL命令 WHERE(一)
指定一个或多个限制性条件的`SELECT`子句。
# 大纲
```sql
SELECT fields
FROM table
WHERE condition-expression
```
## 参数
- `condition-expression` - 由一个或多个布尔谓词组成的表达式,该谓词控制要检索的数据值。
# 描述
可选的`WHERE`子句可以用于以下目的:
- 指定限制要返回哪些数据值的谓词。
- 指定两个表之间的显式连接。
- 指定基表和另一个表中的字段之间的隐式连接。
`WHERE`子句最常用于指定一个或多个谓词,这些谓词用于限制`SELECT`查询或子查询检索到的数据(过滤出行)。
还可以在`UPDATE`命令、`DELETE`命令或`INSERT`(或`INSERT or UPDATE`)命令的结果集中使用`WHERE`子句。
`WHERE`子句限定或取消查询选择中的特定行。
符合条件的行是那些条件表达式为真的行。
条件表达式可以是一个或多个逻辑测试(谓词)。
多个谓词可以通过`AND`和`OR`逻辑操作符链接。
如果谓词包含除法,并且数据库中有任何值可以生成值为零或`NULL`的除法,则不能依赖求值顺序来避免被零除法。
相反,使用`CASE`语句来抑制风险。
`WHERE`子句可以指定包含子查询的条件表达式。子查询必须用圆括号括起来。
`WHERE`子句可以使用`=`(内部连接)符号连接操作符指定两个表之间的显式连接。
`WHERE`子句可以使用箭头语法(`- >`)操作符在基表和来自另一个表的字段之间指定隐式连接。
## 指定字段
`WHERE`子句最简单的形式是指定一个比较字段和值的谓词,例如`WHERE Age > 21`。
有效的字段值包括以下:列名(`WHERE Age > 21`);
`%ID`, `%TABLENAME`,或`%CLASSNAME`;
标量函数指定列名(`WHERE ROUND(Age,-1)=60`),一个排序规则函数指定列名(`WHERE %SQLUPPER(name) %STARTSWITH ' AB'`)。
不能按列号指定字段。
因为重新编译表时`RowID`字段的名称可能会改变,`WHERE`子句应该避免通过名称引用`RowID`(例如,`WHERE ID=22`)。
相反,使用`%ID`伪列名来引用`RowID`(例如,`WHERE %ID=22`)。
不能通过列别名指定字段;
尝试这样做会产生`SQLCODE -29`错误。
但是,可以使用子查询来定义列别名,然后在`WHERE`子句中使用该别名。
例如:
```sql
SELECT Interns FROM
(SELECT Name AS Interns FROM Sample.Employee WHERE Age<21)
WHERE Interns %STARTSWITH 'A'
```
不能指定聚合字段;
尝试这样做将生成`SQLCODE -19`错误。
但是,可以通过使用子查询向`WHERE`子句提供聚合函数值。
例如:
```sql
SELECT Name,Age,AvgAge
FROM (SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person)
WHERE Age < AvgAge
ORDER BY Age
```
## 整型和字符串
如果将定义为整数数据类型的字段与数值进行比较,则在执行比较之前将数值转换为规范形式。
例如,`WHERE Age=007.00`解析为`WHERE Age=7`。
这种转换发生在所有模式中。
如果将定义为整数数据类型的字段与`Display`模式下的字符串值进行比较,则将该字符串解析为数值。
例如,与任何非数字字符串一样,空字符串(`"`)被解析为数字0。
这种解析遵循将字符串处理为数字的ObjectScript规则。
例如,WHERE Age='twenty'`解析为`WHERE Age=0`;
`WHERE Age='20something'`解析为`WHERE Age=20`。
SQL只在Display模式下执行解析;
在逻辑或ODBC模式下,将整数与字符串值进行比较将返回`null`。
要比较字符串字段和包含单引号的字符串,请使用双引号。
例如,`WHERE Name %STARTSWITH 'O'''`,返回的是 `O’Neil and O’Connor`, 而不是 `Obama`.
## 日期和时间
SQL日期和时间使用逻辑模式内部表示进行比较和存储。
它们可以以逻辑模式、显示模式或ODBC模式返回。
例如,`1944年9月28日`表示为:逻辑模式`37891`,显示模式`09/28/1944`,ODBC模式`194409-28`。
在条件表达式中指定日期或时间时,可能由于SQL模式与日期或时间格式不匹配,或由于无效的日期或时间值而发生错误。
`WHERE`子句条件表达式必须使用与当前模式相对应的日期或时间格式。
例如,在逻辑模式下,要返回出生日期为`2005`年的记录,`WHERE`子句将出现如下:`WHERE DOB BETWEEN 59901 AND 60265`
当在显示模式下,同样的`WHERE`子句会出现如下:`WHERE DOB BETWEEN '01/01/2005' AND '12/31/2005'`
如果条件表达式的日期或时间格式与显示模式不匹配,将导致错误:
- 在显示模式或ODBC模式下,以不正确的格式指定日期数据将产生`SQLCODE -146`错误。
以不正确的格式指定时间数据将产生`SQLCODE -147`错误。
- 在逻辑模式下,以不正确的格式指定日期或时间数据不会产生错误,但要么不返回数据,要么返回非预期的数据。
这是因为逻辑模式不会将显示或ODBC格式的日期或时间解析为日期或时间值。
`WHERE DOB BETWEEN 37500 AND 38000 AND DOB <> '1944-09-28' '`返回一系列`DOB`值,包括`DOB=37891(1944年9月28日)`,这是`<>`谓词试图忽略的。
无效的日期或时间值还会生成`SQLCODE -146`或`-147`错误。
无效日期是可以在显示模式/ODBC模式中指定的日期,但 IRIS不能转换为等效的逻辑模式。
例如,在ODBC模式下,以下命令会产生`SQLCODE -146`错误:`WHERE DOB > '1830-01-01'`,因为 IRIS无法处理1840年12月31日之前的日期值。
以下在ODBC模式下也会产生`SQLCODE -146`错误:`WHERE DOB BETWEEN '2005-01-01' AND '2005-02-29'`,因为2005不是闰年。
在逻辑模式下,`Display`模式或ODBC模式值不会被解析为日期或时间值,因此不会对其值进行验证。
因此,在逻辑模式下,`WHERE`子句(例如`WHERE DOB > '1830-01-01'`)不会返回错误。
## 流字段
在大多数情况下,不能在WHERE子句谓词中使用流字段。
这样做将导致`SQLCODE -313`错误。
但是,在`WHERE`子句中允许使用流字段:
- 流空测试:可以指定流字段`IS null`或流字段`IS NOT null`。
- 流长度测试:可以在`WHERE`子句谓词中指定`CHARACTER_LENGTH`(流字段)、`CHAR_LENGTH`(流字段)或`DATALENGTH`(流字段)函数。
- 流子串测试:可以在`WHERE`子句谓词中指定`substring (streamfield,start,length)`函数。
## List结构
IRIS支持列表结构数据类型`%list`(数据类型类`%Library.List`)。
这是一种压缩的二进制格式,并不映射到 SQL的相应本机数据类型。
它对应的数据类型为`VARBINARY`,默认`MAXLEN`为`32749`。
因此,动态SQL不能在`WHERE`子句比较中使用`%List`数据。
要引用结构化列表数据,请使用`%INLIST`谓词或`FOR SOME %ELEMENT`谓词。
要在条件表达式中使用列表字段的数据值,可以使用`%EXTERNAL`将列表值与谓词进行比较。
例如,要返回`FavoriteColors`列表字段值由单个元素`'Red'`组成的所有记录:
```sql
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors)='Red'
```
当`%EXTERNAL`将列表转换为`DISPLAY`格式时,显示的列表项似乎由一个空格分隔。
这个“空格”实际上是两个非显示字符`CHAR(13)`和`CHAR(10)`。
要对列表中的多个元素使用条件表达式,必须指定这些字符。
例如,要返回`FavoriteColors`列表字段值由两个元素`'Orange'`和`'Black'`(按顺序)组成的所有记录:
```sql
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors)='Orange'||CHAR(13)||CHAR(10)||'Black'
```
## 变量
`WHERE`子句谓词可以指定:
`%TABLENAME`或`%CLASSNAME`伪字段变量关键字。
`%TABLENAME`返回当前表名。
`%CLASSNAME`返回当前表对应的类名。
如果查询引用多个表,可以在关键字前加上表别名。
例如,`t1.%TABLENAME`。
一个或多个ObjectScript特殊变量(或它们的缩写):`$HOROLOG`, `$JOB`, `$NAMESPACE`, `$TLEVEL`, `$USERNAME`, `$ZHOROLOG`, `$ZJOB`, `$ZNSPACE`, `$ZPI`, `$ZTIMESTAMP`, `$ZTIMEZONE`, `$ZVERSION`。
## 谓词列表
SQL谓词可分为以下几类:
- Equality Comparison 谓词
- BETWEEN 谓词
- IN and %INLIST 谓词
- %STARTSWITH Predicate and Contains Operator
- NULL Predicate
- EXISTS Predicate
- FOR SOME Predicate
- FOR SOME %ELEMENT Predicate
- LIKE, %MATCHES, and %PATTERN Predicates
- %INSET and %FIND Predicates
## 谓词区分大小写
谓词使用为字段定义的排序规则类型。
默认情况下,字符串数据类型字段是用`SQLUPPER`排序规则定义的,它不区分大小写。
`%INLIST`、`Contains`操作符(`[`)、`%MATCHES`和`%PATTERN`谓词不使用字段的默认排序规则。
它们总是使用区分大小写的`EXACT`排序法。
两个字面值字符串的谓词比较总是区分大小写的。
## 谓词条件和`%NOINDEX`
可以使用`%NOINDEX`关键字作为谓词条件的前缀,以防止查询优化器在该条件上使用索引。
这在指定绝大多数行都满足的范围条件时非常有用。
例如,`WHERE %NOINDEX Age >= 1`。
## 离群值的谓词条件
如果动态SQL查询中的`WHERE`子句选择了一个非空的离群值,可以通过将离群值文字括在双括号中来显著提高性能。
这些双括号导致动态SQL在优化时使用离群值选择性。
例如,如果企业位于马萨诸塞州,那么很大一部分员工将居住在马萨诸塞州。
对于`Employees`表`Home_State`字段,`'MA'`是离群值。
要最优地选择这个值,应该指定`WHERE Home_State=(('MA'))`。
在嵌入式SQL或视图定义中不应使用此语法。
在嵌入式SQL或视图定义中,总是使用离群值选择,不需要特殊编码。
动态SQL查询中的`WHERE`子句会自动针对空离群值进行优化。
例如,`WHERE FavoriteColors IS NULL`这样的子句。
当`NULL`是离群值时,`is NULL`和`is NOT NULL`谓词不需要特殊编码。
离群值选择性由运行调优表实用程序决定。