InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 11:57:13 GMT20http://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxA real world application of cross join is to generate the results of applying databased rules to the row occurrences they apply to. For example, I have an application that tracks the set up of standard AD Groups for database schemas. I have the rules for these groups modeled in tables, and then cross join the rules with the schemas to generate a result set that applies each rule to each schema. I output this result set to a tracking table so I can track the set up of all these AD groups. This of course is only effective because there are a small number (less than 100) of schemas for which to manage a small number of AD groups (around 8 per schema).Sat, 26 Mar 2011 08:24:08 GMTTodd.Everetthttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxExcellent job, Wagner! This reminds me of the first truly useful article on SQL that I found back in 199...7, I think. It was a beautifully detailed article describing normalization techniques to 3NF. This would have been a grand companion to it (all those years ago...) when I first started in the field. I plan to use this article when I explain these concepts to friends and coworkers who want to understand how JOINs work.Bravo!---Mike<G>Wed, 02 Mar 2011 13:59:50 GMTMike M - DBA2Bhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]SQLkiwi (2/27/2011)[/b][hr][quote][b]Jeff Moden (2/27/2011)[/b][hr]Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good [i]introduction [/i]to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... ;-)[/quote]The parallels weren't lost on me, no. That's no reason not to comment though, is it?[/quote]Heh... you're absolutely correct. I read more into it and you were a whole lot nicer about it than that other fellow. Thanks, Paul.Mon, 28 Feb 2011 07:36:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxGotcha! So more of an Ivory Tower nomenclature. Thanks for the speedy clarification.Mon, 28 Feb 2011 06:26:29 GMTrobmullinshttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxrobrelation is a term we generally use in academic environment and I guess this was the word Ed Codd used when he define the basics for relational modeling. in day-top-day business, we assume the word TABLE describing the same (or roughly the same) concept.Mon, 28 Feb 2011 06:23:55 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxI was just going over this in my class, but you explain it much better! Great work! Out of curiosity, I have been hammered on calling it a 'relation' instead of a 'table'. I know the terms are somewhat interchangeable but is there a hard-fast rule for what we call them?Thanks in advance.Mon, 28 Feb 2011 06:17:40 GMTrobmullinshttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]wagner crivelini (2/28/2011)[/b][hr]Well, Paul, I agree with most of your observations, but Jeff is right: the purpose of this article is to introduce the newbie to a new world, SQL Joins.[/quote]Ok, I hope the criticisms were taken in the constructive spirit they were intended.Mon, 28 Feb 2011 05:42:17 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]SQLkiwi (2/27/2011)[/b][hr][quote][b]Jeff Moden (2/27/2011)[/b][hr]Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good [i]introduction [/i]to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... ;-)[/quote]The parallels weren't lost on me, no. That's no reason not to comment though, is it?[/quote]Well, Paul, I agree with most of your observations, but Jeff is right: the purpose of this article is to introduce the newbie to a new world, SQL Joins.If we were talking about motor vehicles instead of SQL, the articles could focus on strollers as well as in Ferraris. I'm focusing on stroller drivers here and I think it would be pointless for this audience to hear some many important details at this moment.Mon, 28 Feb 2011 03:47:25 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]Jeff Moden (2/27/2011)[/b][hr]Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good [i]introduction [/i]to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... ;-)[/quote]The parallels weren't lost on me, no. That's no reason not to comment though, is it?Sun, 27 Feb 2011 19:33:40 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxJamesThe SQL statement was supposed to do it from the first to the last join. Actually I understand that's the way things worked out in old RDMBSs. But SQL SERVER has a powerful Query Optimizer that evaluates the possibilities to choose the best combination of tables so to optimize query performance.You can do yourself a simple test to check that. Try running the following queries. This is a simple query (running on ADVENTUREWORKSLT2008) written in two different forms, changing the sequence of the JOINs.[size="3"][font="Courier New"]USE AdventureWorksLT2008 GOSELECT C.CompanyName, P.Name AS Product, SUM(SD.OrderQty) AS OrderQtyTotalFROM SALESLT.CUSTOMER C INNER JOIN SalesLT.SalesOrderHeader SH ON SH.CustomerID = C.CUSTOMERID INNER JOIN SalesLT.SalesOrderDetail SD ON SD.SalesOrderID = SH.SalesOrderID INNER JOIN SalesLT.Product P ON P.ProductID = SD.ProductID GROUP BY C.CompanyName, P.NameGOSELECT C.CompanyName, P.Name AS Product, SUM(SD.OrderQty) AS OrderQtyTotalFROM SALESLT.Product P INNER JOIN SalesLT.SalesOrderDetail SD ON P.ProductID = SD.ProductID INNER JOIN SalesLT.SalesOrderHeader SH ON SD.SalesOrderID = SH.SalesOrderID INNER JOIN SalesLT.Customer C ON SH.CustomerID = C.CUSTOMERIDGROUP BY C.CompanyName, P.NameGO[/font][/size]If you compare the execution plan of each query, you'll see they're exactly the same (due to the work of the QUERY OPTIMIZER).(I looked for some reference to show you this idea and only found this pretty short explanation http://bytes.com/topic/sql-server/answers/142445-order-joins-performance)Sun, 27 Feb 2011 14:08:38 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxPaul,Isn't this a bit like the article you wrote on designing databases? While I agree with all the things you've stated, entire books have been written on how to join and all the caveates that occur when dupes are present, etc, ad infinitum. This article is a good [i]introduction [/i]to joins... it's not the final word just like your introduction to designing databases isn't and wasn't intended to be the final word. Think about it... ;-)Sun, 27 Feb 2011 07:37:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxVery nicely presented article, but the content is not quite right in places.The set intersection is useful so long as your are clear that you are talking about sets of keys (i.e. unique join column values). If you don't make that distinction, or omit to go on to describe how one-many and many-many joins looks, it seems misleading. An INNER JOIN is not the intersection of two sets (that would be INTERSECT). The example data only uses unique join key values in both tables, which could reinforce any misconception.I appreciate the value of simplification to introduce a concept, but the set-intersect visual might promote unclear understanding. The same issue applies to the coverage of LEFT, RIGHT, and FULL OUTER JOIN. There's no mention of how duplicates are treated, or how rows are NULL-extended when a match does not occur. Again, I feel the Venn diagram approach leads us astray here, unless it is very clearly pointed out that you are visualizing sets (unique by definition) of join keys.The article also describes the cross product of Table1 and Table2 as "...each record of Table1 will be duplicated for each record in Table2". This suggests that only Table1's rows will appear duplicated in the output, which is clearly not the case. It's not wrong exactly, but it is incomplete and imprecise.The section 'Excluding the Intersection of Sets' recommends the least efficient method (LEFT JOIN). NOT EXISTS is generally preferred because it can stop searching as soon as it finds a match. The semantic is different, of course, because LEFT JOIN ... WHERE NOT NULL can produce duplicates whereas NOT EXISTS will not. 'Excluding the intersection' has an air on set theory about it, but it isn't a very good description for returning rows from Table1 where a matching row does not exist in Table2.In the same section, you say: "When we do this kind of query, we have to pay attention to which field we pick for the WHERE clause. We must use a field that does not allow NULL values. Otherwise the result set may include unwanted records. That's why I suggested to use the second table's key. More specifically, its primary key. Since primary keys don't accept NULL values, they will assure our result set will be just what we needed." This is a common misconception, which you should have tested before publishing. There is no problem with performing an outer join on NULLable columns: NULLs never join (regardless of the ANSI_NULLS setting), so there is no possible confusion between a 'join matched' NULL and a NULL resulting from the NULL extension of a row by the outer join.The section 'One Word About Execution Plans' starts with "These comments lead us to an important insight. We usually don't stop to think about this, but observe that the execution plan of SQL queries will first calculate the result set for the FROM clause and the JOIN operator (if any), and then the WHERE clause will be executed". This is logically true, but not physically. If it were, we would never see an index seek in a plan with joins! The SQL Server optimizer is free to reorder expressions, transform the plan, and push filters (predicates) down past joins if it results in a lower cost query. The only reason the filter in your LEFT OUTER JOIN example can't be pushed past the join is because it is testing NULL-extended rows resulting from the join.In 'Joins and Indexes', you say: "On the other hand, Table1 had no index on field key2. Because of that, the query optimizer tried to be smart enough and improve the performance of querying key2 using the only available index." - This is nonsense. It isn't the optimizer trying to be smart - there is no other access method available aside from scanning the clustered index. There's no such thing as a table scan on a clustered table.The ability to perform anything other than an equi-join is not a 'cosmetic feature' - such joins are common where we need to produce a running total or a match on a range of dates, for example. The problem with non-equal (inequality) joins is NOT that they usually duplicate records - it is that performance tends to be worse because more rows will match.Under 'Multiple Joins', you write: "Just remember that joins are written in pairs. So first we will join Table1 to Table2. And then we will join Table1 and Table3". The query optimizer chooses an initial join order based on table cardinality and other heuristics - it does not normally follow the written query order (though you can force that by including the FORCE ORDER hint, setting FORCE_PLAN ON, or by hinting a specific physical join operator e.g. INNER HASH JOIN). In the main, the optimizer picks an initial order, and then applies transformations which may result in further changes to the physical join order.Consider reading my optimizer series starting at [url]http://sqlblog.com/blogs/paul_white/archive/2010/07/29/inside-the-optimiser-constructing-a-plan.aspx[/url]PaulSat, 26 Feb 2011 20:36:12 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxPermapGood catch! I'm correcting it now.ThxSat, 26 Feb 2011 16:14:11 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxOne question: I thought that when you used multiple JOINs that SQL does the first join between Table1 and Table2 and then joins the result set of that join to Table3. Is this correct? Your article seems to say that SQL joins Table1 to Table2 and then joins Table1 to Table3 and then I assume it joins the result set of those two joins. Could you clarify? Is there some "order of operations" when it comes to JOINs?Thank you for the article! Really like the Venn diagram visual aid.Sat, 26 Feb 2011 08:55:27 GMTJames Ghttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxExcellent article using Venn diagramsSat, 26 Feb 2011 08:46:48 GMTksjayadevhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxGood article.Here's a possible correction. For the Self Join section, there is the output to the query. See the fourth record which is "7, Peter, 8, Harry". Isn't it supposed to be "7, Peter, 6, Mark"?Thanks.Fri, 25 Feb 2011 18:19:50 GMTpermaphttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxWell done with this article.Fri, 25 Feb 2011 14:22:49 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]arun55 (10/8/2009)[/b][hr][quote][b]vliet (10/8/2009)[/b][hr]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.[/quote]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.[/quote]If Table B can contain a start but no end date (assumption given inferred use) this is important.Your 40 minute query is returning information based on two specific possibilities:1) a.id = b.id and a.date1 between b.date1 and b.date22) b.date2 is nullThis would return the same data as the following more complex and less efficient query:select a.*,b.*from table aleft join table bon a.id = b.idand a.date1 between b.date1 and b.date2unionselect a.*,b.*from table a, table bwhere b.date2 is nullLooking at the second select this is a cross join. After creating this cross join it then goes through each record Count(A)*Count(B) and select those where date2 is null (0 per your statements). So this entire cross join would be generated then ignored. You are paying the cost to create this cross join and then ignore it. Your results are the same given your data set, but this doesn't mean your queries are equal. They could change after your next transaction.You might consider a third query depending on your expectation.This requires that a.date1 occur after b.date1 but b.date2 may be nullselect a.*,b.*from table aleft join table bon a.id = b.id and a.date1 >= b.date1 and a.date1 <= isnull(b.date2,getdate())This query allows a.date1 to predate b.date1. This may not be desired.Arun55 modified.select a.*,b.*from table aleft join table bon a.id = b.idand (a.date1 between b.date1 and b.date2 or b.date2 is null)Fri, 25 Feb 2011 12:25:12 GMTtimothy bateshttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxEnjoyed the article and found the diagrams easy to follow.Fri, 25 Feb 2011 11:49:58 GMTgregg_dnhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxSunnyUsually 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.Fri, 25 Feb 2011 07:32:02 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxNilsThanks for your comments. You know this 2nd article is in my list, hope I have time to work on it soon :)Fri, 25 Feb 2011 06:55:56 GMTwagner crivelinihttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxThanks for the informative article. As a newbie this helped me tremendously! :-)Fri, 25 Feb 2011 06:47:19 GMTCoffeyhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxGood 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 :-DFri, 25 Feb 2011 06:36:51 GMTNils Gustav Stråbøhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxHi 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 t2on t1.a = t2.ainner join table t3on t2.b = t3.bI 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 likeselect ...from table t1 inner join table t2on t1.a = t2.ainner join table t3on t1.b = t3.bHere how it goes? First join First, which i think but just want to confirm.Thanks.SunnyFri, 25 Feb 2011 00:34:29 GMTsunny.dagliahttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx<!>Tue, 26 Jan 2010 13:42:38 GMTijtroquimhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxHello,Been thoroughly enjoying your article on SQL JOINs. Thank you so much for this information.Regards,Mike G.Seattle, WAMon, 12 Oct 2009 17:03:11 GMTmgodinezhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]superlangerkerl (10/7/2009)[/b][hr]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.[/quote]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 [b][i][u]how[/u][/i][/b] to join only [b][i][u]what[/u][/i][/b] to join. Then I did some more research and found out that you [i]CAN[/i] tell the query optimizer what to do. There is this thing called "Join Hints". But Microsoft says:[quote]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.[/quote] 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:[url=http://msdn.microsoft.com/en-us/library/ms173815.aspx]Join Hints[/url][url=http://msdn.microsoft.com/en-us/library/ms189313.aspx]Understanding Hash Joins[/url][url=http://msdn.microsoft.com/en-us/library/ms191318.aspx]Understanding Nested Loops Joins[/url][url=http://msdn.microsoft.com/en-us/library/ms190967.aspx]Understanding Merge Joins[/url]Those links will get you started.Fri, 09 Oct 2009 07:57:16 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxExcellent 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/ )Thu, 08 Oct 2009 16:50:25 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]vliet (10/8/2009)[/b][hr]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.[/quote]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.Thu, 08 Oct 2009 10:15:37 GMTarun55http://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]Andy DBA (10/7/2009)[/b][hr][quote][b]Jeff Moden (10/7/2009)[/b]Now, there's a suggestion for a follow up article.[/quote]Sure, hint hint. I think it's old ground, though.http://www.sqlservercentral.com/blogs/tim_mitchell/archive/2008/05/29/using-in-with-possible-null-values.aspxhttp://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/http://www.sqlservercentral.com/articles/Advanced+Querying/2829/[/quote]Wow... thanks for the reminder especially about Michael Cole's fine article.Thu, 08 Oct 2009 08:01:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxWagner this is a great article. I gave it all the stars. Then look at the other results. Seven pages on the forums. If you have been around this site for a while you may have spotted that articles do 2 things. They, in themselves, educate, illustrate, inform and provoke. The last triggers the second thing. They get conversations [b][i][u]going[/u][/i][/b].Thu, 08 Oct 2009 07:09:53 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]davidpenton (10/7/2009)[/b][hr][quote][b]Jeff Moden (10/7/2009)[/b][hr]Heh... of course, I will admit that, no matter which method someone uses to identify the column/table relationships, just about anything would be better than a format like the following (taken from a recent post)......from tbl_LeaveStatistics,tbl_leavedetails,tbl_LeaveTypeMaster,tbl_EmpMaster,tbl_UserTypeMaster,tbl_RankMaster [/quote]I agree. How about that sickly hungarian notation for database objects? That kind of stuff makes me sad :)[/quote]Agreed. I especially "love" such notation when the demands of the code require a change to a view or an inline table function (or vice versa). :-)Thu, 08 Oct 2009 07:07:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxActually, Cris, I was referring to you (Chris.Strolia-Davis). The way UNION (and ALL) works is to take multiple result sets and merge them together. It almost does not care where the result sets come from. Look at the UNION statements done by folks on this forum when setting up example tables. They often UNION rows that come from fixed data and not tables at all. You can get results from tables and UNION that with results from a stored procedure and UNION that with results from a function or CLR procedure.I don't think that I'm still being clear. Let me put this statement out her and see how much fire it draws. The UNION of result sets takes place [i][u]after[/u][/i] all the JOINS have happened. In that sense UNIONs are not JOINs at all.Thu, 08 Oct 2009 07:05:56 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxExcellent article. I have a real-life example of the need for a Cross Join in production. Inside my stored procedure, it is necessary that I check conditions against each company/state/county combination.I have a table of unique companies and a table of unique state/counties. With a cross join, I can create a derived table that I use to check conditions for each unique combination.Thu, 08 Oct 2009 06:23:56 GMTkwigganshttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]SQL Noob (10/7/2009)[/b][hr]i have a database where i dump logs into from 50 or so domain controllers, sql servers and other servers. each server has one or more tables. to get all failed logons from the last 2 days i query all the tables and link them by union all statements.would it be faster to just find something to join it on? i'm even looking at combining a lot of the tables into 3-5 tables and a few lookup tables instead of using union all[/quote]The possibility definitely exists for you to speed performance by creating lookup tables or adding appropriate indexes.When it comes to performance tweaking, it can be very hard to say what generally will or won't improve the speed of a query. Query analyzer will often help you find areas where the current queries are taking the longest time to perform and might help suggest where indexes might be most useful. I'm not sure if you've been using that or not.More information than would be practical in this forum would be needed to get an idea of what the best method might be for you. The methods used may involve tradeoffs. For instance, if you could get some of the querying done on the servers providing the data before they dump, you may be able to pair the data down a bit and reduce the overall time needed to complete your reports. This, of course, may not be practical in your environment.Thu, 08 Oct 2009 06:10:58 GMTChris.Strolia-Davishttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxSorry, I forgot the include the quote:[quote][b]sql.varun (10/7/2009)[/b][hr]Good Article,I have a question on Joins.Can anyone help me.When I run below query it is running for 40 mins as it is using Table Spool.[u]Query:[/u]select a.*,b.*from table aleft join table bon a.id = b.idand a.date1 between b.date1 and b.date2or b.date2 is nullModifing this query is running in 5 mins.select a.*,b.*from table aleft join table bon a.id = b.idand (a.date1 between b.date1 and b.date2 or b.date2 is null)But I am not sure whether both queries results same output.Can you explain how does it works while we use multiple join conditions using venn diagrams.(considering order of precedence)Thanks,Varun.[/quote]Thu, 08 Oct 2009 06:06:30 GMTvliethttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxSince 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.Thu, 08 Oct 2009 06:04:12 GMTvliethttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspx[quote][b]Charles Kincaid (10/7/2009)[/b][hr]Chris,Not exactly. The UNION / UNION ALL is kind of tough one to get wrapped around. UNION ALL combines two or more identically structured result sets into one. All rows from each result set are returned in the output. By contrast UNION (without the ALL) returns only distinct rows.Example: Say you were doing income tax preparation and had a table of clients for each year. CLIENT2007, CLIENT2008, CLIENT2009. Each table contains just name and taxpayer ID (SSN). Then your old buddy 'Joe Smith' '999-00-1234' came in all three years. Doing the UNION he would show up just the once but UNION ALL he wold appear three times.Terrible design that but I'm just trying to get over the one point.[/quote]Hi Charles, I assume that you are referring to my post earlier responding to SQL Noob. You might note that I was not pointing out any differences between UNION and UNION ALL, which I am quite aware of the difference of, but if you could replicate the effects of a UNION ALL in a JOIN and whether or not that might be more efficient. Perhaps my use of the word sets instead of tables made it unclear, but I use that since sometimes the data being unioned in is not always a table but could be a subquery which may include data from multiple tables, hashes, xml or constant values and are not technically identically structured. That's probably just semantics because the result is obviously homogenous. Anyway, it's still good for people to know that UNION ALL gets all data rows and UNION gets all distinct rows. I've definitely been burned on that one in the past. Thu, 08 Oct 2009 05:57:42 GMTChris.Strolia-Davishttp://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxits great article.Wed, 07 Oct 2009 22:50:57 GMTbrijesh.patel-1042612http://www.sqlservercentral.com/Forums/Topic798928-1448-1.aspxIt was a good article, however these are fundamental types of joins. I would like to have seen something on hash joins and merge joins.Wed, 07 Oct 2009 16:39:55 GMTsuperlangerkerl