SQL: Joining results of two individual joins

Question

This is an IIT question

I am trying to solve this problem. And I can individually do

(i)P LEFT JOIN Q ON P.A = Q.A AND P.B = Q.B

and

(ii) Q RIGHT JOIN R ON Q.A = R.A But to JOIN the results of these two, I'm not able to find an answer. Though this answer came little close SQL Join two query results

With this, I tried the following and got an ambiguous error. I know I can alias columns but once again failed to find how to create an alias for column names on the FROM clause.

mysql> with r1 as (select * from p left join q on p.a = q.a and p.b = q.b) select * from q right join r on q.a = r.a cross join r1 on r1.a = r.e;
ERROR 1060 (42S21): Duplicate column name 'a'

Is there an elegant way of creating two "WITH" clauses for two results and then joining the two results or can it be done even much more simpler than that?

EDIT: To avoid ambiguity I tried aliasing all fields and found that it is working. But also will try other answer that is provided by Impaler.

mysql> with r1 as (select p.a as pa, p.b as pb, p.c as pc, q.a as qa, q.b as qb, q.d as qd from p left join q on p.a = q.a and p.b = q.b), r2 as (select q.a as qa1, q.b as qb1, q.d as qd1, r.a as ra1, r.e as re1 from q right join r on q.a = r.a) select * from r1 cross join r2;
+------+------+------+------+------+------+------+------+------+------+------+
| pa   | pb   | pc   | qa   | qb   | qd   | qa1  | qb1  | qd1  | ra1  | re1  |
+------+------+------+------+------+------+------+------+------+------+------+
| a3   | b2   | c2   | NULL | NULL | NULL | a1   | b2   |    2 | a1   | e1   |
| a3   | b3   | c1   | NULL | NULL | NULL | a1   | b2   |    2 | a1   | e1   |
| a2   | b1   | c2   | a2   | b1   |    5 | a1   | b2   |    2 | a1   | e1   |
| a1   | b2   | c1   | a1   | b2   |    2 | a1   | b2   |    2 | a1   | e1   |
| a3   | b2   | c2   | NULL | NULL | NULL | a3   | b1   |    6 | a3   | e2   |
| a3   | b3   | c1   | NULL | NULL | NULL | a3   | b1   |    6 | a3   | e2   |
| a2   | b1   | c2   | a2   | b1   |    5 | a3   | b1   |    6 | a3   | e2   |
| a1   | b2   | c1   | a1   | b2   |    2 | a3   | b1   |    6 | a3   | e2   |
| a3   | b2   | c2   | NULL | NULL | NULL | NULL | NULL | NULL | a4   | e3   |
| a3   | b3   | c1   | NULL | NULL | NULL | NULL | NULL | NULL | a4   | e3   |
| a2   | b1   | c2   | a2   | b1   |    5 | NULL | NULL | NULL | a4   | e3   |
| a1   | b2   | c1   | a1   | b2   |    2 | NULL | NULL | NULL | a4   | e3   |
| a3   | b2   | c2   | NULL | NULL | NULL | NULL | NULL | NULL | a4   | NULL |
| a3   | b3   | c1   | NULL | NULL | NULL | NULL | NULL | NULL | a4   | NULL |
| a2   | b1   | c2   | a2   | b1   |    5 | NULL | NULL | NULL | a4   | NULL |
| a1   | b2   | c1   | a1   | b2   |    2 | NULL | NULL | NULL | a4   | NULL |
+------+------+------+------+------+------+------+------+------+------+------+

Answer 1

Let's see.

The left side is a left join. It evaluates to:

L (ABCD)
A1 B2 C1 2
A2 B1 C2 5
A3 B3 C1 null
A3 B2 C2 null

Then, the right side is a right join. It evaluates to:

R (ABDE)
A1 B2   2    E1
A3 B1   6    E2
A4 null null E3
A4 null null null

Finally, the query uses a natural join to join both sides. There's only one match for the columns ABD. It evaluates to:

LR (ABCDE)
A1 B2 C1 2 E1

You can express the whole query in SQL as:

select *
from (
  select p.*, q.d
  from p 
  left join q on q.a = p.a and q.b = p.b
) l
join (
  select r.*, q.b, q.c
  from q
  right join r on r.a = q.a
) r on r.a = l.a and r.b = l.b and r.d = l.d

Note: I purposedly avoided the "natural join" syntax. It's confusing, obscure, and error prone. In my opinion, natural joins should be removed from the SQL Standard.