Why does the second query work incorrectly when merged?

Question

SQL

Hello. query

(Select c.id, Count(s.email) as kol1
From campaigns c
Left Join subscribers s on c.id = s.campaigns_id
And s.created_on between '2020-10-15' and '2020-11-01'
group by c.id)

union

(Select c1.id, Count(cd.email) as kol2
From campaigns c1
Left Join subscribers_deleted cd on c1.id = cd.campaigns_id
And cd.created_on between '2020-10-15' and ' 2020-11-01'
group by c1.id)

Separately, both queries work correctly, and when combined, the second query does not work the left join, i.e. it does not list all IDs in the left table, but only those who have a match in the right table and unit does not combine everything in 3 columns, but displays the results queries one by one

(in general, the task is to add the fields kol1 and kol2, but I haven’t figured out how to do this yet, at least output it in 3 columns and put it in exel according to the formula, at least line them up in 3 columns value)

Answer 1

You do not correctly understand what and how union is intended for.
Apparently you wanted to achieve something like this:

SELECT
    c.id,
    Count(s.email) as kol1,
    Count(cd.email) as kol2,
    Count(s.email) + Count(cd.email) as summa
From 
    campaigns c
    Left Join subscribers s on 
        c.id = s.campaigns_id
        And s.created_on between '2020-10-15' and '2020-11-01'
    Left Join subscribers_deleted cd on 
        c.id = cd.campaigns_id
        And cd.created_on between '2020-10-15' and '2020-11-01'
group by c.id

But I would do this:

SELECT
    c.id,
    (SELECT Count(s.email) FROM subscribers s WHERE 
        c.id = s.campaigns_id And s.created_on between '2020-10-15' and '2020-11-01'
    ) as kol1,
    (SELECT Count(cd.email) FROM subscribers_deleted cd WHERE
        c.id = cd.campaigns_id And cd.created_on between '2020-10-15' and '2020-11-01'
    ) as kol2
From campaigns c

Requests are not checked. there may be typos.
If at once the sum is necessary - summarize. If you need both the sum and separately, then you can make a subquery using WITH

Answer 2

Thank you! Everything worked out, I really didn’t understand how in your second option you can sum it up, but in the first one everything is ok. I don't really remember anything anymore, I'll do it. Once I studied at sql-ex, maybe something more convenient has appeared now?