Grouping certain columns on a union on 2 tables returns one of the two randomly-CodePudding

TABLE 1

A	B	C
2021-01-01 01:00:00	1	20
2021-01-01 01:00:00	2	22
2021-01-01 02:00:00	1	20
2021-01-01 03:00:00	6	20
2021-01-02 00:00:00	3	22

TABLE 2

A	B	C
2021-01-01	2	20
2021-01-01	9	22

I have 2 very big tables in the given format with A and C as their primary keys. Every time I run the following query, I get one of the two union rows randomly instead of getting the first one just like in the example. Why does it behave that way and how can I change the query so that I will always get the first row in the union result unless table1 doesn't have that date? (using only union and no joins)

SELECT * FROM (
  (
  SELECT Date(A) as 'Date', SUM(B) as 'B', C FROM TABLE1
    WHERE Date(A) = '2021-01-01'
    AND C = 20
    GROUP BY A, C
  ) 
UNION
  (
  SELECT A, SUM(B) as 'B', C FROM TABLE2
    WHERE A = '2021-01-01'
    AND C = 20
    GROUP BY A, C
  )
)d
GROUP BY d.Date , d.C;

UNION RESULT

Date	B	C
2021-01-01	8	20
2021-01-01	2	20

QUERY RESULT

A	B	C
2021-01-01	8	20

CodePudding user response：

You could avoid it like this

SELECT * FROM (
  (
  SELECT Date(A) as 'Date', SUM(B) as 'B', C, 1 AS 'Sort' FROM TABLE1
    WHERE Date(A) = '2021-01-01'
    AND C = 20
    GROUP BY A, C
  ) 
UNION
  (
  SELECT A, SUM(B) as 'B', C, 2 FROM TABLE2
    WHERE A = '2021-01-01'
    AND C = 20
    GROUP BY A, C
  )
) d
ORDER BY d.Sort
GROUP BY d.Date , d.C;

CodePudding user response：

You can just add a condition that the select query on the second table should only return records if the 1st table doesn't return anything for your where condition

SELECT Date(A) as 'Date', SUM(B) as 'B', C
FROM t1
WHERE Date(A) = '2021-01-01' AND 
      C = 20 
GROUP BY Date(A), C

UNION

SELECT A, SUM(B) as 'B', C
FROM t2
WHERE A = '2021-01-01' AND  
      C = 20 AND
      (select count(*) from t1 where Date(A) = '2021-01-01' AND  C = 20) = 0
GROUP BY A, C;