In SQL Server, I've the following design:
Is it 100% sure that the first condition from the OR statement in a JOIN will be executed first ? So that the following SQL statement will result in the 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 = '*')
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
SQL's from clause selects and joins your tables and is the first executed part of a query. This means that in queries with joins, the join is the first thing to happen. It's a good practice to limit or pre-aggregate tables before potentially large joins, which can otherwise be very memory intensive.
1 Answer. The order doesn't matter for INNER joins. As long as you change your selects from SELECT * to SELECT a.
Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.
Is it 100% sure that the first condition from the OR statement in a JOIN will be executed first ?
No. There is no guaranteed order of evaluation and even if there where the entire expression would still evaluate to the same value and would not influence what rows are matched in the join.
Your query will give this result:
Name Percentage
---------- -----------
P-A 100
P-A 20
P-A 80
P-B 100
I guess 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. https://data.stackexchange.com/stackoverflow/qt/118492/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With