Outer join

Posted on 2011-10-04
Last Modified: 2012-05-12

After executing the following query:

Select e.deptartment _id EMP,d.department_id DEPT
from employee e, department d
where e.department_id(+) = d.department_id

I get:
|EMP                                     DEPT               |
 |                                          Contracting     |

I need the same results to be produced WITHOUT using the outer join

Question by:devguru001
    LVL 67

    Accepted Solution

    The only way to achieve that without using an outer join is by UNION:
    Select e.deptartment _id EMP,d.department_id DEPT
    from employee e, department d
    where e.department_id = d.department_id
    Select NULL,d.department_id 
    from department d
    where not exists (select * from employee e where e.department_id = d.department_id)

    Open in new window


    Author Closing Comment

    Good answer!!!!  Thanks for the quick response....

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example, show how to take different types of Oracle backups using RMAN.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now