SQL Optimization for JOIN
Posted on 2007-04-11
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.