将所有与具有不同列数的查询联合起来 [英] union all with queries that have a different number of columns

问题描述

我遇到过一个我希望返回错误的 sqlite 查询实际上成功的情况，我想知道是否有人可以指出为什么这个查询是有效的.

I've run into a case where a sqlite query I'm expecting to return an error is actually succeeding and I was wondering if anyone could point out why this query is valid.

CREATE TABLE test_table(
  k INTEGER,
  v INTEGER
);

INSERT INTO test_table( k, v ) VALUES( 4, 5 );

SELECT * FROM(
  SELECT * FROM(
    SELECT k, v FROM test_table WHERE 1 = 0
  )
  UNION ALL
  SELECT * FROM(
    SELECT rowid, k, v FROM test_table
  )
)

上面的sqlfiddle

我认为合并具有不同列数的两个选择会返回错误.如果我删除最外面的 SELECT * 然后我会收到我期望的错误:UNION ALL 左侧和右侧的 SELECTs 没有相同数量的结果列.

I would think that unioning two selects which have a different number of columns would return an error. If I remove the outermost SELECT * then I receive the error I'm expecting: SELECTs to the left and right of UNION ALL do not have the same number of result columns.

推荐答案

这个问题的答案似乎很简单:是的，这是一个怪癖.我想用一个简短的例子来证明这一点.但在此之前，让我们查阅​​文档:

The answer to this seems to be straightforward: Yes, this is a quirk. I'd like to demonstrate this with a short example. But beforehand, let's consult the documentation:

两个或多个简单的 SELECT 语句可以连接在一起形成使用 UNION、UNION ALL、INTERSECT 或 EXCEPT 的复合 SELECT运营商.在复合 SELECT 中，所有组成的 SELECT 必须返回相同数量的结果列.

Two or more simple SELECT statements may be connected together to form a compound SELECT using the UNION, UNION ALL, INTERSECT or EXCEPT operator. In a compound SELECT, all the constituent SELECTs must return the same number of result columns.

所以文档非常清楚地说明两个 SELECTs 必须提供相同数量的列.但是，正如您所说，最外面的 SELECT 奇怪地避免了这种限制".

So the documentations says very clearly that two SELECTs must provide the same number of columns. However, as you said, the outermost SELECT strangely avoids this 'limitation'.

示例 1

SELECT * FROM(
    SELECT k, v FROM test_table
  UNION ALL
    SELECT k, v,rowid FROM test_table
);

结果:

k|v
4|5
4|5

如注释中指出的那样，第三列 rowid 被简单地省略了.

The third column rowid gets simply omitted, as pointed out in the comments.

示例 2

我们只是交换了两个 select 语句的顺序.

We are only switching the order of the two select statements.

 SELECT * FROM(
    SELECT k, v, rowid FROM test_table
  UNION ALL
     SELECT k, v FROM test_table
  );

结果

k|v|rowid
4|5|1
4|5|

现在，sqlite 不会省略列而是添加一个空值.

Now, sqlite does not omit the column but add a null value.

结论

这使我得出结论，如果将 UNION ALL 作为子查询处理，sqlite 只是简单地处理它.

This brings me to my conclusion, that sqlite simply handles the UNION ALL differently if it is processed as a subquery.

PS:如果你只是使用 UNION 它在任何情况下都会失败.

PS: If you are just using UNION it fails at any scenario.

这篇关于将所有与具有不同列数的查询联合起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！