文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Teradata语法迁移/ 函数和操作符/ 比较和列表操作符
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

比较和列表操作符

以下章节介绍了支持的比较和列表操作符。

比较操作符LT、LE、GT、GE、EQ和NE不得用作表别名或列别名。

^=和GT

输入：比较操作（^=和GT） 
1
2
3
4
 
SELECT t1.c1, t2.c2 
  FROM tab1 t1, tab2 t2 
 WHERE t1.c3 ^= t1.c3
   AND t2.c4 GT 100;

输出：

1
2
3
4
 
SELECT t1.c1, t2.c2 
  FROM tab1 t1, tab2 t2 
 WHERE t1.c3 <> t1.c3
   AND t2.c4 > 100;

EQ和NE

输入：比较操作（EQ和NE） 
1
2
3
4
 
SELECT t1.c1, t2.c2 
  FROM tab1 t1 INNER JOIN tab2 t2 
    ON t1.c2 EQ t2.c2
 WHERE t1.c6 NE 1000;

输出：

1
2
3
4
5
 
 SELECT t1.c1, t2.c2 
  FROM tab1 t1 INNER JOIN tab2 t2 
    ON t1.c2 = t2.c2
 WHERE
        t1.c6 <> 1000;

LE和GE

输入：比较操作（LE和GE） 
1
2
3
4
 
SELECT t1.c1, t2.c2 
  FROM tab1 t1, tab2 t2 
 WHERE t1.c3 LE 200
   AND t2.c4 GE 100;

输出：

1
2
3
4
 
 SELECT t1.c1, t2.c2 
   FROM tab1 t1, tab2 t2 
  WHERE t1.c3 <= 200
    AND t2.c4 >= 100;

NOT=和LT

输入：比较操作（NOT=和LT） 
1
2
3
4
 
SELECT t1.c1, t2.c2 
  FROM tab1 t1, tab2 t2 
 WHERE t1.c3 NOT= t1.c3
   AND t2.c4 LT 100;

输出：

1
2
3
4
 
SELECT t1.c1, t2.c2 
  FROM tab1 t1, tab2 t2 
 WHERE t1.c3 <> t1.c3
   AND t2.c4 < 100;

IN和NOT IN

详情请参见IN/NOT IN转换。

输入：IN和NOT IN 
1
2
3
 
 SELECT c1, c2
   FROM tab1
  WHERE c1 IN 'XY';

输出：

1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c1 = 'XY';

DWS支持IN和NOT IN操作符，特殊场景除外。

IS NOT IN

输入：IS NOT IN 
1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c1 IS NOT IN (subquery);

输出：

1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c1 NOT IN (subquery);

LIKE ALL/NOT LIKE ALL

输入：LIKE ALL/NOT LIKE ALL 
1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c3 NOT LIKE ALL ('%STR1%', '%STR2%', '%STR3%');

输出：

1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c3 NOT LIKE ALL (ARRAY[ '%STR1%', '%STR2%', '%STR3%' ]);

LIKE ANY/NOT LIKE ANY

输入：LIKE ANY/NOT LIKE ANY 
1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c3 LIKE ANY ('STR1%', 'STR2%', 'STR3%');

输出：

1
2
3
 
SELECT c1, c2
  FROM tab1
 WHERE c3 LIKE ANY (ARRAY[ 'STR1%', 'STR2%', 'STR3%' ]);
父主题： 函数和操作符