Why does Postgres choose different data solely based on columns selected?-CodePudding

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.

CodePudding user response：

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.