在PostgreSQL中使用EXCEPT子句 [英] Using EXCEPT clause in PostgreSQL

问题描述

我正在尝试使用 EXCEPT 子句从表中检索数据。我想从 table1 中获取所有行，但 table2 中存在的行除外。
据我所知，以下操作无效：

I am trying to use the EXCEPT clause to retrieve data from table. I want to get all the rows from table1 except the one's that exist in table2. As far I understand, the following would not work:

CREATE TABLE table1(pk_id int, fk_id_tbl2 int);
CREATE TABLE table2(pk_id int);

Select fk_id_tbl2
FROM table1
Except
Select pk_id
FROM table2

我可以使用 EXCEPT 的唯一方法似乎是从相同的表中选择或选择具有相同列名的列

The only way I can use EXCEPT seems to be to select from the same tables or select columns that have the same column name from different tables.

有人可以解释如何最好地使用解释子句吗？

Can someone please explain how best to use the explain clause?

推荐答案

您的查询似乎完全有效：

Your query seems perfectly valid:

SELECT fk_id_tbl2 AS some_name
FROM   table1
EXCEPT  -- you may want to use EXCEPT ALL
SELECT pk_id
FROM   table2;

列 名称 与查询无关。仅 数据类型 必须匹配。您的查询的输出列名称为 fk_id_tbl2 ，仅因为它是第一个 SELECT 中的列名称。您可以使用任何别名。

Column names are irrelevant to the query. Only data types must match. The output column name of your query is fk_id_tbl2, just because it's the column name in the first SELECT. You can use any alias.

经常被忽略的地方： EXCEPT （折叠重复项）和 EXCEPT ALL -保留所有不匹配的行。
更多说明和其他操作方法，更灵活一些：

What's often overlooked: the subtle differences between EXCEPT (which folds duplicates) and EXCEPT ALL - which keeps all individual unmatched rows. More explanation and other ways to do the same, some of them much more flexible:

• 选择其他表中不存在的行

• Select rows which are not present in other table

手册中 EXCEPT 的详细信息。

Details for EXCEPT in the manual.

这篇关于在PostgreSQL中使用EXCEPT子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！