August 25, 2009 at 10:09 am
I'm building a dynamic SQL statement based on form inputs. For the purposes of my code, I'd like to just put all the WHERE clauses together.
Is there any actual difference between saying:
SELECT Columns
FROM TableA
JOIN TableB ON SomeColumnA = SomeColumnB
AND SomeClauseFromTableB
WHERE SomeClauseFromTableA
and
SELECT Columns
FROM TableA
JOIN TableB ON SomeColumnA = SomeColumnB
WHERE SomeClauseFromTableA
AND SomeClauseFromTableB
I realize that it makes more sense to put the clauses on Table B in the JOIN, but beyond that, is there any difference in the execution plan, query time, or the results?
August 25, 2009 at 10:15 am
The statements mean different things when you are dealing with outer joins. You won't get the same records.
For example:
select *
from TableA
Left join TableB on SomeColumnA = SomeColumnB
AND SomeClauseFromTableB
Where SomeClauseFromTableA
is equivalent to
select *
from TableA
Left join (select * from TableB WHERE SomeClauseFromTableB) TB
ON SomeColumnA = SomeColumnB
Where SomeClauseFromTableA
but
SELECT Columns
FROM TableA
JOIN TableB ON SomeColumnA = SomeColumnB
WHERE SomeClauseFromTableA
AND SomeClauseFromTableB
will eliminate rows from the "left" that don't match SomeClauseFromTableB
----------------------------------------------------------------------------------
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?
August 25, 2009 at 10:18 am
Yeah, I know that for Outer Joins they will, for my purposes I'm only looking at Inner Joins though.
August 25, 2009 at 10:20 am
The way I look at it is, the criteria for joining the tables belongs in the ON statement in the FROM CLAUSE and the filter criteria for the result set belongs in the WHERE clause.
August 25, 2009 at 10:20 am
As far as inner joins, I haven't experienced a difference. The argument is that the ANSI Join syntax clearly seperates join logic from filter logic, and I can see that, I just prefer the other way, but I'm coming around..
As far as OUTER joins, it makes a difference..
CEWII
August 25, 2009 at 10:23 am
What Lynn and Elliott said...:)
----------------------------------------------------------------------------------
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?
August 25, 2009 at 10:26 am
Perfect - that's what I figured, just wanted to make sure there wasn't any hidden workings going on behind the scenes that I didn't know about.
Personally, I always put the filters of the joins as part of the joins, just because it makes it easier for me to see which things are affecting which tables.
August 25, 2009 at 10:36 am
Okay, another question on the same note:
Is there any difference between:
SELECT Columns
FROM TableB
JOIN TableA ON SomeColumnA = SomeColumnB
AND SomeClauseFromTableA
WHERE SomeClauseFromTableB
and
SELECT Columns
FROM TableA
JOIN TableB ON SomeColumnA = SomeColumnB
AND SomeClauseFromTableB
WHERE SomeClauseFromTableA
August 25, 2009 at 10:40 am
Inner JOIN is permutative, so there really is no difference between the 2.
The engine makes its own determination what the "base" table is, so it really doesn't matter which you put first.
----------------------------------------------------------------------------------
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?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply