vpruthi/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
doc-exports/docs/dws/tool/dws_16_0047.html
Lu, Huayi 27019c2991 DWS TOOL 830.201 version 
Reviewed-by: Hasko, Vladimir <vladimir.hasko@t-systems.com>
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Lu, Huayi <luhuayi@huawei.com>
Co-committed-by: Lu, Huayi <luhuayi@huawei.com>
2024-05-16 07:35:25 +00:00

29 KiB
Raw Permalink Blame History

Comparison and List Operators

The comparison operators LT, LE, GT, GE, EQ, and NE must not be used as TABLE alias or COLUMN alias.

The following comparison and list operators are supported:

^= and GT

Input: Comparison operations (^= and 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;

Output

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

EQ and NE

Input: Comparison operations (EQ and 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;

Output

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 and GE

Input: Comparison operations (LE and 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;

Output

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

NOT= and LT

Input: Comparison operations (NOT= and 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;

Output

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

IN and NOT IN

For details, see IN and NOT IN Conversion.

Input: IN and NOT IN
1
2
3
 SELECT c1, c2
   FROM tab1
  WHERE c1 IN 'XY';

Output

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

GaussDB A and GaussDB(DWS) support the IN and NOT IN operators, except in special scenarios.

IS NOT IN

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

Output

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

LIKE ALL/NOT LIKE ALL

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

Output

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

LIKE ANY/NOT LIKE ANY

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

Output

1
2
3
SELECT c1, c2
  FROM tab1
 WHERE c3 LIKE ANY (ARRAY[ 'STR1%', 'STR2%', 'STR3%' ]);
Parent topic: Functions and Operators