更新时间:2026-10-21 GMT+08:00
子查询表达式
子查询表达式主要有以下几种。
EXISTS/NOT EXISTS
EXISTS/NOT EXISTS的语法图如图1所示。
- EXISTS的参数是一个任意的SELECT语句,或者是子查询。
- 系统对子查询进行运算以判断它是否返回行。如果它至少返回一行,则EXISTS结果就为真;如果子查询没有返回任何行, EXISTS的结果是假。
- 这个子查询通常只运行到能判断它是否可以生成至少一行为止,而不是等到全部运算结束。
示例:
m_db=# CREATE SCHEMA tpcds; m_db=# CREATE TABLE tpcds.store_returns ( sr_reason_sk integer, sr_reason_id character(16), sr_reason_desc character(100), sr_customer_sk integer ); m_db=# CREATE TABLE tpcds.date_dim ( d_dom integer, d_reason_desc character(100) ); m_db=# INSERT INTO tpcds.store_returns VALUES(1, 'test_001', 'test_desc_001', 5), (12, 'test_002', 'test_desc_002', 8), (23, 'test_003', 'test_desc_003', 9), (14, 'test_004', 'test_desc_004', 10), (45, 'test_005', 'test_desc_005', 12), (55, 'test_006', 'test_desc_006', 1), (43, 'test_007', 'test_desc_007', 12); m_db=# INSERT INTO tpcds.date_dim VALUES(1, 'test_dim_desc_001'), (6, 'test_dim_desc_002'), (9, 'test_dim_desc_003'), (19, 'test_dim_desc_004'), (12, 'test_dim_desc_005'), (23, 'test_dim_desc_006'); m_db=# SELECT sr_reason_sk,sr_customer_sk FROM tpcds.store_returns WHERE EXISTS (SELECT d_dom FROM tpcds.date_dim WHERE d_dom = store_returns.sr_reason_sk AND sr_customer_sk <10); sr_reason_sk | sr_customer_sk --------------+---------------- 1 | 5 12 | 8 23 | 9 (3 rows)
IN/NOT IN
IN/NOT IN的语法如图2所示。
- IN/NOT IN右侧为一个圆括号括起来的子查询,它只返回一个字段。
- IN/NOT IN左侧表达式对子查询结果的每一行进行一次计算和比较。
- 当IN/NOT IN语句中如果含有任何相等的子查询行,则IN结果为真;如果没有相等行,则结果为假(包括子查询没有返回任何行的情况)。
- 表达式或子查询行里的NULL遵照SQL处理布尔值和NULL组合时的规则。如果两个行对应的字段都相等且非空,则这两行相等;如果任意对应字段不等且非空,则这两行不等;否则结果是未知(NULL)。如果每一行的结果都是不等或NULL ,并且至少有一个NULL ,则IN的结果是NULL 。
- GUC参数m_format_dev_version控制行为保持前向兼容,m_format_dev_version取值为s3时,可使用最新功能,其他取值保持前向兼容。
- 当IN/NOT IN右侧为VALUE或VALUES子句时,rowexpr中的列必须为常量或者参数占位符。
示例一:
m_db=# SELECT sr_reason_sk,sr_customer_sk FROM tpcds.store_returns WHERE sr_customer_sk IN (SELECT d_dom FROM tpcds.date_dim WHERE d_dom < 10); sr_reason_sk | sr_customer_sk --------------+---------------- 55 | 1 23 | 9 (2 rows)
示例二:
m_db=# CREATE TABLE t_char_mix(pk1 int auto_increment PRIMARY KEY, pk2 int, id int, c1 char(255), c2 varchar(100), c3 varchar(100));
NOTICE: CREATE TABLE will create implicit sequence "t_char_mix_pk1_seq" for serial column "t_char_mix.pk1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_char_mix_pkey" for table "t_char_mix"
CREATE TABLE
m_db=# INSERT INTO t_char_mix(c1) VALUES ('2023-01-01 10:11:12.4');
INSERT 0 1
m_db=# SELECT distinct pk1,c1 FROM t_char_mix WHERE c1 IN (20230101000000) OR c1 IN (timestamp'2023-01-01 10:11:12.4') ORDER BY pk1;
WARNING: The double value '2023-01-01 10:11:12.4' is incorrect.
pk1 | c1
-----+-----------------------
1 | 2023-01-01 10:11:12.4
(1 row)
m_db=# SET m_format_dev_version='';
SET
m_db=# SELECT distinct pk1,c1 FROM t_char_mix WHERE c1 IN (20230101000000, timestamp'2023-01-01 10:11:12.4') ORDER BY pk1;
WARNING: The double value '2023-01-01 10:11:12.4' is incorrect.
pk1 | c1
-----+----
(0 rows)
m_db=# SET m_format_dev_version='s3';
SET
m_db=# select distinct pk1,c1 FROM t_char_mix WHERE c1 IN (20230101000000, timestamp'2023-01-01 10:11:12.4') ORDER BY pk1;
WARNING: The double value '2023-01-01 10:11:12.4' is incorrect.
pk1 | c1
-----+-----------------------
1 | 2023-01-01 10:11:12.4
(1 row)
m_db=# DROP TABLE t_char_mix;
DROP TABLE
m_db=# CREATE TABLE t_decimal_mix (pk1 int auto_increment PRIMARY KEY, pk2 int, id int, c1 numeric(30,6), c2 varchar(100), c3 varchar(100));
NOTICE: CREATE TABLE will create implicit sequence "t_decimal_mix_pk1_seq" for serial column "t_decimal_mix.pk1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_decimal_mix_pkey" for table "t_decimal_mix"
CREATE TABLE
m_db=# INSERT INTO t_decimal_mix (c1) VALUES (11.123);
INSERT 0 1
m_db=# INSERT INTO t_decimal_mix (c1) VALUES (11.456);
INSERT 0 1
m_db=# INSERT INTO t_decimal_mix (c1) VALUES (4294967294);
INSERT 0 1
m_db=# SELECT pk1,c1 FROM t_decimal_mix WHERE c1 IN (cast(11.123 as float)) OR c1 IN (4294967294) ORDER BY pk1;
pk1 | c1
-----+-------------------
3 | 4294967294.000000
(1 row)
m_db=# SET m_format_dev_version='';
SET
m_db=# SELECT * FROM t_decimal_mix WHERE c1 IN (cast(11.123 as float),4294967294) ORDER BY pk1;
pk1 | pk2 | id | c1 | c2 | c3
-----+-----+----+-------------------+----+----
1 | | | 11.123000 | |
3 | | | 4294967294.000000 | |
(2 rows)
m_db=# SET m_format_dev_version='s3';
SET
m_db=# SELECT * FROM t_decimal_mix WHERE c1 IN (cast(11.123 as float),4294967294) ORDER BY pk1;
pk1 | pk2 | id | c1 | c2 | c3
-----+-----+----+-------------------+----+----
3 | | | 4294967294.000000 | |
(1 row)
m_db=# DROP TABLE t_decimal_mix;
DROP TABLE 示例三:
m_db=# CREATE TABLE t_float4_test (id INT AUTO_INCREMENT PRIMARY KEY, c1 float(2,2), c2 float) WITH (storage_type=ustore); NOTICE: CREATE TABLE will create implicit sequence "t_float4_test_id_seq" for serial column "t_float4_test.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_float4_test_pkey" for table "t_float4_test" CREATE TABLE m_db=# CREATE INDEX idx_float4_id USING ubtree ON t_float4_test(c1, c2) WITH (storage_type=ustore); CREATE INDEX m_db=# INSERT INTO t_float4_test (c1, c2) VALUES (0.3, 0.3), (0.3, 0.1), (0.1, 0.3); INSERT 0 3 m_db=# SET m_format_dev_version=''; SET m_db=# SELECT * FROM t_float4_test WHERE c1 IN (cast(0.3 as float8)) ORDER BY id; id | c1 | c2 ----+----+---- (0 rows) m_db=# SELECT * FROM t_float4_test WHERE c1 IN (cast(0.1 as float8)) ORDER BY id; id | c1 | c2 ----+----+---- (0 rows) m_db=# SELECT * FROM t_float4_test WHERE c1 IN (cast(0.3 as float8), cast(0.1 as float8)) ORDER BY id; id | c1 | c2 ----+------+----- 1 | 0.30 | 0.3 2 | 0.30 | 0.1 3 | 0.10 | 0.3 (3 rows) m_db=# SET m_format_dev_version='s3'; SET m_db=# SELECT * FROM t_float4_test WHERE c1 IN (cast(0.3 as float8)) ORDER BY id; id | c1 | c2 ----+----+---- (0 rows) m_db=# SELECT * FROM t_float4_test WHERE c1 IN (cast(0.1 as float8)) ORDER BY id; id | c1 | c2 ----+----+---- (0 rows) m_db=# SELECT * FROM t_float4_test WHERE c1 IN (cast(0.3 as float8), cast(0.1 as float8)) ORDER BY id; id | c1 | c2 ----+----+---- (0 rows) m_db=# DROP TABLE t_float4_test; DROP TABLE
示例四:
m_db=# CREATE TABLE t_bigint(c1 BIGINT, c2 BIGINT); CREATE TABLE m_db=# INSERT INTO t_bigint VALUES(1, 1); INSERT 0 1 m_db=# SELECT * FROM t_bigint WHERE (c1, c2) IN (VALUES(1, 1), (2, 2)); c1 | c2 ----+---- 1 | 1 (1 row) m_db=# DROP TABLE t_bigint; DROP TABLE
ANY/SOME
ANY/SOME的语法图如图3所示。
- ANY/SOME右边是一个圆括号括起来的子查询,它只返回一个字段。
- ANY/SOME左边表达式使用operator对子查询结果的每一行进行一次计算和比较,其结果是布尔值。如果至少获得一个真值,则ANY结果为真;如果全部获得假值,则结果是假(包括子查询没有返回任何行的情况)。SOME是ANY的同义词。IN与ANY可以等效替换 。
示例:
m_db=# SELECT sr_reason_sk,sr_customer_sk FROM tpcds.store_returns WHERE sr_customer_sk < ANY (SELECT d_dom FROM tpcds.date_dim WHERE d_dom < 10); sr_reason_sk | sr_customer_sk --------------+---------------- 1 | 5 12 | 8 55 | 1 (3 rows)
ALL
ALL的语法如图4所示。
- ALL右边是一个圆括号括起来的子查询,它只返回一个字段。
- ALL左边表达式使用operator对子查询结果的每一行进行一次计算和比较,其结果是布尔值。如果全部获得真值,ALL结果为真(包括子查询没有返回任何行的情况);如果至少获得一个假值,则结果是假。
示例:
m_db=# SELECT sr_reason_sk,sr_customer_sk FROM tpcds.store_returns WHERE sr_customer_sk < all(SELECT d_dom FROM tpcds.date_dim WHERE d_dom < 10); sr_reason_sk | sr_customer_sk --------------+---------------- (0 rows) m_db=# DROP TABLE tpcds.store_returns; m_db=# DROP TABLE tpcds.date_dim; m_db=# DROP SCHEMA tpcds;
父主题: 表达式



