Make a dual-purpose query, or two separate queries?

Question

This is a contrived example, but consider a scenario where employees have working locations, and where employees have managers who also have working locations:

create table WorkingLocation (
    ID int not null primary key identity(1,1), 
    Name varchar(50)
)

create table Employee (
    ID int not null primary key identity(1,1), 
    Name varchar(50), 
    WorkingLocationID int null,
    ManagerID int null,
    constraint FK_Employee_WorkingLocation foreign key (WorkingLocationID) references WorkingLocation (ID),
    constraint FK_Employee_Manager foreign key (ManagerID) references Employee (ID)
)

Now consider a business rule that wants the employee's WorkingLocation, but will settle for his manager's WorkingLocation if he doesn't have one. At this point you have two options:

1: Have a query that gets both and let business rules decide which to use:

select 
    e.*,
    emp_location.*,
    mgr_location.*
from Employee e
    left join WorkingLocation emp_location on e.WorkingLocationID = emp_location.ID
    left join Employee mgr on e.ManagerID = mgr.ID
    left join WorkingLocation mgr_location on mgr.WorkingLocationID = mgr_location.ID
where e.ID = @id

2: Make separate calls to the database to retrieve the manager's details if the employee has no WorkingLocation set.

Which do you prefer and why?

Answer 1

I would most definately choose option 1.

Have a query which gets both and let the business rules decide which to use.

I would always say avoid multiple calls to the database, unless your getting back an unreasonable amount of data and/or the query is taking a long time.

Answer 2

Try both and see which has better performance in your environment, retaining the option to switch approaches later if something changes.

I don't think you have to pick one solution and stick with it forever.

Answer 3

There is another option - specify the rule in the T-SQL query using COALESCE or use the null-coalescing operator ?? in your code (also works in LinqToSQL).

Either of these will only then require one call the database, so it's +1 for option 1.

Answer 4

If you're not concerned with whose location is returned then, as Mike says, you could consider coalescing the employee and manager locations. However that does move the business logic into what you may consider as the data access tier so, depending on how strict you are, you may instead prefer to have such a rule enforced elsewhere. In which case I'd probably vote for option 1.

Answer 5

EDIT: if it has to be 1 select then:

select e.*, wl.*
from Employee e 
inner join WorkingLocation wl
on e.WorkingLocationID = wl.ID
union
select e.*, wl.*
from Employee e 
inner join Employee m
on m.ID = e.ManagerID 
inner join WorkingLocation wl
on m.WorkingLocationID = wl.ID
where not exists (select 1
   from WorkingLocation wl
   on wl.ID = e.WorkingLocationID)

Answer 6

I know your schema is contrived, but as it stands an ISNULL or COALESCE statement, as suggested by Mike, won’t work because the WorkingLocationID is not nullable and therefore the employee will have to have a location. However, if there is a default location that indicates that the employee doesn’t have a location, say by using the value 0, then using a CASE statement will work. Note that you will need a CASE statement for each WorkingLocation field that you require. So the query becomes something like:

   SELECT e.*
        , CASE
              WHEN emp_location.WorkingLocationID = 0
              THEN mgr_location.ID
              ELSE emp_location.ID
          END AS Location
        , CASE
              WHEN emp_location.WorkingLocationID = 0
              THEN mgr_location.Name
              ELSE emp_location.Name
          END AS Name
     FROM Employee e
LEFT JOIN WorkingLocation emp_location
       ON e.WorkingLocationID = emp_location.ID
LEFT JOIN Employee mgr
       ON e.ManagerID = mgr.ID
LEFT JOIN WorkingLocation mgr_location
       ON mgr.WorkingLocationID = mgr_location.ID
    WHERE e.ID = @id