CODEWITHSUNDEEP
mysql
George A. Custer
George A. Custer

Reputation: 121

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

Upvotes: 0

Views: 86

Answers (2)

Rick James
Rick James

Reputation: 142218

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.

Upvotes: 4

Jetto Martínez
Jetto Martínez

Reputation: 999

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.

Upvotes: 3

Related Questions