更新时间:2026-10-21 GMT+08:00
分享

子查询表达式

子查询表达式主要有以下几种。

EXISTS/NOT EXISTS

EXISTS/NOT EXISTS的语法图如图1所示。

图1 EXISTS/NOT EXISTS::=
  • 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所示。

图2 IN/NOT IN::=
  • 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所示。
图3 any/some::=
  • 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所示。

图4 all::=
  • 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;

相关文档