Join vs Where

Question

Join vs Where

chileu17

Hall of Fame

Points: 3093
More actions
April 21, 2010 at 8:58 am

#223724

Hi everyone, today I am having one those days where you just start questioning everything lol so I started with joins and wheres. Please correct me if I am wrong, I as far as I know to create a join, a Cartesian product is made out of the records from all the tables we are joining, and to make a join, we could use the ANSI-92 standard (joins) or the undocumented one (where). And besides that sql 2k5 optimiser uses a similar plan to execute both of them.
So would here be a technical/theoretical difference between these two join options???
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Toby White SSCertifiable Points: 6187 More actions · Answer 1

I suggest looking at the actual execution plan for figuring out whether the optimizer did anything different. You are correct that the optimizer can and will take liberties with T-SQL and factor it in the best (usually) way based on statistics and indexes. That being said, it has the most flexibility with join semantics than Where.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

chileu17 (4/21/2010)
to create a join, a Cartesian product is made out of the records from all the tables we are joining, and to make a join, we could use the ANSI-92 standard (joins) or the undocumented one (where). And besides that sql 2k5 optimiser uses a similar plan to execute both of them.
So would here be a technical/theoretical difference between these two join options???
Thanks in advance.

It depends on how you write out your Join as to whether or not a Cartesian product is made.

The columns in the join are sometimes interchangeable with the where clause if both are present in a query. The where clause is more of a filter for whether a join is present or not. The Join brings separate tables together to create a single result set.

To join tables I would explicitly define the Join and columns to be joined on and then use the where statement as a filter.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 3

I would follow up Jason's point with a simple question (once you understand more about the different kinds of joins):

How do you replicate a LEFT JOIN using the where clause? (where all rows from the left table are included, and all rows from the right that match to the left, PLUS NULLs where rows don't match to the left-hand side)

That should be enough to show why you don't want to use the WHERE clause for your joins. Was for me, anyway.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

chileu17 Hall of Fame Points: 3093 More actions · Answer 4

It depends on how you write out your Join as to whether or not a Cartesian product is made.
The columns in the join are sometimes interchangeable with the where clause if both are present in a query. The where clause is more of a filter for whether a join is present or not. The Join brings separate tables together to create a single result set.
To join tables I would explicitly define the Join and columns to be joined on and then use the where statement as a filter.

Hi there, thanks for your replies.

But I wasn't really talking about what each clause is used for. I was actually talking about if there is a difference in the way sql server implements the process of joining two tables. We know we can join two tables with the join clause and by using the where clause. Like jcrawf02 pointed out, there are some features that can be used by following the join 92-ansi standard, and that the where (an undocumented join standard) does not provide.

And yeah Toby, the sql server 2k5 optimiser does decide how it executes both types of join. They use the same execution plan.

Any further ideas????

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

From that perspective then, no I don't believe that there is a difference.

SELECT myTable.myColumn, yourTable.yourColumn

FROM myTable, yourTable

WHERE myTable.myColumn=yourTable.yourColumn

should be treated the same as

SELECT myTable.myColumn, yourTable.yourColumn

FROM myTable

JOIN yourTable ON myTable.myColumn=yourTable.yourColumn

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

chileu17 Hall of Fame Points: 3093 More actions · Answer 6

Yeah, they are same. It is just the sintax that is different 😀

DBMS implement them in the same way, and of course sql 2k5 optimiser is clever enough to realise that we are working with a join statement either way.

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 7

jcrawf02 (4/21/2010)
How do you replicate a LEFT JOIN using the where clause?

Since you asked (compatibility level 80 or lower):

WITH CTELeft

AS

(SELECT 1 AS ID UNION

SELECT 2UNION

SELECT 3UNION

SELECT 4),

CTERight

AS

(SELECT 1 AS ID UNION

SELECT 4)

SELECT *

FROM CTELeft, CTERight

WHERE CTELeft.ID *= CTERight.ID

Chris

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 8

Chris Howarth-536003 (4/27/2010)
jcrawf02 (4/21/2010)
How do you replicate a LEFT JOIN using the where clause?
Since you asked (compatibility level 80 or lower):
WITH CTELeft
AS
(SELECT 1 AS ID UNION
SELECT 2UNION
SELECT 3UNION
SELECT 4),
CTERight
AS
(SELECT 1 AS ID UNION
SELECT 4)
SELECT *
FROM CTELeft, CTERight
WHERE CTELeft.ID *= CTERight.ID
Chris

Ha! Thanks Chris. 😉

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."