CODEWITHSUNDEEP
oracle-database
Naga K
Naga K

Reputation: 733

Filter a query in Oracle

I have the following query

select a.empid, a.age, a.city, b.name
  join supervisor b on a.supervisorid = b.empid

There is a chance that entries in "Supervisor" table may not be present in "Employee" table as an Employee After forming the above query , i want to make "b.supervisorname" field as "null", if "b.supervisorid" not in "a.empid" column

EMPLOYEE TABLE:

EMPID--AGE--CITY--SUPERVISOR

1--12--A--123

2--21--B--1

3--23--C--2

Supervisor Table:

SUPERVISOR TABLE

EMPID--NAME

123--ABC

1--EFG

2-HIJ

OUTPUT:

EMPID--AGE--CITY--NAME

1--12--A--null

2--21--B--ABC

3--23--C--EFG

i dont want to use,

select a.empid, a.age, a.city, b.name
  from employee a
  join supervisor b on a.supervisorid =
                       (select empid
                          from supervisor
                         where empid in (select empid from employee))

as this kind of querying affects the performance

Is there any shortcut way to do it?

Upvotes: 0

Views: 62

Answers (1)

MonkeyZeus
MonkeyZeus

Reputation: 20737

You should ALWAYS use explicit joins to avoid performance issues. And in general it helps to define a FROM clause in queries

The query below should work for you:

select
    e.empid,
    e.age,
    e.city,
    s.name
FROM
    employee e
    LEFT OUTER JOIN
        supervisor s
        on e.supervisor = s.empid

Upvotes: 1

Related Questions