FULL OUTER JOIN without any join conditions

Question

I have two tables

t1:

+--------------+-------------+--------------+
| Product Name |   Issue #   |  Risk Level  |
+--------------+-------------+--------------+
| Product1     | Incident#45 | Risk Level 2 |
| Product2     | Incident#23 | Risk Level 3 |
| Product3     | Incident#98 | Risk Level 1 |
+--------------+-------------+--------------+

t2:

+----------+----------------+
| Org Code | Monthly Output |
+----------+----------------+
|      598 |           2000 |
|      412 |            100 |
|      598 |           2500 |
+----------+----------------+

Which I would like to combine as an "outer join" to create:

+--------------+-------------+--------------+----------+----------------+
| Product Name |   Issue #   |  Risk Level  | Org Code | Monthly Output |
+--------------+-------------+--------------+----------+----------------+
| Product1     | Incident#45 | Risk Level 2 | (null)   | (null)         |
| Product2     | Incident#23 | Risk Level 3 | (null)   | (null)         |
| Product3     | Incident#98 | Risk Level 1 | (null)   | (null)         |
| (null)       | (null)      | (null)       | 598      | 2000           |
| (null)       | (null)      | (null)       | 412      | 100            |
| (null)       | (null)      | (null)       | 598      | 2500           |
+--------------+-------------+--------------+----------+----------------+

t1 and t2 have no similar columns for me to join on. Is there any way to join these tables together?

score 4 · Accepted Answer · answered Feb 09 '20 at 23:26

4

You can use:

select l1.*, l2.*
from l1 full join
     l2
     on 1 = 0;   -- never true

answered Feb 09 '20 at 23:26

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · answered Feb 09 '20 at 23:35

I think that UNION ALL is more efficient, and most importantly, more readable, than a JOIN.

SELECT
    ProductName
    , IssueNumber
    , RiskLevel
    , NULL AS OrgCode
    , NULL AS MonthlyOutput
FROM t1

UNION ALL

SELECT
    NULL AS ProductName
    , NULL AS IssueNumber
    , NULL AS RiskLevel
    , OrgCode
    , MonthlyOutput
FROM t2

score 1 · Answer 3 · answered Feb 09 '20 at 23:33

You could very well use union all here. This seems to me like the most natural way to phrase a query for your question.

It also has the advantage of being widely portable: most databases support union all - this is not true of full join, that, although part of the SQL Standard for decades, is not yet supported in all major products (eg in MySQL).

select product_name, issue#, risk_level, null org_code, null monthly_output from t1
union all
select null, null, null, org_code, monthly_output from t2

FULL OUTER JOIN without any join conditions

3 Answers3