Question 112200 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – UNION doesn't return all rows

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)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search