PostgreSQL-查询

检索过程或从数据库中检索数据的命令称为查询。在 SQL 中，SELECT 命令用于指定查询。SELECT 命令的一般语法是：

[WITH with_queries] SELECT select_list FROM table_expression [sort_specification]

WITH 查询是最后处理的，因为它们是一项高级功能。

表引用可以是表名（可能是模式限定的），也可以是派生表，例如子查询、JOIN 构造或这些的复杂组合。如果 FROM 子句中列出了多个表引用，则表是交叉连接的（即，形成它们行的笛卡尔积；）。FROM 列表的结果是一个中间虚拟表，然后可以通过 WHERE、GROUP BY 和 HAVING 子句进行转换，最终成为整个表表达式的结果。

当表引用命名作为表继承层次结构的父表的表时，表引用不仅会生成该表的行，还会生成其所有后代表的行，除非关键字 ONLY 位于表名之前。但是，该引用仅生成出现在命名表中的列——添加到子表中的任何列都将被忽略。

您可以在表名之后写 * 来明确指定包含后代表，而不是只在表名之前写。没有真正的理由再使用这种语法了，因为搜索后代表现在始终是默认行为。但是，它支持与旧版本的兼容性。

表连接类型

Cross join

T1 CROSS JOIN T2

对于 T1 和 T2 中的每个可能的行组合（即笛卡尔积），连接表将包含一行，该行由 T1 中的所有列和 T2 中的所有列组成。如果表分别有 N 和 M 行，则连接表将有 N * M 行。

FROM T1 CROSS JOIN T2 等价于 FROM T1 INNER JOIN T2 ON TRUE。它也相当于 FROM T1、T2。

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

单词 INNER 和 OUTER 在所有形式中都是可选的。INNER 是默认值；LEFT、RIGHT 和 FULL 表示外连接。连接条件在 ON 或 USING 子句中指定，或由单词 NATURAL 隐式指定。连接条件确定两个源表中的哪些行被认为是“匹配”的。

INNER JOIN
对于 T1 的每一行 R1，连接表对于 T2 中满足与 R1 的连接条件的每一行都有一行。

LEFT OUTER JOIN
首先，执行内连接。然后，对于 T1 中与 T2 中的任何行不满足连接条件的每一行，在 T2 的列中添加一个带有空值的连接行。因此，连接表对于 T1 中的每一行总是至少有一行。

RIGHT OUTER JOIN
首先，执行内连接。然后，对于 T2 中与 T1 中的任何行不满足连接条件的每一行，在 T1 的列中添加一个带有空值的连接行。这与左连接相反：结果表将始终为 T2 中的每一行保留一行。

FULL OUTER JOIN
首先，执行内连接。然后，对于 T1 中与 T2 中的任何行不满足连接条件的每一行，在 T2 的列中添加一个带有空值的连接行。此外，对于 T2 中与 T1 中的任何行不满足连接条件的每一行，添加一个在 T1 的列中具有空值的连接行。

ON 子句是最通用的连接条件：它采用与 WHERE 子句中使用的相同类型的布尔值表达式。如果 ON 表达式的计算结果为真，则 T1 和 T2 中的一对行匹配。

USING 子句是一种速记，它允许您利用连接双方对连接列使用相同名称的特定情况。它采用逗号分隔的共享列名称列表，并形成一个连接条件，其中包括每个列的相等比较。例如，使用 USING (a, b) 连接 T1 和 T2 会产生连接条件 ON T1.a = T2.a AND T1.b = T2.b。

最后，NATURAL 是 USING 的简写形式：它形成一个 USING 列表，其中包含出现在两个输入表中的所有列名。与 USING 一样，这些列在输出表中只出现一次。如果没有公用列名，则 NATURAL JOIN 的行为类似于 JOIN ... ON TRUE，从而产生叉积连接。

测试：

create table t1(num integer,name varchar(50));
create table t2(num integer,value varchar(50));

insert into t1 values(1,'a'),(2,'b'),(3,'c');
insert into t2 values(1,'xxx'),(3,'yyy'),(5,'zzz');

连接测试：

SELECT * FROM t1 CROSS JOIN t2;

SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;

SELECT * FROM t1 INNER JOIN t2 USING (num);

SELECT * FROM t1 NATURAL INNER JOIN t2;

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;

SELECT * FROM t1 LEFT JOIN t2 USING (num);

SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;

SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;

SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';

这是因为放置在 ON 子句中的限制是在连接之前处理的，而放置在 WHERE 子句中的限制是在连接之后处理的。这对于内部连接无关紧要，但对于外部连接却很重要。

create table mytable1(a int,b int,name varchar(50));
create table mytable2(a int,b int,value varchar(50));
insert into mytable1 values(1,1,'111'),(2,2,'222'),(3,3,'333');
insert into mytable2 values(1,1,'xxx'),(3,3,'yyy'),(5,5,'zzz');

select * from mytable1 natural inner join mytable2;

update mytable2 set b = b+1 where a=1;

natural连接默认是T1和T2表同名字段作为连接条件。

子查询可以是VALUES列表：

FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
 AS names(first, last)

表函数
表函数是产生一组行的函数，由基本数据类型（标量类型）或复合数据类型（表行）组成。它们在查询的 FROM 子句中用作表、视图或子查询。表函数返回的列可以包含在 SELECT、JOIN 或 WHERE 子句中，其方式与表、视图或子查询的列相同。

表函数也可以使用 ROWS FROM 语法组合，结果在并行列中返回；在这种情况下，结果行数是最大函数结果的行数，较小的结果用空值填充以匹配。

function_call [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]
ROWS FROM( function_call [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

如果指定了 WITH ORDINALITY 子句，则会在函数结果列中添加一个 bigint 类型的附加列。此列对函数结果集的行进行编号，从 1 开始。（这是对 UNNEST ... WITH ORDINALITY 的 SQL 标准语法的概括。）默认情况下，序数列称为 ordinality，但列名不同可以使用 AS 子句分配给它。

可以使用任意数量的数组参数调用特殊表函数 UNNEST，它会返回相应数量的列，就像在每个参数上分别调用 UNNEST并使用 ROWS FROM 构造组合一样。

UNNEST( array_expression [, ... ] ) [WITH ORDINALITY] [[AS] table_alias [(column_alias [, ... ])]]

如果没有指定table_alias，则使用函数名作为表名；在 ROWS FROM() 构造的情况下，使用第一个函数的名称。

如果未提供列别名，则对于返回基本数据类型的函数，列名也与函数名相同。对于返回复合类型的函数，结果列获取该类型的各个属性的名称。

举个栗子：

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
      SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
WHERE foosubid IN (
                    SELECT foosubid
                    FROM getfoo(foo.fooid) z
                    WHERE z.fooid = foo.fooid
                  );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

在某些情况下，定义可以根据调用方式返回不同列集的表函数很有用。为了支持这一点，表函数可以声明为返回没有 OUT 参数的伪类型记录。当在查询中使用这样的函数时，必须在查询本身中指定预期的行结构，以便系统知道如何解析和计划查询。此语法如下所示：

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )

例子：

SELECT *
FROM ROWS FROM
    (
        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
            AS (a INTEGER, b TEXT),
        generate_series(1, 3)
    ) AS x (p, q, s)
ORDER BY p;

LATERAL子查询

出现在 FROM 中的子查询前面可以有关键字 LATERAL。这允许他们引用前面的 FROM 项提供的列。（没有 LATERAL，每个子查询都是独立评估的，因此不能交叉引用任何其他 FROM 项。）

FROM 中出现的表函数也可以在关键字 LATERAL 之前，但对于函数，关键字是可选的；在任何情况下，函数的参数都可以包含对前面 FROM 项提供的列的引用。

LATERAL 项可以出现在 FROM 列表的顶层，或者出现在 JOIN 树中。在后一种情况下，它还可以引用位于右侧的 JOIN 左侧的任何项目。

当 FROM 项目包含 LATERAL 交叉引用时，评估过程如下：对于提供交叉引用列的 FROM 项目的每一行，或提供列的多个 FROM 项目的行集，使用 LATERAL 项目评估该行或行集的列值。结果行像往常一样与计算它们的行连接。对列源表中的每一行或每组行重复此操作。

LATERAL 主要用于计算要连接的行需要交叉引用的列时。一个常见的应用是为一个集合返回函数提供一个参数值。例如，假设 vertices(polygon) 返回多边形的顶点集，我们可以识别存储在表中的多边形的靠近在一起的顶点：

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

等价于

SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

LATERAL子查询的主要应用是获取from后面LATERAL之前的表名的字段。

分组集

使用分组集的概念可以实现比上述更复杂的分组操作。FROM 和 WHERE 子句选择的数据按每个指定的分组集单独分组，为每个组计算聚合，就像简单的 GROUP BY 子句一样，然后返回结果。例如：

 create table items_sold(
  brand varchar(30),
  size char(1),
  sales numeric
  );

  insert into items_sold values('Foo','L',10),('Foo','M',20),('Bar','M',15),('Bar','L',5);

 SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());

GROUPING SETS 的每个子列表可以指定零个或多个列或表达式，其解释方式与直接在 GROUP BY 子句中的方式相同。一个空的分组集意味着所有的行都被聚合到一个单独的组中（即使没有输入行也会被输出），正如上面对于没有 GROUP BY 子句的聚合函数的情况所描述的那样。GROUPING SETS将后面指定的表达式依次分组。

ROLLUP ( e1, e2, e3, ... )

相当于

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 ),
    ( e1 ),
    ( )
)

还有：

CUBE ( e1, e2, ... )

相当于

GROUPING SETS (
      ( a, b, c ),
      ( a, b    ),
      ( a,    c ),
      ( a       ),
      (    b, c ),
      (    b    ),
      (       c ),
      (         )
  )

集合运算
可以使用集合运算并集、交集和差集来组合两个查询的结果。语法是

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

UNION 有效地将 query2 的结果附加到 query1 的结果（尽管不能保证这是实际返回行的顺序）。此外，它以与 DISTINCT 相同的方式从其结果中消除重复行，除非使用 UNION ALL。

INTERSECT 返回同时在 query1 的结果和 query2 的结果中的所有行。除非使用 INTERSECT ALL，否则会消除重复行。

EXCEPT 返回在 query1 结果中但不在 query2 结果中的所有行。（这有时称为两个查询之间的差异。）同样，除非使用 EXCEPT ALL，否则会消除重复项。

为了计算两个查询的并集、交集或差异，这两个查询必须是“并集兼容”的，这意味着它们返回相同数量的列并且对应的列具有兼容的数据类型。

可以组合集合操作，例如

query1 UNION query2 EXCEPT query3

等价于

(query1 UNION query2) EXCEPT query3

如此处所示，您可以使用括号来控制评估的顺序。如果没有括号，UNION 和 EXCEPT 从左到右关联，但 INTERSECT 比这两个运算符绑定得更紧密。因此

query1 UNION query2 INTERSECT query3

等价于

query1 UNION (query2 INTERSECT query3)

排序

SELECT select_list
FROM table_expression
ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
         [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

NULLS FIRST 和 NULLS LAST 选项可用于确定在排序顺序中空值是出现在非空值之前还是之后。默认情况下，空值排序为好像大于任何非空值；也就是说，NULLS FIRST 是 DESC 顺序的默认值，否则 NULLS LAST。

LIMIT 和 OFFSET
LIMIT 和 OFFSET 允许您仅检索由其余查询生成的行的一部分：

SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]

如果给定了限制计数，则返回的行数不会超过该数量（但如果查询本身产生的行数较少，则可能会更少）。LIMIT ALL 与省略 LIMIT 子句相同，与带有 NULL 参数的 LIMIT 一样。OFFSET 表示在开始返回行之前跳过那么多行。OFFSET 0 与省略 OFFSET 子句相同，带有 NULL 参数的 OFFSET 也是如此。如果同时出现 OFFSET 和 LIMIT，则在开始计算返回的 LIMIT 行之前会跳过 OFFSET 行。

使用 LIMIT 时，使用 ORDER BY 子句将结果行约束为唯一顺序很重要。否则，您将获得查询行的不可预测的子集。您可能要求第 10 到第 20 行，但第 10 到第 20 行的顺序是什么？排序是未知的，除非您指定了 ORDER BY。

VALUES列表

VALUES 提供了一种生成“常量表”的方法，该表可以在查询中使用，而无需实际创建和填充磁盘上的表。语法是

VALUES ( expression [, ...] ) [, ...]

每个带括号的表达式列表在表中生成一行。列表必须具有相同数量的元素（即表中的列数），并且每个列表中的相应条目必须具有兼容的数据类型。分配给结果每一列的实际数据类型是使用与 UNION 相同的规则确定的。

例如：

VALUES (1, 'one'), (2, 'two'), (3, 'three');

等价于：

SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';

再如：

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);

with查询
WITH 提供了一种编写辅助语句以用于更大查询的方法。这些语句，通常被称为公用表表达式或 CTE，可以被认为是定义了只为一个查询而存在的临时表。WITH 子句中的每个辅助语句都可以是 SELECT、INSERT、UPDATE 或 DELETE；WITH 子句本身附加到也可以是 SELECT、INSERT、UPDATE 或 DELETE 的主语句。

WITH 中 SELECT 的基本价值是将复杂的查询分解为更简单的部分。一个例子是：

WITH regional_sales AS (
      SELECT region, SUM(amount) AS total_sales
      FROM orders
      GROUP BY region
  ), top_regions AS (
      SELECT region
      FROM regional_sales
      WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
  )
  SELECT region,
         product,
         SUM(quantity) AS product_units,
         SUM(amount) AS product_sales
  FROM orders
  WHERE region IN (SELECT region FROM top_regions)
  GROUP BY region, product;

递归查询

可选的 RECURSIVE 修饰符将 WITH 从单纯的语法便利性更改为完成标准 SQL 中不可能完成的事情的功能。使用 RECURSIVE，WITH 查询可以引用它自己的输出。一个非常简单的示例是对 1 到 100 的整数求和的查询：

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

递归 WITH 查询的一般形式始终是非递归项，然后是 UNION（或 UNION ALL），然后是递归项，其中只有递归项可以包含对查询自身输出的引用。这样的查询执行如下：

递归查询评估

1.评估非递归项。对于 UNION（但不是 UNION ALL），丢弃重复的行。在递归查询的结果中包含所有剩余的行，并将它们放在临时工作表中。

2.只要工作表不为空，重复以下步骤：

a.评估递归项，将工作表的当前内容替换为递归自引用。对于 UNION（但不是 UNION ALL），丢弃重复的行和与任何先前结果行重复的行。在递归查询的结果中包括所有剩余的行，并将它们放在临时中间表中。

b.用中间表的内容替换工作表的内容，然后清空中间表。

WITH 查询使用materialized

WITH 查询的一个有用属性是，它们通常在每次执行父查询时只评估一次，即使它们被父查询或同级 WITH 查询多次引用。因此，可以将在多个地方需要的昂贵计算放在 WITH 查询中以避免冗余工作。另一个可能的应用是防止对具有副作用的函数进行不必要的多次评估。然而，另一面是优化器无法将父查询的限制下推到多重引用的 WITH 查询中，因为这可能会影响 WITH 查询输出的所有使用，而它应该只影响一个。多次引用的 WITH 查询将被评估为已写入，而不会抑制父查询之后可能丢弃的行。（但是，如上所述，如果对查询的引用只需要有限数量的行，则评估可能会提前停止。）

但是，如果 WITH 查询是非递归且无副作用的（即，它是一个不包含 volatile 函数的 SELECT），则可以将其折叠到父查询中，从而允许对两个查询级别进行联合优化。默认情况下，如果父查询仅引用一次 WITH 查询，则会发生这种情况，但如果它多次引用 WITH 查询则不会。您可以通过指定 MATERIALIZED 来强制单独计算 WITH 查询，或指定 NOT MATERIALIZED 以强制将其合并到父查询中来覆盖该决定。后一种选择有重复计算 WITH 查询的风险，但如果每次使用 WITH 查询只需要 WITH 查询完整输出的一小部分，它仍然可以节省净成本。

explain with query_qith as  materialized  (select * from mytable1)
select * from query_qith;

explain with query_qith as not  materialized  (select * from mytable1)
select * from query_qith;

with NOT MATERIALIZED：不使用物化，允许外面条件推进去，但是存在子查询被多次执行的情况。
with MATERIALIZED：使用物化，不允许外面条件推进去，但是可以保证子查询只被执行一遍，适用于子查询本身成本很高的情况。

您可以在 WITH 中使用数据修改语句（INSERT、UPDATE 或 DELETE）。这允许您在同一个查询中执行几个不同的操作。一个例子是：

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

此查询有效地将行从 products 移动到 products_log。WITH 中的 DELETE 从产品中删除指定的行，通过其 RETURNING 子句返回它们的内容；然后主查询读取该输出并将其插入 products_log。

上面示例的一个优点是 WITH 子句附加到 INSERT，而不是 INSERT 中的子 SELECT。这是必要的，因为数据修改语句只允许在附加到顶级语句的 WITH 子句中。

WITH 中的数据修改语句通常具有 RETURNING 子句，如上例所示。它是 RETURNING 子句的输出，而不是数据修改语句的目标表，它形成了可供查询的其余部分引用的临时表。如果 WITH 中的数据修改语句缺少 RETURNING 子句，则它不会形成临时表并且不能在查询的其余部分中引用。尽管如此，这样的语句仍将被执行。一个不是特别有用的例子是：

WITH t AS (
      DELETE FROM foo
  )
  DELETE FROM bar;

此示例将从表 foo 和 bar 中删除所有行。报告给客户端的受影响行数将仅包括从 bar 中删除的行。

不允许在数据修改语句中进行递归自引用。

WITH 中的数据修改语句只执行一次，并且总是完成，与主查询是否读取所有（或实际上任何）输出无关。

WITH 中的子语句彼此同时执行，并与主查询同时执行。因此，当在 WITH 中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用同一个快照执行，因此它们无法“看到”彼此对目标表的影响。这减轻了行更新的实际顺序的不可预测性的影响，并且意味着返回数据是在不同的 WITH 子语句和主查询之间传达更改的唯一方法。这方面的一个例子是

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

外部 SELECT 将在 UPDATE 操作之前返回原始价格,

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

外部 SELECT 将返回更新的数据。

不支持在单个语句中尝试两次更新同一行。

目前，在 WITH 中用作数据修改语句目标的任何表都不能有条件规则，也不能有 ALSO 规则，也不能有扩展到多个语句的 INSTEAD 规则。

with test_mytable as(
insert into mytable(f1,f2,f3) values(1,10,'test') returning *)
select * from test_mytable;