So I'm trying to join 2 table outputs which has no common column but same column name.
SQL> SELECT sum(s.bytes/1024/1024/1024) AS "Total Space"
FROM dba_data_files s
UNION all
SELECT sum(d.bytes/1024/1024/1024) AS "Used space"
FROM dba_segments d 2 3 4 5
6 ;
Total Space
----------
90.9035645
74.4375
When I'm using cross join I'm getting incorrect output:
SELECT sum(s.bytes/1024/1024/1024) AS "Total Size", sum(d.bytes/1024/1024/1024) AS "Used space"
FROM dba_data_files s
CROSS JOIN dba_segments d;
2 3
Total Size Used space
---------- ----------
665959.513 744.375
The bytes column in both tables are different. The output I'm getting above should come in a single row like this:
Total Size Used space
---------- -----------
90.9035645 74.4375
How should I modify the above query? Please help.
There are lots ways to do this, I'm thinking of using imaginary column to join your second table.
SELECT t1.totalspace as "Total Space", t2.usedspace as "Used Space"
FROM
(SELECT sum(bytes/1024/1024/1024) AS totalspace, 1 as col
FROM dba_data_files) t1
LEFT JOIN
(SELECT sum(bytes/1024/1024/1024) AS usedspace, 1 as col
FROM dba_data_files) t2 ON t2.col = t1.col