SELECT 和 WHERE 子句中未指定列名的 Postgresql 表名或别名

Question

我有两个表:

CREATE TABLE a (id INT NOT NULL);
CREATE TABLE b (id INT NOT NULL);
INSERT INTO a VALUES (1), (2);
INSERT INTO b VALUES (1);

如果我尝试从 a 中获取记录，而 b 中有记录(查询 1):

SELECT a.id, b FROM a LEFT JOIN b on a.id = b.id WHERE b is NOT NULL;

我得到:

 id |  b  
----+-----
  1 | (1)

如果我尝试从 a 获取记录，而 b 中没有记录(查询 2):

SELECT a.id, b FROM a LEFT JOIN b on a.id = b.id WHERE b IS NULL;

我得到:

 id | b 
----+---
  2 | 

看起来还可以。

然后我修改b:

ALTER TABLE b ADD COLUMN s TEXT NULL;

然后查询 1 不返回任何行，查询 2 返回相同的行

SELECT a.id, b FROM a LEFT JOIN b on a.id = b.id;

返回

 id |  b   
----+------
  1 | (1,)
  2 | 

我的问题是:

1. 为什么 Postresql 允许在 WHERE 子句中使用表名或别名而不指定列名？
2. 结果行的 b 列中的 (1,) 是什么？
3. 为什么(1,)在查询1和查询2中不满足IS NULL和IS NOT NULL？

P.S. 如果我将表 b 更改为 ALTER TABLE b ADD COLUMN s TEXT NOT NULL DEFAULT '' 而不是查询 1 和 2 返回相同的行。

Answer 1

按问题回答:

1. 这是行构造函数，因此列中的每个值都会使用列中的值为其成员字段构建行值(复合值)
2. (1,) 是一个行构造函数，第一个成员是 1，第二个成员(您的文本字段)具有 null 值, 因此不显示任何值。
3. 您正在比较实际上满足两个比较(为空和不为空)的整行构造函数

关于第 3 点的更多信息:

select *, b is not null as b_not_null, b is null as b_null from b;

结果:

 id | b_not_null | b_null
----+------------+--------
  1 | t          | f

一行 IS NULL 当其所有成员都具有 NULL 值时，否则它 IS NOT NULL。重现:

create table rowtest ( col1 int, col2 int);
insert into rowtest values (null,null), (1,1), (null,1);

select 
  col1, col2, rowtest,
  case when rowtest is null then true else false end as rowtest_null
from rowtest;

结果:

 col1 | col2 | rowtest | rowtest_null
------+------+---------+--------------
      |      | (,)     | t
    1 |    1 | (1,1)   | f
      |    1 | (,1)    | f

---

实际上，对于您的查询，它们都可以重写为:

查询 1:从 a 获取记录并从 b

获取匹配记录

使用 INNER JOIN 实际上与 JOIN 相同:

SELECT a.id, b FROM a JOIN b on a.id = b.id;

查询 2:从 a 获取记录，但没有来自 b

的匹配记录

使用 NOT EXISTS 而不是 LEFT JOIN:

SELECT a.id 
FROM a 
WHERE NOT EXISTS (
  SELECT 1 
  FROM b
  WHERE a.id = b.id
  );

对于最后一个查询，如果你真的需要第二个空列，你可以添加一个静态值来选择这样的列表:

SELECT a.id, null as b