oracle inline view

Hi I want to use fullouter join oracle

SELECT a.department_id
  FROM employees a,
       ( SELECT department_id  FROM department) b
 WHERE a.department_id = b.department_id

The above query is only giving matching rows ie., 10,20,30 but i want 40,50,60 and 70 can you please advise how can i use fullouter join in inline views. thanks.

employee.dept_id      department.dept_id
10                        10
20                           20      
30                              30
40                              60  
50                              70
Sarma VadlamaniprogrammeranalystAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If you think that way, you need to include both tables' results in the rowset:
SELECT coalesce(a.department_id, b.department_id)
FROM   employees a 
       full outer join (SELECT department_id 
                        FROM   department) b 
                    ON a.department_id = b.department_id 

Open in new window

but of course you would write this simple select more like
select coalesce(a.department_id, b.department_id)
from   employees a 
full join department b 
on a.department_id = b.department_id 

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
Not sure what you are trying to do as your query doesn't match your results.

The syntax should be:

SELECT a.department_id 
FROM   employees a 
       full outer join (SELECT department_id 
                        FROM   department) b 
                    ON a.department_id = b.department_id 

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Doesn't look correct at all. Do you want to have the UNION of both IDs? Any ID being in at least one of both tables?
0
 
johnsoneSenior Oracle DBACommented:
No union should be required with the ANSI outer join syntax.  If we have sample data and expected results we should be able to do it without UNION.

A UNION would be required if you were trying to avoid the ANSI join syntax.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.