How do I compose this sql query in Oracle?

Question

I have got an sql query that pulls out all sorts of information. Part of it is the following

  select gsm.mobile_no, emp.employee_id, d.department_id
  from data gsm, employees emp, department d
  where gsm.code = e.code
  and d.id = e.id

Now there's a column called roaming in another table called "call" . Here's the problem. There's information from the call table for only some of the mobile numbers so when I join gsm.code = call.id like below

  select gsm.mobile_no, emp.employee_id, d.department_id, roaming.name 
  from data gsm, employees emp, department d, call roaming
  where gsm.code = e.code
  and d.id = e.i
  and roaming.id = gsm.code

Then I lose information about employees and departments since only the records that satisfy the condition roaming.id = gsm.code are retrieved so I lose info about departments, employees and all other mobile numbers. I want to retrieve all records from all tables including roaming.id for the mobile numbers where applicable and if there's no data available for some of the mobile numbers then display null but I want all of the records displayed. How could I do that?

using a LEFT JOIN at the place of INNER JOIN and join conditions in ON at the place of the conditions in WHERE — CodeBird
– CodeBird, Commented Apr 17, 2014 at 20:39

Gordon Linoff · Accepted Answer · 2014-04-17 20:39:06Z

3

Your time has come to move to the world of modern join syntax. Put your join conditions in the on clause and remember the simple rule: Never use a comma in the from clause.

What you need is a left outer join. You can't really do that in the where clause. Well, you can in Oracle, but it is not pretty and not as good as a real left outer join.

select gsm.mobile_no, emp.employee_id, d.department_id, roaming.name 
from employes left outer join
     data gsm
     on gsm.code = e.code left join
     department d
     on d.id = e.i left outer join
     call roaming
     on  roaming.id = gsm.code;

Although you can mix inner and outer joins, you want to keep all employees. So start with that table and make all the joins left outer join.

answered Apr 17, 2014 at 20:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3017335 Over a year ago

Oh that would be a lot of rewriting since I have got loads of other inner joins in the form of where clause. Is it possible to use left outer join in a where clause just once?

Collectives™ on Stack Overflow

How do I compose this sql query in Oracle?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related