Join two tables... without JOIN-CodePudding

I've got two tables T1 and T2, both with a single field (id).

T1.id has values:
1
2
4

T2.id has values:
1
3
4

I need to join these tables.

Desired result:

T1    |    T2
------|------
1     |    1
2     |    null
null  |    3
4     |    4

With JOIN I'd do it easily:

Query 1

SELECT * FROM T1 FULL JOIN T2 ON T1.id=T2.id

But due to certain reasons I can't use JOIN here. So, with a simple query like this

Query 2

SELECT * FROM T1, T2 WHERE T1.id=T2.id

I would get only two rows of data

T1    |    T2
------|------
1     |    1
4     |    4

as two other rows would be omitted due to no matches in the other table.

No matter what to fill the missing matches with. It could be NULL or any other value - really anything, but I need to get those omitted rows.

Is there a way to modify Query 2 to get the desired result without using any JOIN?

PS: Real tables are different in structure, so UNION is not allowed either.

PPS: I've just given a model to point out the problem. In reality it's a "megaquery" involving many tables each having dozens of columns.

CodePudding user response：

Standard way to implement FULL OUTER JOIN when only implicit joins are supported.

select t1.id t1id, t2.id t2id
from t1, t2 where t1.id = t2.id

union all

select id, null from t1
where not exists (select 1 from t2 where t2.id = t1.id)

union all

select null, id from t2
where not exists (select 1 from t1 where t1.id = t2.id)

order by coalesce(t1id, t2id)

The first SELECT produces the INNER JOIN part of the result.

The second SELECT adds the additional LEFT OUTER JOIN rows to the result.

The third SELECT adds the additional RIGHT OUTER JOIN rows to the result.

All together, a FULL OUTER JOIN is performed!

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ec154ad243efdff2162816205fdd42b5

CodePudding user response：

SELECT t1.id t1_id, t2.id t2_id
FROM ( SELECT id FROM table1
       UNION DISTINCT
       SELECT id FROM table2 ) t0
NATURAL LEFT JOIN table1 t1 
NATURAL LEFT JOIN table2 t2