SQLite Forum

UNION of selects returning wrong result (SOLVED)
Login

UNION of selects returning wrong result (SOLVED)

(1.1) By alesXala on 2021-01-17 18:24:01 edited from 1.0 [source]

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

(2) By Keith Medcalf (kmedcalf) on 2021-01-17 18:13:51 in reply to 1.0 [link] [source]

The results are correct.

UNION returns only distinct results.

UNION ALL returns all results.