Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajani
Constraint Violating Yak Guru
367 Posts |
 Posted - 2010-06-14 : 18:12:02
|
Hiplease see my query belowSELECT [firstnames], [surname], (SELECT [name] AS [DATA()] FROM [dbo].[u_town] AS town WHERE [town].townid = [dbo].[u_master].[FK_TOWN_TOWNID] FOR XML PATH('')) FROM [dbo].[u_master]here town is parent and master is child.all the query doing is get all patient records and any town info.when i turn on graphical execution plan it shows that its doing inner join between master and town but whats interesting is i get patients records with no town info filled in too (ie. [FK_TOWN_TOWNID] is null), what am i missing here ? (even though its showing inner join output results behave like left outer join!)i dont see any option to upload my graphical execution plan to this thread so let me know if you need any further info. Thanks for the advise.Cheers |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-15 : 19:35:36
|
| This is the expected behavior and here is why. Your outer SELECT on the u_master table is getting the data for the first two columns directly and is being used to get the third column indirectly. The outer select should return data for every row in the u_master table.The inner subquery on u_town is trying to return actual data if it can be found. Since this data is only returned if a match can be found for both the u_master and u_town tables, this is indeed an INNER JOIN. In those cases when there is no matching data in the u_town table, the subquery dutifully returns no data for that row so the value displayed for that column for that row is Null.Quod erat demonstratum=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
 |
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2010-06-15 : 22:09:53
|
| thanks for the reply Bustaz, but isnot it graphical plan bit confusing ? it seems as though you would get rows with only matching town record.if i instead use "outer apply" for the 3rd column i get "left outer join" in the plan which makes sense when you look at results(you still get row even with no town key). the confusing part is both methods return exactly same data even though one plan shows inner join and other left outer. hope i made sense.Cheers |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-16 : 12:53:57
|
| It does seem a bit misleading at first. Look at it from the viewpoint that you are not performing a "normal" join of two tables but instead are using a "main" select with a default value of Null for the third column. Then you are adding (joining) data to it if it is available in your subquery. You are going to get a full set of rows from your main query on u_master. You get actual data from the subquery if the WHERE clause evaluates to true. This is only true if there is matching data in both tables, which is an inner join.If you do the outer apply, then you are explicitly doing an outer join operation. I assume that if you did a cross apply that you'd once again get an inner join operator.HTH - Sorry if I'm not making myself as clear as I'd like to be.=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
rajani
Constraint Violating Yak Guru
367 Posts |
Posted - 2010-06-17 : 00:05:38
|
| yes you are right Cross apply gives me exactly same plan (inner join). i understand what you are saying. when i saw the plan first time i thought i'd miss some rows but results show all. i thought ask question here to better understand whats happening here.Thanks for your time.Cheers |
|
|
|
|
|
|
why graphical plan showing inner join?