Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910»»»

SQL & the JOIN Operator Expand / Collapse
Author
Message
Posted Thursday, October 8, 2009 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 4:41 PM
Points: 3, Visits: 151
vliet (10/8/2009)
Since the 'or' operator has lower precedence as the 'and' operator, when you ommit the parentheses the join condition means something completely different. Without parentheses the predicate behind the 'or' is evaluated separately, which leaves out the 'a.id = b.id' part completely, thus returning all records from 'a' for each and every record where 'b.date2' is empty. This might blow up the size of the result set a lot when a has many rows and b has many rows without a date in this column. Never ommit any parenthenses whenever you use both 'and' and 'or' in a predicate. You can never use too many parenthenses in these cases, computers don't know what you mean, they just do what you tell them to, but that's generaly not what you want.

I've seen predicates with both the 'and' and 'or' operator being the cause of 'human' errors in SQL so often, it's almost worth an article.



Thanks for the information. I understood what you have explained.
But in above case, b.date field has no nulls and I am getting same results.
I think it was a precautionary statement. I will check the requirement and try to add parentheses.
Post #800165
Posted Thursday, October 8, 2009 4:50 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Excellent article.

I'll echo several other posts and point out I frequently use cross joins for test data or simply when I need to have a huge number of rows for some reason (such as generating a tally table).

Also, remember that you can place indexes on views (if certain conditions are met, described in books oline and at http://www.sqlservercentral.com/articles/Indexed+Views/63963/ )


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #800422
Posted Friday, October 9, 2009 7:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
superlangerkerl (10/7/2009)
It was a good article, however these are fundamental types of joins. I would like to have seen something on hash joins and merge joins.
I had noted your post and then noted that nobody responded. There is a difference in the type you mention that almost puts them outside this kind of article.

I was about to spout off and say that these are things available to the query optimizer only. You don't get to tell SQL Server how to join only what to join. Then I did some more research and found out that you CAN tell the query optimizer what to do. There is this thing called "Join Hints". But Microsoft says:
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints, including <join_hint>, be used only as a last resort by experienced developers and database administrators.
For the most part I tend to agree. Then understanding what goes on under the hood (bonnet) and why the query optimizer picks what it does can be helpful.

From MSDN:
Join Hints
Understanding Hash Joins
Understanding Nested Loops Joins
Understanding Merge Joins

Those links will get you started.








ATB

Charles Kincaid

Post #800755
Posted Monday, October 12, 2009 5:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 13, 2011 1:46 PM
Points: 10, Visits: 64
Hello,

Been thoroughly enjoying your article on SQL JOINs. Thank you so much for this information.

Regards,

Mike G.
Seattle, WA
Post #801881
Posted Tuesday, January 26, 2010 1:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 12:16 AM
Points: 4, Visits: 231
<!>
Post #853944
Posted Friday, February 25, 2011 12:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:23 AM
Points: 5, Visits: 40
Hi All,

I have a question on joining more than 2 tables.

When we have more than 2 tables to join what is the order of execution :

For eg :

select ...
from table t1 inner join table t2
on t1.a = t2.a
inner join table t3
on t2.b = t3.b

I think it should join t1 & t2 first and then t2 & t3 to further filter the records from the join of t1 &t2.
And if the query is like

select ...
from table t1 inner join table t2
on t1.a = t2.a
inner join table t3
on t1.b = t3.b

Here how it goes? First join First, which i think but just want to confirm.

Thanks.
Sunny
Post #1069424
Posted Friday, February 25, 2011 6:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:51 AM
Points: 1,854, Visits: 3,451
Good article

The only thing I'm missing is a description of the other "join like" operators, like APPLY, INTERSECT and EXCEPT, but I know it's hard to cover everything in one article.
Guess there will be a follow up
Post #1069555
Posted Friday, February 25, 2011 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 25, Visits: 300
Thanks for the informative article. As a newbie this helped me tremendously!
Post #1069559
Posted Friday, February 25, 2011 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:00 AM
Points: 29, Visits: 226
Nils

Thanks for your comments. You know this 2nd article is in my list, hope I have time to work on it soon :)
Post #1069565
Posted Friday, February 25, 2011 7:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:00 AM
Points: 29, Visits: 226
Sunny

Usually the query optimizer will evaluate the different sequences when defining the execution plan. So, considering standard situations, your query's performance will be same in both cases.
Post #1069589
« Prev Topic | Next Topic »

Add to briefcase «««678910»»»

Permissions Expand / Collapse