Subqueries

Single-Row Subqueries

Single-row subquery operators include >=, >, <=, <, and <>.

-- Create a student table and insert data into the table.
gaussdb=# CREATE TABLE student(
    sid VARCHAR(5),     -- Student ID
    grade INT,          -- Grade
    name VARCHAR(20),   -- Name
    height INT          -- Height
);
gaussdb=# INSERT INTO student VALUES ('00001',1,'Scott',135),('00002',1,'Jack',95),('00003',1,'Ben',100);
gaussdb=# INSERT INTO student VALUES ('00004',2,'Henry',115),('00005',2,'Jordan',130),('00006',2,'Bob',126);
gaussdb=# INSERT INTO student VALUES ('00007',3,'Bella',128),('00008',3,'Alicia',136);
-- Create a teacher table and insert data into the table.
gaussdb=# CREATE TABLE teacher (
    name VARCHAR(20),    -- Teacher name
    grade INT            -- Class
);
-- Insert data.
gaussdb=# INSERT INTO teacher VALUES ('Bill',1),('Sally',2),('Luke',3);

-- Query the students who are taller than Bella.
gaussdb=# SELECT * FROM student
WHERE height > (SELECT height FROM student WHERE name = 'Bella');
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00001 |     1 | Scott  |    135
 00005 |     2 | Jordan |    130
 00008 |     3 | Alicia |    136
(3 rows)

Multi-Row Subqueries

Multi-row subquery operators are as follows:

• IN: equal to any value in the list.
• ANY: used together with single-row comparison operators to compare with any value returned by the subquery.

• ALL: used together with single-row comparison operators to compare with all values returned by the subquery.
• SOME: another name for ANY and both operators have the same effect.

Example: Query the students of Sally and Luke.

gaussdb=# SELECT * FROM student t1 WHERE t1.grade IN (
    SELECT grade FROM teacher WHERE name = 'Sally' OR name = 'Luke'
);
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00004 |     2 | Henry  |    115
 00005 |     2 | Jordan |    130
 00006 |     2 | Bob    |    126
 00007 |     3 | Bella  |    128
 00008 |     3 | Alicia |    136
(5 rows)

Example: Query the students in grade 2 who are taller than any students in grade 3.

gaussdb=# SELECT * FROM student
WHERE grade = 2 AND 
      height > ANY (SELECT height FROM student WHERE grade = 3);
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00005 |     2 | Jordan |    130
(1 row)

-- The query result is equivalent to that of the following query:
gaussdb=# SELECT * FROM student
WHERE grade = 2 AND 
      height > (SELECT MIN(height) FROM student WHERE grade = 3);

Example: Query the students in grade 1 who are taller than all students in grade 2.

gaussdb=# SELECT * FROM student
WHERE grade = 1 AND
      height > ALL (SELECT height FROM student WHERE grade = 2);
  sid  | grade | name  | height 
-------+-------+-------+--------
 00001 |     1 | Scott |    135
(1 row)

-- The query result is equivalent to that of the following query:
gaussdb=# SELECT * FROM student
WHERE grade = 1 AND 
      height > (SELECT MAX(height) FROM student WHERE grade = 2);

Correlated Subqueries

Characteristics: Subqueries cannot run independently and are related to parent queries. Execute parent queries and then subqueries. Each time a parent query is executed, its subquery is recalculated.

Example: Query the students whose height is greater than the average height of the students in the class.

gaussdb=# SELECT * FROM student out
WHERE height > (SELECT AVG(height) FROM student
                WHERE grade = out.grade);
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00001 |     1 | Scott  |    135
 00005 |     2 | Jordan |    130
 00006 |     2 | Bob    |    126
 00008 |     3 | Alicia |    136
(4 rows)

Non-Correlated Subqueries

Characteristics: A subquery queries the value and then returns the result to the outer layer for query.

Example: Query the students whose height is greater than the average height of the students in the class.

gaussdb=# SELECT t1.* 
FROM student t1,
     (SELECT grade, AVG(height) avg_hei FROM student GROUP BY grade) t2
WHERE t1.grade = t2.grade AND 
      t1.height > t2.avg_hei;
  sid  | grade |  name  | height 
-------+-------+--------+--------
 00001 |     1 | Scott  |    135
 00005 |     2 | Jordan |    130
 00006 |     2 | Bob    |    126
 00008 |     3 | Alicia |    136
(4 rows)

-- Delete.
gaussdb=# DROP TABLE student;
gaussdb=# DROP TABLE teacher;