PostgreSQL学习之子查询

 一、子查询简介

        子查询也称为内部查询或嵌套查询，指的是在PostgreSQL查询中的WHERE字句中嵌入查询语句一个SELECT语句的查询结果能够作为另一个语句的输入值。子查询可以与SELECT、INSERT，UPDATE和DELETE语句一起使用，并可以使用运算符例如=，< , > ，IN，BETWEEN等。

在使用子查询时必须要遵循几个规则：

1. 子查询必须用括号括起来。
2. 子查询在select字句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
3. ORDER BY不能用在字句查询中，虽然主查询可以使用ORDER BY。可以在子查询中使用GROUP BY，其功能与ORDER BY相同。
4. 子查询返回多于一行，只能与多值运算符一起使用，如IN运算符。
5. BETWEEN运算符不能与子查询一起使用，但是，BETWEEN可在子查询内使用。
6. 在WHERE子句中不可以使用函数。
7. 原则上子查询必须设定名称。

 标量子查询有一个特殊的限制，那就是必须而且只能返回 1 行 1 列的结果，也就是返回表中某一行的某一列的值。

二、子查询用法 

子查询返回多数据时一个数据的范围，在WHERE子句中通过数据范围进行筛选时，需用操作符IN、ANY、ALL 

1、SELECT 语句中的子查询使用 

SELECT * FROM COMPANY 
WHERE ID IN 
      (  SELECT ID 
         FROM COMPANY  
         WHERE SALARY > 45000
       ) ;

2、INSERT 语句中的子查询使用

子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。

在子查询中所选择的数据可以用任何字符、日期或数字函数修改。

INSERT INTO COMPANY_BKP 
SELECT * FROM COMPANY  
WHERE ID IN (
         SELECT ID 
         FROM COMPANY
) ;

3、UPDATE 语句中的子查询使用

子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新。

UPDATE COMPANY 
SET salary = salary * 0.50 
WHERE AGE IN (
          SELECT AGE 
          FROM COMPANY_BKP 
          WHERE AGE >= 27 
);

4、DELETE 语句中的子查询使用

DELETE FROM COMPANY 
WHERE AGE IN (
          SELECT AGE 
          FROM COMPANY_BKP 
          WHERE AGE > 27 
);

5、在HAVING子句中使用子查询

在HAVING子句中使用子查询，即对分组进行过滤，子查询往往返回的都是一个具体的数据（单行单列）

SELECT id ,avg(salary) FROM table
GROUP BY id
HAVING avg(salary) > (
        SELECT avg(salary) 
        FROM table
);

三、关联子查询

        关联子查询：子查询不可独立执行，子查询中使用主查询的列作为条件。先执行外部查询，将外部查询出的每条数据传递给子查询语句执行，子查询执行一次返回执行结果后，主查询根据子查询结果进行决策。

• 关联子查询会在细分的组内进行比较时使用。
• 关联子查询和GROUP BY子句一样，也可以对表中的数据进行切分。
• 关联子查询的结合条件如果未出现在子查询之中就会发生错误。
• 在细分的组内进行比较时，需要使用关联子查询。

SELECT product_type, product_name, sale_price
FROM Product AS P1 
WHERE sale_price > (
                    SELECT AVG(sale_price)
                    FROM Product AS P2 
                    WHERE P1.product_type = P2.product_type
                    GROUP BY product_type
);
-- 在同一商品种类中对各商品的销售单价和平均单价进行比较

 注意：Ⅰ、结合条件一定要写在子查询中！
            Ⅱ、子查询内部设定的关联名称，只能在该子查询内部使用。

1、在细分的组内进行比较

SELECT * FROM table1
WHERE salary > (
        SELECT avg(salary) 
        FROM table1 
        GROUP BY id
);
# 错误写法
# 在子查询中查每个部门的平均工资，但是是多行多列的结果。
# WHERE条件后，当子查询结果为单行单列时，才能进行正常比较。

SELECT * FROM table1 t1
WHERE salary > (
        SELECT avg(salary) 
        FROM table1 t2 
        WHERE t1.id=t2.id
);
#正确写法

 2、使用EXISTS,NOT EXISTS时使用关联子查询

SELECT * FROM customer t1
WHERE not exists (
          SELECT customer_id FROM order t2
          WHERE t1.customer_id = t2.customer_id
);

注：在使用EXISTS子查询时，常见的做法是在子查询中使用SELECT 1作为子查询的选择列表。它的作用是为了提高查询性能。

EXISTS运算符不关心子查询的选择列表的具体内容，它只关心子查询是否返回了至少一行结果。因此，为了减少对性能的影响，通常在EXISTS子查询中使用SELECT 1来代表一行数据。这是因为查询一个固定值1比查询具体的数据列要更高效，它避免了查询不必要的列数据，从而减少了工作量和资源消耗。

实际上，你也可以使用其他任何固定的非空值代替1，例如SELECT 'dummy'或SELECT '*'等。选择什么具体值并不重要，它们只是一个占位符，用于满足语法的要求，而不会影响查询结果。

所以，在使用EXISTS子查询时，通常推荐使用SELECT 1，这是一种常见的最佳实践。

SELECT *
FROM table t
WHERE EXISTS (
              SELECT 1
              FROM your_other_table ot
              WHERE ot.id = t.id
              AND ot.id IN (1, 2, 3, 4, 5, 6, 7, 99)
);