1

I'm running two different queries with two unions each inside a subquery:

Both queries

So the structure is:

SELECT *
FROM (subquery_1
UNION SELECT subquery_2)

Now, if I perform the query on the left, I get this result: Query #1 Result

However, the query on the right returns this result: Query #2 Result

How are the results differing even though the conditions have not changed in either query, and the only difference was one of the selected columns in a subquery?

This is very counter-intuitive.

forpas
  • 160,666
  • 10
  • 38
  • 76
Mohammad Ayoub
  • 379
  • 2
  • 9
  • 1
    Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 –  Apr 03 '22 at 14:48
  • 1
    Though I generally agree with @a_horse_with_no_name about the images in questions, the 1st image in this question which presents the 2 queries side by side by highlighting their differences, is the best way to describe the problem. – forpas Apr 03 '22 at 15:06

1 Answers1

2

The operator UNION removes duplicate rows from the returned resultset.

Removing a column from the SELECT statement may produce duplicate rows that would not exist if the removed column was there.

Try UNION ALL instead, which will return in any case all the rows of the unioned queries.

See a simplified demo.

forpas
  • 160,666
  • 10
  • 38
  • 76