JOIN two SELECT without UNION-CodePudding

It needs to download the ID, name and surname of those who have registered by the deadline and have an identity card (with the relevant conditions) or passport (with the relevant conditions). ID card and passport are two separate tables.

I have made the SQL queries in UNION format and it works:

select distinct p.id, p.name, p.surname from persons.person p 
join persons.documents d on d.person_id = p.id
join persons.id_card idd on d.id_card_id = idd.id
join persons.id_card_to_registration ir on idd.id = ir.id_card
join registrations.registration r on ir.registration_id = r.id
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and p.registration_id = r.id
and ir.status in (0,5)
UNION
select distinct p.id, p.name, p.surname from persons.person p 
join persons.documents d on d.person_id = p.id
join persons.passport pass on d.passport_id = pass.id
join persons.passport_country pc on pc.id = pass_country_id
join persons.passport_to_registration pr on pass.id = pr.passport_id 
join registrations.registration r on pr.registration_id = r.id
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and p.registration_id = r.id
and pc.zone in (0,1) or (pc.zone is null and pass.safe = true);

I would now like to do this SQL in one query without union and unfortunately it doesn't work for me - I tried to do it like this:

select distinct p.id, p.name, p.surname from persons.person p 
join persons.documents d on d.person_id = p.id
left join persons.id_card idd on d.id_card_id = idd.id
left join persons.id_card_to_registration ir on idd.id = ir.id_card
left join persons.passport pass on d.passport_id = pass.id
left join persons.passport_country pc on pc.id = pass_country_id
left join persons.passport_to_registration pr on pass.id = pr.passport_id 
join registrations.registration r on ir.registration_id = r.id
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and p.registration_id = r.id
and (ir.status in (0,5) or ir.status is null)
and pc.zone in (0,1) or (pc.zone is null and pass.safe = true)

And it doesn't return any records to me. I would like some advice on what error I have made. And is it possible to create such a query without union?

CodePudding user response：

The SQL in clause may help to filter by each condition. This may be the way to avoid the union clause, please let me know if this works for you:

select distinct p.id, p.name, p.surname from persons.person p 
where p.created_at >= '2022-01-01'
and p.created_at <= '2022-03-30'
and ((p.id in 
        (select distinct p2.id from persons.person p2 
            d.person_id from persons.documents d on d.person_id = p2.id
            join persons.id_card idd on d.id_card_id = idd.id
            join persons.id_card_to_registration ir on idd.id = ir.id_card
            join registrations.registration r on ir.registration_id = r.id
            where p2.registration_id = r.id
            and ir.status in (0,5)
        )
) or (
    (p.id in 
        (select distinct p3.id from persons.person p3
            d.person_id from persons.documents d on d.person_id = p3.id
            join persons.passport pass on d.passport_id = pass.id
            join persons.passport_country pc on pc.id = pass_country_id
            join persons.passport_to_registration pr on pass.id = pr.passport_id 
            join registrations.registration r on pr.registration_id = r.id
            where p3.registration_id = r.id
            and pc.zone in (0,1) or (pc.zone is null and pass.safe = true);
        )
    )
))

CodePudding user response：

I think you'll have better performance if you can convert the query to use EXISTS.

SELECT DISTINCT p.id, p.name, p.surname
 FROM persons.person p 
WHERE p.created_at >= '2022-01-01'
  AND p.created_at <= '2022-03-30'
  AND(EXISTS (SELECT * 
                FROM persons.documents d 
                JOIN persons.id_card idd 
                  ON d.id_card_id = idd.id
                JOIN persons.id_card_to_registration ir 
                  ON idd.id = ir.id_card
                JOIN registrations.registration r 
                  ON ir.registration_id = r.id
               WHERE p.id = d.person_id
                 AND p.registration_id = r.id
                 AND ir.status IN (0,5))
   OR EXISTS (SELECT *
                FROM persons.documents d
                JOIN persons.passport pass
                  ON d.passport_id = pass.id
                JOIN persons.passport_country pc 
                  ON pc.id = pass_country_id
                JOIN persons.passport_to_registration pr 
                  ON pass.id = pr.passport_id 
                JOIN registrations.registration r 
                  ON pr.registration_id = r.id
               WHERE p.id = d.person_id
                 AND p.registration_id = r.id
                 AND pc.zone IN (0,1) 
                  OR (pc.zone IS NULL 
                      AND pass.safe = TRUE)))