Thanks Patrick and Sayan! I've tested your method, very great and easy to
understand! The most important point is simple but my approach is a bit
complex initially.
create table tableA (ts_name varchar2(1), used_size number);
insert into tableA values('a', 10);
insert into tableA values('b', 20);
insert into tableA values('c', 30);
insert into tableA values('d', 40);
insert into tableA values('e', 50);
commit;
create table tableB (ts_name varchar2(1), used_size number);
insert into tableB values('a', 2);
insert into tableB values('b', 6);
insert into tableB values('e', 20);
commit;
a.used_size - b.used_size used_size
FROM tableA a, tableB b
WHERE a.ts_name = b.ts_name
UNION ALL
SELECT a.ts_name,
a.used_size
FROM tableA a
WHERE a.ts_name NOT IN (SELECT b.ts_name FROM tableB b)
ORDER BY ts_name;
- -----------------
a 8
b 14
c 30
d 40
e 30
a.used_size - b.used_size used_size
FROM tableA a, tableB b
WHERE a.ts_name = b.ts_name
UNION ALL
SELECT a.ts_name,
a.used_size
FROM tableA a
WHERE NOT EXISTS (SELECT 1 FROM tableB b WHERE b.ts_name = a.ts_name)
ORDER BY ts_name;
- ----------------
a 8
b 14
c 30
d 40
e 30
Out of interest I pasted your question word for word into ChatGPT and this
was the response I got. Fills me with hope and scares me a little.
You can achieve the desired result by using a SQL join operation and
performing the subtraction between the "USED_SIZE" and "USES_SIZE" columns
of the two tables. Here's an example SQL query:
SELECT
COALESCE(a.TS_NAME, b.TS_NAME) AS TS_NAME,
COALESCE(a.USED_SIZE, 0) - COALESCE(b.USES_SIZE, 0) AS USED_SIZEFROM
table_A a
FULL OUTER JOIN table_B b ON a.TS_NAME = b.TS_NAMEORDER BY
TS_NAME;
In this query, we use a FULL OUTER JOIN operation to combine the data from
both tables, matching on the "TS_NAME" column. We use the COALESCE function
to handle any NULL values that may result from the join operation.
We then perform the subtraction between the "USED_SIZE" and "USES_SIZE"
columns, using the COALESCE function again to handle any NULL values.
Finally, we order the results by the "TS_NAME" column.
This query should produce the desired result you described.
Quanwen Zhao <quanwenzhao@xxxxxxxxx> escreveu no dia terça, 11/04/2023
à(s) 01:46:
Thank you so much, I'll try it using the approach you suggested.
Best Regards
Quanwen Zhao
Sayan Malakshinov <xt.and.r@xxxxxxxxx> 于2023年4月10日周一 22:56写道：
Left join & size1-nvl(size2, 0)
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE
http://orasql.org
On Mon, 10 Apr 2023, 15:53 Quanwen Zhao, <quanwenzhao@xxxxxxxxx> wrote:
Hello there,
I've a requirement about how to retrieve the final calc result from two
number of tables (A and B) from oracle database, such as:
table A:
TS_NAME USED_SIZE(MB)
-------------- ------------------------
a 10
b 20
c 30
d 40
e 50
table B:
TS_NAME USES_SIZE(MB)
--------------- ------------------------
a 2
b 6
e 20
I expect to acquire the result like this:
TS_NAME USED_SIZE(MB)
--------------- -------------------------
a 8
b 14
c 30
d 40
e 30
How to use the SQL to finish it?
Best Regards
Quanwen Zhao
