OK, thanks for the fast answer! my confusion "UNION ALL" fixes the "issue" -------- Original post My understanding is that a union of several selects should return as many records as all them together. As a trivial example if select1 and select2 return each one record select1 union select2 should return two records. but in following example using MAX only one record is returned instead of two CREATE TABLE A (id1 , id2 ); CREATE TABLE B (id1 , id2 ); INSERT INTO A VALUES(100,200); INSERT INTO B VALUES(100,200); SELECT MAX(id1) AS na FROM A UNION SELECT MAX(id1) AS na FROM B ; when different column are requested (id1 and id2) or the maximum values of the same column turn to be different then the result of the union is correct (two records) this is summarized in the example below CREATE TABLE A (id1 , id2 ); CREATE TABLE B (id1 , id2 ); INSERT INTO A VALUES(100,200); INSERT INTO B VALUES(100,200); SELECT "** FIRST union"; SELECT MAX(id1) FROM A UNION SELECT MAX(id1) FROM B ; SELECT "** SECOND union"; SELECT MAX(id1) FROM A UNION SELECT MAX(id2) FROM B ; INSERT INTO B VALUES(300,400); SELECT "** THIRD union"; SELECT MAX(id1) FROM A UNION SELECT MAX(id1) FROM B ; being the output ** FIRST union 100 ** SECOND union 100 200 ** THIRD union 100 300 maybe I am missing something, but I think that the result of the first union is wrong. This behavior is present in old sqlite versions (3.2.1) until now 3.34