April 3, 2008 at 10:59 am
Hi,
Wanted to know whether having the filtering condition in the WHERE clause is better than having it in the FROM clause
To give an example
SELECT
A.A1
,A.A2
,B.B1
,B.B2
FROM A
INNER JOIN B on A.A3 = B.B3
WHERE
B.4 = 'TEST' AND B.5 ='Y'
OR
SELECT
A.A1
,A.A2
,B.B1
,B.B2
FROM A
INNER JOIN B on A.A3 = B.B3
AND B.4 = 'TEST' AND B.5 ='Y'
Thanks
Vinoj
April 3, 2008 at 11:13 am
In the From clause is better.
In Books Online, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb9696a6-1b2f-4d0e-baa4-e9c54211ea2b.htm
"...the join conditions in the FROM clause, which is the preferred method."
That article has a little more data on it, and why it works the way it does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 11:19 am
Keep in mind that the different placement would create altogether different query results in an OUTER JOIN scenario.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 2:22 pm
to add to mark's comment...
ideally, the FROM clause should describe how the tables JOIN together (dept join emp on dept.deptId = emp.deptId) and the WHERE clause should have criteria for the desired data (dept.divisionId = 3 and emp.gradeLevel > 4).
however, sometimes moving crtieria to the FROM clause can kick the optimizer in the pants by changing the execution plan.
April 3, 2008 at 2:31 pm
So if i understand correct the best option is to have it in the FROM clause itself, got confused with the replies.
April 3, 2008 at 2:35 pm
vinojsasidharan (4/3/2008)
So if i understand correct the best option is to have it in the FROM clause itself, got confused with the replies.
no, best practice is the JOIN conditions should be in the FROM clause. filtering conditions should be in the WHERE clause.
April 3, 2008 at 2:40 pm
vinojsasidharan (4/3/2008)
So if i understand correct the best option is to have it in the FROM clause itself, got confused with the replies.
If it's about the join, it should be in the From clause. If it's about the final data, it should be in Where.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 3:21 pm
antonio.collins (4/3/2008)
to add to mark's comment...ideally, the FROM clause should describe how the tables JOIN together (dept join emp on dept.deptId = emp.deptId) and the WHERE clause should have criteria for the desired data (dept.divisionId = 3 and emp.gradeLevel > 4).
however, sometimes moving crtieria to the FROM clause can kick the optimizer in the pants by changing the execution plan.
Not quite. when dealing with OUTER JOINS, it might affect which of the RIGHT ROWS come through if put in the ON statement; if it's in the WHERE clause, it affects which of the COMPOSITE rows come through.
It's not so much the execution plan, you get different records and results.
An example is sometimes better:
DROP TABLE TABLEA
DROP TABLE TABLEb
create table tableA(A_id int, value int)
create table tableB(b_id int, A_id int, sub_value int)
INSERT tableA
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3
INSERT TableB
SELECT 1, 1, 10 UNION ALL
SELECT 2, 1, 20 UNION ALL
SELECT 3, 3, 30 UNION ALL
SELECT 4, 2, 10 UNION ALL
SELECT 5, 2, 20
SELECT a.value, b.sub_value
FROM
tableA as A
LEFT OUTER JOIN TableB as B
on a.a_id=b.a_id
and B.sub_value <25
GO
SELECT a.value, b.sub_value
FROM
tableA as A
LEFT OUTER JOIN TableB as B
on a.a_id=b.a_id
where
B.sub_value <25
So - although it's just a prettiment with INNER JOINS, don't get sloppy, because you'll get bitten if you ever switch one....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 4:33 pm
i'm not disagreeing with you matt regrading outer joins. i'm simply pointing that in some complex queries with inner joins, the execution plan (and performance) can be changed by moving selectivity criteria to the join clause. 😉
April 3, 2008 at 4:54 pm
I think it's worth noting here that what you see happening in Matt's example, is that for the OUTER JOIN, if you reference the outer table in the WHERE clause, the SQL optimizer treats it just like an INNER JOIN. In order for the optimizer to apply the filter criteria in the WHERE clause, the row value must exist and be NOT NULL, which is, of course, an INNER JOIN.
When he applies the filtering criterea in the for the outer table in the FROM clause, you truely get the LEFT JOIN results you'd expect with the NULL value A_id 3.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply