SQL: Is the first condition in the OR statement in JOIN always satisfied first?

Question

In SQL Server, I have the following design:

100% sure that the first condition from the OR operator to JOIN will be satisfied first? So, the following SQL result will produce a green result?

SELECT P.Name, D.Percentage FROM Personnel P JOIN Department D ON P.Dep_Code = D.Code AND (P.SubDep_Code = D.SubCode OR D.SubCode = '*') 

Answer 1

100% sure that the first condition from the OR statement in the JOIN will be executed first?

Not. There is no guaranteed evaluation order, and even if where the whole expression will still be evaluated with the same value and will not affect which lines are matched in the connection.

Your query will produce the following result:

 Name Percentage ---------- ----------- PA 100 PA 20 PA 80 PB 100 

I think you are looking for something like this.

 select P.Name, coalesce(D1.Percentage, D2.Percentage) as Percentage from Personnel as P left outer join Department as D1 on P.Dep_Code = D1.Code and P.SubDep_Code = D1.SubCode left outer join Department as D2 on P.Dep_Code = D2.Code and D2.SubCode = '*' where coalesce(D1.Percentage, D2.Percentage) is not null 

You can try the queries here using SQL Server 2008. http://data.stackexchange.com/stackoverflow/qt/118492/

Answer 2

Mikael's answer is correct, but this modified version of the request is more concise:

 SELECT P.Name, D.Percentage FROM Personnel P JOIN Department D ON P.Dep_Code = D.Code AND COALESCE(P.SubDep_Code,'*') = D.SubCode