3

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

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
constantine
  • 121
  • 1
  • 2
  • 6

3 Answers3

7

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

See 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.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • @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 -- http://stackoverflow.com/questions/1193654/difference-between-oracles-plus-notation-and-ansi-join-notation – Taryn Jan 17 '13 at 14:38
  • Thanks for Your explanations, they were very helpful. – constantine Jan 20 '13 at 18:48
  • 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 Jan 21 '13 at 09:15
  • I have found this: [link](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) but the article applies to MSSQL not ORACLE and that is why I'm still trapped :). Please help. – constantine Jan 21 '13 at 09:29
  • @constantine The `OUTER` is not required syntax when using a `JOIN` – Taryn Jan 21 '13 at 11:09
  • I've made some changes and updates into my query. Now there are LEFT OUTER JOIN at three tables. All works perfectly. Thanks for explaining to me why shouldn't I use old methods. All links were very useful and helpful. Now I see the light in the tunnel :). It's easier to manipulate JOIN syntaxes in the FROM clause than (+) in the WHERE clause. Best regards. – constantine Jan 21 '13 at 20:48
4

Oracle syntax:

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

SQL Fiddle here.

Phil
  • 2,392
  • 18
  • 21
0
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
Jim W
  • 4,890
  • 2
  • 20
  • 26
  • 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 – constantine Jan 20 '13 at 18:53