Mixing INNER and OUTER joins

I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...

Take the following example:

SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).

Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.

The solution I have used in the past is:

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.

I could do this:

SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.

I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.

Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?

Daniel

I think that this is pretty much the answer, though there is an easier way to write it

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

You can nest the joins to get the effect you want:

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
INNER JOIN [Department] --this limits the department to the context of a Profile only!
ON [Department].[DepartmentId] = [Profile].[DepartmentId]
ON [Profile].[UserId] = [User].[UserId]

However, you have the same limitation that you could not in another join reference Department. You could nest in other tables to to get to the right combination, perhaps (it can get really messy fast if you have lots of tables to join :) So if you actually need to refer to the department in that context in a later join, then the two left outer joins might be the correct thing to do

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
ON [Profile].[UserId] = [User].[UserId]
AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department]
ON [Department].[DepartmentId] = [Profile].[DepartmentId]

I don't see a problem with that syntax at all. You are correct that it is not as clear what your intention is, but, if you are joining department to multiple tables, that might be the case.

I highlighted in that context because if you need department in another context, it is valid to reference it again, as some alias:

SELECT *
FROM [User]
LEFT OUTER JOIN [Profile]
INNER JOIN [Department] --this limits the department to the context of a Profile only!
ON [Department].[DepartmentId] = [Profile].[DepartmentId]
ON [Profile].[UserId] = [User].[UserId]
INNER JOIN Department as otherDepartment
ON User.department = otherDepartment.DepartmentId

Or whatever. It is all in how you need to use the data.

|||

Hi Louis,

Thanks for the info; the nesting of joins is new syntax to me... Looks like I'm going to have to revisit my T-SQL code structure standards now!

I've tried the nexted join approach for the specific scenario I'm working on and it seems to work perfectly and also reads more clearly to me than the other two options.

I've also tried to extend it more than I need right now and it looks like you can reference the nested joined table in a different context, e.g.:

SELECT *

FROM [User]

LEFT OUTER JOIN [Profile]

INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

ON [Profile].[UserId] = [User].[UserId]

LEFT OUTER JOIN [BusinessUnit] ON [BusinessUnit].[BusinessUnitId] = [Department].[BusinessUnitId]

AND [BusinessUnit].[BusinessUnitId] = [User].[BusinessUnitId]

I think you were saying that the above example would not work because the department table is only visible inside the scope of the "profile" join but the above example does work OK. Have I misunderstood what you were saying or could this be a change in SQL 2005?

Daniel