Объединение всех с запросами, которые имеют различное количество столбцов

Я столкнулся с тем случаем, когда запрос sqlite, который я ожидаю вернуть ошибку, на самом деле преуспевает, и мне было интересно, может ли кто-нибудь указать, почему этот запрос действителен.

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 *, тогда я получу ожидаемую ошибку: SELECTs to the left and right of UNION ALL do not have the same number of result columns.

Ответ 1

Ответ на это кажется простым: Да, это причуда. Я хотел бы продемонстрировать это с помощью небольшого примера. Но заранее, проконсультируйтесь с документацией:

Два или более простых оператора SELECT могут быть соединены вместе, чтобы сформировать элемент SELECT с использованием UNION, UNION ALL, INTERSECT или EXCEPT оператор. В составном SELECT все составляющие SELECT должны возвращает одинаковое количество столбцов результатов.

Таким образом, в документах четко сказано, что два SELECTs должны содержать одинаковое количество столбцов. Однако, как вы сказали, внешний SELECT странно избегает этого "ограничения".

Пример 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 просто пропущен, как указано в комментариях.

Пример 2

Мы только переключаем порядок двух операторов select.

 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 не пропускает столбец, но добавляет нулевое значение.

Заключение

Это приводит меня к выводу, что sqlite просто обрабатывает UNION ALL иначе, если он обрабатывается как подзапрос.

PS: Если вы просто используете UNION, он терпит неудачу при любом сценарии.

Ответ 2

UNION ALL вернет результаты с нулевыми значениями в дополнительных столбцах.

Базовый UNION не будет работать, потому что UNION без ALL должен иметь одинаковое количество столбцов из обеих таблиц.

Итак:

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

возвращает 3 столбца с нулями в столбце 3.

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

должен завершиться ошибкой, поскольку количество столбцов не соответствует.

В заключение вы можете добавить пустой столбец в UNION, чтобы вы выбрали 3 столбца из каждой таблицы, и они все равно будут работать.

Пример:

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