Multiple Full join is not working in PostgreSQL

Question

I have one select statement with multiple full joins in it.

select
aroll as aroll,
aname as aname,
astandard as astandard,
amarks as amarks,
adepartment_id as adepartment_id,
broll as broll,
bname as bname,
bstandard as bstandard,
bmarks as bmarks,
bdepartment_id as bdepartment_id,
croll as croll,
cname as cname,
cstandard as cstandard,
cmarks as cmarks,
cdepartment_id as cdepartment_id
from

(
    
(   
        (
select 
            roll as aroll,
            name as aname,
            standard as astandard,
            marks as amarks,
            department_id as adepartment_id
        from student
        where department_id = 1
        and standard = 10) as firstA

        full join

        (select 
            roll as broll,
            name as bname,
            standard as bstandard,
            marks as bmarks,
            department_id as bdepartment_id
        from student
        where department_id = 2
        and standard = 10) as secondB 
        on
        firstA.astandard = secondB.bstandard
    ) first_second_combined
    full join
        (select
        roll as croll,
        name as cname,
        standard as cstandard,
        marks as cmarks,
        department_id as cdepartment_id
    from student
    where department_id = 3
    and standard = 10) thirdC
    on
    first_second_combined.astandard = thirdC.cstandard
    and
    first_second_combined.bstandard = thirdC.cstandard
    
)x;

It gives me error as below

Error: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions

Yes, that doesn't work. What is your question? – Laurenz Albe Dec 07 '20 at 11:18 — Laurenz Albe, Dec 07 '20 at 11:18

score 0 · Answer 1 · answered Dec 07 '20 at 15:55

You are making it look very complicated. You could write your query like this:

select
  firstA.roll as aroll,
  firstA.name as aname,
  firstA.standard as astandard,
  firstA.marks as amarks,
  firstA.department_id as adepartment_id,
  secondB.roll as broll,
  secondB.name as bname,
  secondB.standard as bstandard,
  secondB.marks as bmarks,
  secondB.department_id as bdepartment_id,
  thirdC.roll as croll,
  thirdC.name as cname,
  thirdC.standard as cstandard,
  thirdC.marks as cmarks,
  thirdC.department_id as cdepartment_id
from student firstA
full join student secondB ON firstA.standard = secondb.standard
full join student thirdC ON firstA.standard = thirdC.standard

It essentially means "Get the triplets of students sharing a common standard.".

Note that you will get a triplet (S, S, S) for each student S. Also, for each triplet (A, B, C), you will also get `(A, C, B), (B, A, C), (B, C, A), (C, A, B), (C, B, A). If your intent is to get only triplets of different students, and also to avoid repetitions, you may want to add :

where firstA.name < secondB.name AND secondB.name < thirdC.name

Note also that using FULL JOIN here makes sense only if you have NULL values in the standard column. Otherwise you could aswell use INNER JOINs.

I hope this helps you get better knowledge of how SQL works.

Multiple Full join is not working in PostgreSQL

1 Answers1