Oracle sql 查询联合操作? [英] Oracle sql query union operation?

问题描述

我有两张桌子.TableA 和 TableB.两个表都有一些包含两列的数据，如下所示.

I have two tables. TableA and TableB. both the tables has some data with two columns as below.

TableA
---------
id  Name
--- ----
1   abc
2   def

TableB
---------
id  Name
--- ----
1   xyz
2   pqr

现在我将从我的应用程序中传递 id 列表并获得与它们相同的 id 及其名称:

Now i would pass list of ids from my application and get same ids along with their names as:

select id, name 
from TableA 
where id in(1,2) 
union select id, name 
from TableB 
where id in(1,2);

以上查询给出的结果为:

above query gives results as:

1   abc
1   xyz
2   def
2   pqr

但我需要的是，如果两个表中都存在相同的 ID，则应考虑 TableB 的名称，而不考虑 TableA 的名称.

But what i need is if same id is present in both the tables then TableB's Name should be considered but not TableA's name.

Expected output:

1   xyz
2   pqr

另外一个是，如果TableB不包含任何数据，那么应该获取TableA的数据.

One more is, if TableB does not contain any data then TableA's data should be fetched.

我该怎么做?

谢谢！

推荐答案

请尝试使用 LEFT JOIN.

Please try using LEFT JOIN.

SELECT TableA.ID, 
  NVL(TableB.Name, TableA.Name) Name FROM 
TableA LEFT JOIN TableB 
 ON TableA.ID=TableB.ID
WHERE TableA.ID IN (1, 2)

这篇关于Oracle sql 查询联合操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！