Join two tables... without JOIN

Question

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.

Do you mean without using a `join` *keyword*? You will always have a join operation involved, regardess of syntax. — Stu, Mar 03 '22 at 19:58
Query 2 is still a `JOIN`, just without that keyword. Since a `JOIN` is the way to do this, what is your reason for trying to do it without? — Tangentially Perpendicular, Mar 03 '22 at 19:58
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ec154ad243efdff2162816205fdd42b5 — jarlh, Mar 03 '22 at 20:08
JOIN behaves like Query 2 returning only rows with matches in both tables. But I'd like to get the behavior of FULL JOIN — Sputnik, Mar 03 '22 at 20:08
@Sputnik, see my dbfiddle link above. (Was going to answer, but the question was closed just before I was finished.) — jarlh, Mar 03 '22 at 20:09
Read the duplicate link please, LFET JOIN UNION RIGHT JOIN, idependet whyt you have — nbk, Mar 03 '22 at 20:09
@nbk this question asks specifically `without using any JOIN `, so the answer provided by @jarlh on the fiddle would suited for this question , it is wrong closed — Ergest Basha, Mar 03 '22 at 20:13
@jarlh Please let me try this one. Looks promising. The only problem is that real tables are different in structure and not suitable for UNION. ...Where t1.id=t2.id or not exists (...) is the desired logic, but invalid in sql — Sputnik, Mar 03 '22 at 20:17
@ErgestBasha i opened it again, but id doubt tghat you can use a query without joins, impcit or other wise, the unioned table will not work as the coment on Akinas post, says the tables are to different — nbk, Mar 03 '22 at 20:19

jarlh · Accepted Answer · 2022-03-03T20:25:51.423

2

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

edited Mar 03 '22 at 20:25

answered Mar 03 '22 at 20:17

jarlh

42,561
8
45
63

Thank you very much, this is very close. Is there a way to implement logic like ... where t1.id=t2.id or null [if no match]... without a subquery? – Sputnik Mar 03 '22 at 20:31
Not without an explicit OUTER JOIN (left or right.) – jarlh Mar 03 '22 at 20:35

score 1 · Answer 2 · answered Mar 03 '22 at 19:55

1

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

answered Mar 03 '22 at 19:55

Akina

39,301
5
14
25

Real tables are quite different, so UNION would not work. Sorry for not mentioning it in my post. Will add it )) – Sputnik Mar 03 '22 at 20:01
@Sputnik - Then modify the Question to point out that the tables are different. Furthermore, explain why the `SELECT` from the two tables won't be fetching the "same" columns. This eliminates the issue of the tables being different! – Rick James Mar 03 '22 at 20:17
1

@Sputnik May this will be surprize for you - but in UNION you may select any needed amount of any columns and expressions, not all table columns only. – Akina Mar 03 '22 at 20:46

Join two tables... without JOIN

2 Answers2