oracle Oracle从两个表中选择数据，如果一个为空返回null

Oracle select data from two tables and if one is empty return null

提问by constantine

I have two tables (fruit_cost and fruit_availability) in oracle database, details below:

fruit_cost looks like this:

fruit_name | fruit_cost
apple | 30
orange | 7
melon | 14

fruit_availability looks like this:

fruit_name | fruit_availability
table is empty

is there any good option to get results like these:

fruit_name | fruit_cost | fruit_availability
apple | 30 | null
orange | 7 | null
melon | 14 | null

我在 oracle 数据库中有两个表（fruit_cost 和fruit_availability），详细信息如下：

fruit_cost 看起来像这样：

fruit_name | Fruit_cost
苹果 | 30
橙| 7
瓜 | 14个

fruit_availability看起来像这样：

fruit_name | fruit_availability
表是空的

有没有什么好的选项来获得这样的结果：

fruit_name | Fruit_cost | Fruit_availability
苹果| 30 | 空
橙 | 7 | 空
瓜| 14 | 空值

回答by Taryn

You can just join the tables using a LEFT JOIN.

您可以使用LEFT JOIN.

A LEFT JOINwill return all records in the fruit_costtable regardless of whether there is a matching record in the fruit_availabilitytable. Your query will look like this:

无论表中是否存在匹配的记录，ALEFT JOIN都会返回fruit_cost表中的所有记录fruit_availability。您的查询将如下所示：

select fc.fruit_name, 
  fc.fruit_cost,
  fa.fruit_availability
from fruit_cost fc
left join fruit_availability fa
  on fc.fruit_name = fa.fruit_name

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

The result is:

结果是：

| FRUIT_NAME | FRUIT_COST | FRUIT_AVAILABILITY |
------------------------------------------------
|      melon |         14 |             (null) |
|     orange |          7 |             (null) |
|      apple |         30 |             (null) |

If you need help learning join syntax here is a great visual explanation of joins.

如果您在学习连接语法方面需要帮助，这里有一个很好的连接可视化解释。

回答by Phil

Oracle syntax:

甲骨文语法：

select *
from fruit_cost fc, fruit_availability fa
where fc.fruit_name = fa.fruit_name (+);

SQL Fiddle here.

SQL 小提琴在这里。

回答by Jim W

select c.fruit_name, c.fruit_cost, a.fruit_availability
from fruit_cost c 
   left outer join on fruit_availability a on
   c.fruit_name = a.fruit_name
order by c.fruit_name