GaussDB(DWS)子查询表达式

子查询表达式

• EXISTS/NOT EXISTS

  首先执行子查询，然后根据子查询的结果是否满足条件来决定是否继续执行主查询。如果子查询至少返回一行，则EXISTS结果就为“真”。如果子查询没有返回任何行， NOT EXISTS的结果是“真”。

  

  语法：

  1	WHERE column_name EXISTS/NOT EXISTS (subquery)

• IN/NOT IN

  用于测试某个给定的比较值是否存在于某一组值里。如果外层查询里的行与子查询返回的某一个行相匹配，那么IN的结果为“真”。如果外层查询中的行与子查询返回的所有行都不匹配，那么NOT IN的结果为“真”。

  

  语法：

  1	WHERE column_name IN/NOT IN (subquery)

• ANY/SOME

  ANY表示子查询中的任何值都可以与外部查询中的值匹配。SOME与ANY相同，只是在语法上的差别。

  右边的子查询必须只返回一个字段。左边表达式使用operator对子查询结果的每一行进行一次计算和比较（=、<>、<、<=、>、>=），其结果必须是布尔值。如果至少获得一个真值，则ANY结果为“真”。如果全部获得假值，则结果是“假”（包括子查询没有返回任何行的情况）。

  

  语法：

  1	WHERE column_name operator ANY/SOME (subquery)

• ALL

  右边的子查询必须只返回一个字段。左边表达式使用operator对子查询结果的每一行进行一次计算和比较（=、<>、<、<=、>、>=），其结果必须是布尔值。如果全部获得真值，ALL结果为“真”（包括子查询没有返回任何行的情况）。如果至少获得一个假值，则结果是“假”。

  

  语法：

  1	WHERE column_name operator ALL (subquery)

  表1 ALL查询条件

  条件	描述
  column_name > ALL(…)	column_name列中的值必须大于要评估为true的集合中的最大值。
  column_name >= ALL(…)	column_name列中的值必须大于或等于要评估为true的集合中的最大值。
  column_name < ALL(…)	column_name列中的值必须小于要评估为true的集合中的最小值。
  column_name <= ALL(…)	column_name列中的值必须小于或等于要评估为true的集合中的最小值。
  column_name <> ALL(…)	column_name列中的值不得等于要评估为true的集合中的任何值。
  column_name = ALL(…)	column_name列中的值必须等于要评估为true的集合中的任何值。

示例

创建记录课程信息的表course，并插入数据：

1
2
3
4
5

CREATE TABLE course(cid VARCHAR(10) COMMENT 'No.course',cname VARCHAR(10) COMMENT 'course name',teid VARCHAR(10) COMMENT 'No.teacher');

INSERT INTO course VALUES('01' , 'course1' , '02'); 
INSERT INTO course VALUES('02' , 'course2' , '01'); 
INSERT INTO course VALUES('03' , 'course3' , '03');

创建教师表teacher，并插入数据：

1
2
3
4
5
6

CREATE TABLE teacher(teid VARCHAR(10) COMMENT '教师编号',tname VARCHAR(10) COMMENT '教师姓名');

INSERT INTO teacher VALUES('01' , 'teacher1'); 
INSERT INTO teacher VALUES('02' , 'teacher2');
INSERT INTO teacher VALUES('03' , 'teacher3');
INSERT INTO teacher VALUES('04' , 'teacher4');

• EXISTS/NOT EXISTS示例

查询在course表中的教师记录：

1

SELECT * FROM teacher WHERE EXISTS (SELECT * FROM course WHERE course.teid = teacher.teid);

查询没有在course表中的教师记录：

1

SELECT * FROM teacher WHERE NOT EXISTS (SELECT * FROM course WHERE course.teid = teacher.teid);

• IN/NOT IN 示例

根据教师id匹配course表，查询在course表的教师信息：

1

SELECT * FROM course WHERE teid IN (SELECT teid FROM teacher );

查询不在course表的教师信息：

1

SELECT * FROM teacher WHERE teid NOT IN (SELECT teid FROM course );

• ANY/SOME 示例

左侧主句与右侧子查询进行字段比对，获取需要的结果集：

1

SELECT * FROM course WHERE teid < ANY (SELECT teid FROM teacher where teid<>'04');

或

1

SELECT * FROM course WHERE teid < some (SELECT teid FROM teacher where teid<>'04');

• ALL示例

查询teid列中的值必须小于要评估为true的集合中的最小值：

1

SELECT * FROM course WHERE teid < ALL(SELECT teid FROM teacher WHERE teid<>'01');

注意事项

• 禁止一条SQL语句中，出现重复子查询语句。
• 尽量少用标量子查询（标量子查询指结果为1个值，并且条件表达式为等值的子查询）。
• 避免在SELECT目标列中使用子查询，可能导致计划无法下推影响执行性能。
• 子查询嵌套深度建议不超过2层。由于子查询会带来临时表开销，过于复杂的查询应考虑从业务逻辑上进行优化。

子查询可以在 SELECT 语句中嵌套其他查询，从而实现更复杂的查询。子查询还可以在WHERE子句中使用其他查询的结果，从而更好地过滤数据。但是子查询可能会导致查询性能问题和代码难阅读和理解。 所以在GaussDB等数据库中使用SQL子查询时，请结合实际业务情况进行操作。