Question about an AND statement in the were clsue

Question

Question about an AND statement in the were clsue

itmasterw 60042

SSCrazy

Points: 2852
More actions
November 13, 2014 at 8:33 am

#291962

I have someone telling me that I should have put my AND statement in the join instead of the where part, but canot tell me why.
so is there a difference in the where I put it.
Is there a difference in the results in any way between the two here in the results in anyway?
Example:
Givin a query:
But let’s take a simple one here:
Query(1)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
Where ct1.zipcode = '14124'
AND ct1.Name = 'Bob'
Query(2)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
AND ct1.zipcode = '14124'
Where ct1.Name = 'Bob'
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

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

g.britton SSChampion Points: 13862 More actions · Answer 1

No difference in results for inner join. It's a matter of style. Matching vs filtering. Usually put matching in ON clause and filtering in WHERE clause. Outer joins are different since they can return null values from the joined table. So, putting filtering in WHERE clause is required to get correct results.

Gerald Britton, Pluralsight courses

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

itmasterw 60042 (11/13/2014)
I have someone telling me that I should have put my AND statement in the join instead of the where part, but canot tell me why.
so is there a difference in the where I put it.
Is there a difference in the results in any way between the two here in the results in anyway?
Example:
Givin a query:
But let’s take a simple one here:
Query(1)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
Where ct1.zipcode = '14124'
AND ct1.Name = 'Bob'
Query(2)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
AND ct1.zipcode = '14124'
Where ct1.Name = 'Bob'
Thank you

Those two queries are definitely not the same thing. The first query will ONLY return rows where the two values in ct1 meet the criteria but the second query will return all rows where ct1.Name = 'Bob' regardless of the value of the zip code. It would only have values in ct2 when the zipcode is 14124.

Let's say you have two rows in the table with the name Bob but the zipcodes are 14121 and 12345. The first query will return 1 row but the second query will return 2 rows.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 3

Thanks that what I foung too but want to be sure I was seeing this right.

Thanks

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 4

itmasterw 60042

SSCrazy

Points: 2852

November 13, 2014 at 8:52 am

#1758284

Thank you