Question 20880 in Mysql Question posted in
The official MySQL documentation can be found here.

Union as sub query using MySQL 8

I’m wanting to optimize a query using a union as a sub query.

Im not really sure how to construct the query though.

I’m using MYSQL 8.0.12

Here is the original query:

enter image description here

---------------
| c1     | c2 |
---------------
| 18182  | 0  |
| 18015  | 0  |
---------------
2 rows in set (0.35 sec)

I’m sorry but the question doesn’t stored if I paste the sql query as text and format using ctrl+k

Output expected

---------------
| c1    | c2  |
---------------
| 18182 | 167 |
| 18015 | 0   |
---------------

As a output I would like to have the difference of rows between the two tables in UNION ALL.

I processed this question using the wizard https://stackoverflow.com/questions/ask

Tags:

2

Answers


  1. 0

    There are several ways to go for this, including UNION, but I wouldn’t recommend it, as it is IMO a bit ‘hacky’. Instead, I suggest you use subqueries or use CTEs.

    With subqueries

    SELECT
    ABS(c_tbl_aaa.size - c_tbl_bbb.size) as diff
FROM (
        SELECT
           COUNT(*) as size
        FROM tbl_aaa
    ) c_tbl_aaa
CROSS JOIN (
        SELECT
           COUNT(*) as size
        FROM tbl_bbb
    ) c_tbl_bbb

    With CTEs, also known as WITHs

    WITH c_tbl_aaa AS (
    SELECT
       COUNT(*) as size
    FROM tbl_aaa
), c_tbl_bbb AS (
    SELECT
       COUNT(*) as size
    FROM tbl_bbb
) 
SELECT
    ABS(c_tbl_aaa.size - c_tbl_bbb.size) as diff
FROM c_tbl_aaa
    CROSS JOIN c_tbl_bbb

    In a practical sense, they are the same. Depending on the needs, you might want to define and join the results though, and in said cases, you could use a single number as a "pseudo id" in the select statement.

    Since you only want to know the differences, I used the ABS function, which returns the absolute value of a number.

    Let me know if you want a solution with UNIONs anyway.

    Edit: As @Rick James pointed out, COUNT(*) should be used in the subqueries to count the number of rows, as COUNT(id_***) will only count the rows with non-null values in that field.

    Login or Signup to reply.
  2. 0

    Since a parenthesized SELECT can be used almost anywhere a expression can go:

    SELECT
    ABS( (SELECT COUNT(*) FROM tbl_aaa) -
         (SELECT COUNT(*) FROM tbl_bbb) ) AS diff;

    Also, MySQL is happy to allow a SELECT without a FROM.

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

Back To Top
Search