SQL Optimization for JOIN

Is there a way in DB2 SQL that allows you to pick which table data will come from in a join? Example:

TABLE t_Employee (EmpID, Name, Status)
TABLE t_Acting (ActID, EmpID, JobTitle, Salary)
TABLE t_Current(CurrID, EmpID, JobTitle, Salary)

- Everyone (old and current) in the database, always has a record in t_Employee.
- Every employee who is currently working, always has a record in t_Current with foreign key EmpID,  Status = "Current"
- If an employee is currently acting in another position, they have a record in t_Acting with foreign key EmpID, Status = "Acting"

So if Emp A is working as a team-leader, Emp A has a record in t_Employee and t_Current with TEAM LEADER as job title.
If Emp B is working as a team-leader, but is currently acting as a Manager, Emp B's t_Current record will have him as TEAM LEADER in job title, however he will also have t_Acting record with MANAGER in  job title.

When I want to do a query of all currently working employees, but only list their name and job-title. If I query the above example, I want a result of:

Name, Status, JobTitle
Emp A, Working, TEAM LEADER
Emp B, Acting, MANAGER

I currently have a solution that works, but is terribly slow as there is 7600 records in the employee table.

What it is now (I'm querying specific Columns in this statement, but I omitted them here for simplicity):
SELECT * FROM t_Employee INNER JOIN ((SELECT * FROM t_Acting) UNION (SELECT * FROM t_Current WHERE EmpID NOT IN (SELECT EmpID FROM t_Acting))) INNER JOIN ... a couple other small tables.
DJmistralAsked:
Who is Participating?
 
LowfatspreadCommented:
select e.emp_id
        ,case when a.empid is null then 'Working' else 'Acting' end as Status
        ,coalesce(a.jobtitle,c.jobtitle) as jobtitle
from t_employee as e
left outer join t_acting as a
on e.emp_id = a.emp_id
inner join t_current as c
on a.emp_id=c.emp_id
order by ae.emp_id

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi DJ,

Joining 7,600 records is trivial.  You should see a sub-second response to this kind of query.

Lowfatspread's suggestion eliminates the derived tables.  (Generally a very good thing.)  It also generates a result set based on two joins that may well be the result of index scans.

Try Lowfatspread's query.  If this doesn't come back almost instantly you've got a problem that needs to be addressed.  Report back to us and if there's still an issue we'll help you to solve it.

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.