将all与具有不同列数的查询联合起来-unionallwithqueriesthathaveadifferentnumberofcolumns

10  

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:

答案似乎很简单：是的，这是一个怪癖。我想用一个简短的例子来证明这一点。但事先，让我们查阅文档：

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.

可以使用UNION，UNION ALL，INTERSECT或EXCEPT运算符将两个或多个简单SELECT语句连接在一起以形成复合SELECT。在复合SELECT中，所有组成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'.

因此，文档非常清楚地说明两个SELECT必须提供相同数量的列。但是，正如你所说，最外面的SELECT奇怪地避免了这种“限制”。

Example 1

例1

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

Result:

结果：

k|v
4|5
4|5

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

正如评论中所指出的那样，第三列rowid被简单地省略了。

Example 2

例2

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

我们只是切换两个select语句的顺序。

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

Result

结果

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

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

现在，sqlite不会省略列但添加空值。

Conclusion

结论

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

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

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

PS：如果您只是使用UNION，它在任何情况下都会失败。

8  

UNION ALL will return the results with null values in the extra columns.

UNION ALL将在额外列中返回带有空值的结果。

A basic UNION will fail because UNION without the ALL has to have the same number of columns from both tables.

基本UNION将失败，因为没有ALL的UNION必须具有来自两个表的相同列数。

So:

所以：

SELECT column1, column2 FROM table a
UNION ALL 
SELECT column1, column2, column3 FROM table b

returns 3 columns with nulls in column 3.

返回3列中带有空值的3列。

and:

和：

SELECT column1, column2 FROM table a
UNION 
SELECT column1, column2, column3 FROM table b

should fail because the number of columns do not match.

应该失败，因为列数不匹配。

In conclusion you could add a blank column to the UNION so that you are selecting 3 columns from each table and it would still work.

总之，您可以向UNION添加一个空白列，以便从每个表中选择3列，它仍然可以工作。

EX:

EX：

SELECT column1, column2, '' AS column3 FROM table a
UNION  
SELECT column1, column2, column3 FROM table b