UNION doesn't return all rows-CodePudding

My head is about to explode.. For some reason mysql doesn't want to return all rows from a UNION statement.

If I use

SELECT `full_address` FROM `x_servers` WHERE `service_type` IN ('boost','testboost')

It returns 51 rows, while if I use

(SELECT `full_address` FROM `x_servers` WHERE `service_type` = 'boost') UNION (SELECT `full_address` FROM `x_servers` WHERE `service_type` = 'testboost')

It gives 25.

When I use them seperately,

SELECT `full_address` FROM `x_servers` WHERE `service_type` = 'boost'

returns 31 rows and

SELECT `full_address` FROM `x_servers` WHERE `service_type` = 'testboost'

returns 20

What is a possible problem? I cannot see it.

Server: MySQL (Localhost via UNIX socket) Server type: MySQL Server version: 8.0.30-0ubuntu0.20.04.2 - (Ubuntu)

CodePudding user response：

IIRC SQL's UNION removes duplicates. So when using it with rows that have same full_address only one of the duplicates will show up in the final output. But with a normal SQL SELECT duplicates won't be removed (Unless you use DISTINCT)

CodePudding user response：

Apparently that was a phpMyAdmin issue. For some reason all it showed was: Showing rows 0 - 24 (25 total, Query took 0.0011 seconds.) And there were no other pages to go to. It normally shows other pages if they don't fit within the 25 rows limit but it didn't this time. It also did not show that there's more that 25 rows in total as it said "25 total". But once I pressed "show all" it showed the rest of the query.