Do a double query or two separate queries?

Question

This is a far-fetched example, but consider a scenario where employees have jobs, and where employees have managers who also have jobs:

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 to collaborate WorkingLocation, but will agree to its manager WorkingLocationif he does not have one. At this point you have two options:

1: You have a request that receives both and allows business rules to decide what 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 get manager information if the employee does not WorkingLocation.

What do you prefer and why?

Answer 1

- T-SQL COALESCE ? ( LinqToSQL).

, 1 +1.

Answer 2

1.

, - , .

, , / .

Answer 3

, , , , . - , , , , , . , , 1.

Answer 4

, , ISNULL COALESCE, , , WorkLocationID NULL, . , , , , , 0, CASE . , WorkingLocation CASE. , :

   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

Answer 5

, , , - .

I don’t think you need to choose one solution and stick to it forever.

Answer 6

EDIT: if it should be 1, select:

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)