Use of Inline views
-
Hi,
I have three tables employee, department and person. I do a inner join on employee and department tables to get the relavent information. I want to get ssn# from person table only if it is stored in the table - I do left outer join on person table. Since each of the tables have 2 million rows, I am wondering if I can eliminate left outer join and use an inline view in the query.
select e.empname, d.departmentname, p.ssn, p.dob
from employee e inner join department d
on e.emp_id = d.emp_id
left outer join person p on p.emp_id = e.emp_id
On general terms what is the advantage and purpose of using inline view.
Any suggestions and inputs would help.
Thanks
-
You would still need to join the result of the inline view in your from clause, so I'm not sure if you could avoid the left outer join this way.
Probably the biggest advantage of inline views is that you do not create any new database objects. It should however not confuse the query optimizer. I would suggest to look at execution plans and compare them.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
select e.empname, d.departmentname, '', ''
from employee e inner join department d
on e.emp_id = d.emp_id
union
select e.empname, d.departmentname, p.ssn, p.dob
from employee e inner join department d
on e.emp_id = d.emp_id
inner join person p on p.emp_id = e.emp_id
Might work ok, might be worse. Have to test, but it can eliminate the outer joins.
-
Outer joins are not necessarily a bad thing... it's the criteria (or rather, lack of) that is usually responsible for the notorious reputation of outer joins.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I do agree with Jeff. I try to avoid outer joins where it makes sense, but definitely test both ways and be sure you understand the difference between the ON and WHERE clauses when writing them.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply