CODEWITHSUNDEEP
sqloracle-databasejoinfull-outer-join
heyooo678
heyooo678

Reputation: 91

FULL OUTER JOIN without any join conditions

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?

Upvotes: 1

Views: 3395

Answers (4)

WesternGun
WesternGun

Reputation: 12728

You can do cross join, which is basically selecting from both tables.

select * from
    t1, t2
;

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32675

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

Upvotes: 2

GMB
GMB

Reputation: 222382

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use:

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

Upvotes: 4

Related Questions