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
Add a comment
|
3 Answers
You can just join the tables using a LEFT JOIN.
A LEFT JOIN will return all records in the fruit_cost table regardless of whether there is a matching record in the fruit_availability table. Your query will look like this:
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
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.
6 Comments
Taryn
@constantine while the syntax in the other answer is valid for oracle only, Oracle does recommend that you use
OUTER JOIN syntax rather than the join operator. Here is a question explaining -- stackoverflow.com/questions/1193654/…
constantine
Thanks for Your explanations, they were very helpful.
constantine
One more question to You, should I use LEFT JOIN or LEFT OUTER JOIN in my case (ORACLE DB) and what's the difference between these two syntaxes?
constantine
I have found this: link but the article applies to MSSQL not ORACLE and that is why I'm still trapped :). Please help.
Taryn
@constantine The
OUTER is not required syntax when using a JOIN |
Oracle syntax:
select *
from fruit_cost fc, fruit_availability fa
where fc.fruit_name = fa.fruit_name (+);
SQL Fiddle here.
1 Comment
constantine
Best and shortest result 4 my solution. Thanks!
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
1 Comment
constantine
select c.fruit_name, c.fruit_cost, a.fruit_availability from fruit_cost c left outer join fruit_availability a on c.fruit_name = a.fruit_name order by c.fruit_name