Teradata 简明教程
Teradata - Logical and Conditional Operators
Teradata 支持以下逻辑和条件运算符。这些运算符用于执行比较并组合多个条件。
Syntax |
Meaning |
> |
Greater than |
< |
Less than |
>= |
大于或等于 |
⇐ |
小于或等于 |
= |
Equal to |
BETWEEN |
If values within range |
IN |
If values in <expression> |
NOT IN |
如果值不在 <expression> 中 |
IS NULL |
If value is NULL |
IS NOT NULL |
如果值不为 NULL |
AND |
组合多个条件。仅当所有条件都满足时才评估为真 |
OR |
组合多个条件。仅当任一条件满足时才评估为真。 |
NOT |
颠倒条件的含义 |
BETWEEN
BETWEEN 命令用于检查某个值是否在一个值范围内。
Example
考虑以下员工表。
EmployeeNo |
FirstName |
LastName |
JoinedDate |
DepartmentNo |
BirthDate |
101 |
Mike |
James |
3/27/2005 |
1 |
1/5/1980 |
102 |
Robert |
Williams |
4/25/2007 |
2 |
3/5/1983 |
103 |
Peter |
Paul |
3/21/2007 |
2 |
4/1/1983 |
104 |
Alex |
Stuart |
2/1/2008 |
2 |
11/6/1984 |
105 |
Robert |
James |
1/4/2008 |
3 |
12/1/1984 |
以下示例获取雇员编号在 101、102 和 103 之间的记录。
SELECT EmployeeNo, FirstName FROM
Employee
WHERE EmployeeNo BETWEEN 101 AND 103;在执行上述查询时，它返回雇员编号在 101 和 103 之间的雇员记录。
*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
EmployeeNo FirstName
----------- ------------------------------
101 Mike
102 Robert
103 PeterIN
IN 命令用于针对给定的值列表检查值。
Example
以下示例获取雇员编号为 101、102 和 103 的记录。
SELECT EmployeeNo, FirstName FROM
Employee
WHERE EmployeeNo in (101,102,103);上述查询返回以下记录。
*** Query completed. 3 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
EmployeeNo FirstName
----------- ------------------------------
101 Mike
102 Robert
103 PeterNOT IN
NOT IN 命令颠倒了 IN 命令的结果。它获取与给定列表不匹配的值的记录。
Example
以下示例提取了不在 101、102 和 103 中的员工编号记录。
SELECT * FROM
Employee
WHERE EmployeeNo not in (101,102,103);上述查询返回以下记录。
*** Query completed. 2 rows found. 6 columns returned.
*** Total elapsed time was 1 second.
EmployeeNo FirstName LastName
----------- ------------------------------ -----------------------------
104 Alex Stuart
105 Robert James