.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Merge Join vs. T-Sql Inner Join?

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server


I noticed this has been posted before - but could not get it working with the information on that post so here goes:

I have a T-Sql query that INNER JOINs 2 tables on one field - this returns 6,383 records.

(the one table contains 6,383 records and the other table 12 records)

However when I try to implement a data flow task with two data sources (one for each table) and sort the and then try to use the Merge Join transformation to sort and join them on the exact same field the result is 11,073 records.

What is going on here? logically this does not make sense to me ...

I.W Coetzer

View Complete Post

More Related Resource Links

repeating merge joins using full outer join - should be possible without sort, but cannot get the co

Hello,   I am trying to combine several source tables with Merge Join.  For the Moment I have three source tables all already sorted in the source query and the is sorted property with the correct keys set. (its an aggregate query with group by and sort by the key columns) Then I do a merge join between two of the tables - with a full outer join. Then I just want to do another merge join with the output of the next result. but as the merge join do not combine the key columns into one column I need to add a derived column which substitute null values on the left side of the join with values from the right side of the join. The second merge then says only the left columns of the first merge join is sorted, but not the comined columns. If i Replace with a derived column the left side primary key columsn with the combinded key columns the next merge join says the input is not sorrted? But the output must be sorted. So how do I get the next merge join working without the ssis sort transformation? the data is sorted, but the component does not recognize the sorted data? I do not want to use (can use) the sort because there are flowing >= 500 Mio. records through the pipeline. Any help appreciated.   Hanneshttp://www.hmayer.net/

Merge Join: Full Outer Join - keep key values in case of no-match

I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

Removing columns after Merge Join ?



I have the scenario (using a third party component) where certain columns must not be present as input.

I am doing a Full Outer - Merge Join.

This results in:



but now in some cases the LeftPK may be  null in other cases the RightPK may be null,

so i'm creating a derived column to check:

ISNULL(LeftPK) ? RightPK : LeftPK

and call this the TruePK

this all works however now i'm left with 3 columns

LeftPK; RightPK; TruePK

Is there some kind of transform (other than script task) that i can use to get rid of Left and Right PK columns before sending input to next third party component?

I.W Coetzer

Merge Join Failue


I'm working with SQL 2008 SSIS.  I'm trying to join two tables on email fields.  For test purposes I have copied some of the email addresses from table A to table B to insue I have matching values.  The OLE DB Source components each use a SQL query that sorts the output by the email addresses in ascending order.   For each of the two source components I've set the IsSorted property to True and set the Output Columns SortKeyPosition property to 1.  Within the SQL query I've also added trim functions to eliminate any possible extra spaces.  Since for testing purposes table B's email addresses are copies of email addresses in table A there is no issue with case.   When linking the two outputs using the Merge Join flow component I do not get any matches.

I've tried sorting using a Sort component rather than within the OLD DB Source query, but it seems to lock up on one of my flows (about 1.5 million records) and won't proceed.

At this point I don't know what else to try. 

Merge join and IsSorted for joining more than 2 inputs


I have an XML file like this:


 <e1 myvalue="test">



   <detail value1="hello" value2="world" />

   <detail value1="

Merge join in SSIS

how to use merge join in SSIS?

Merge Join in SSIS 2005 Vs 2008



I have a process which reads from 2 tables (using two connection managers) and "merge join" them to a third table by using left outer join.

This process is implemented on SSIS 2005 and SSIS 2008 identically, however, for some reason I get less records at the third table at the 2008 process.

When implementing this merge join on Sql Server 2008 and Sql Server 2005 I get the same result as I got at the SSIS 2005 process - so it seems like the problem is at the SSIS 2008 process.
I've checked the merge join properties - they look similar on 2005 and 2008.
Does anyone knows if the 2008 merge join object should be configured differently than the 2005 merge join?
I have nulls on some the fields that connect the two tables - do you think it's got to do with it?
What else can I check?

Join Two Tables and Prepare Report



            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.

How do I join those two tables and get that field value in the existing select query.?


Linq join fields for return data


  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....

How to join two sharepoint list in c# by CAML query?

Hii All,
   I am using wss 3.0 and i have one site and site has 4 to 5 lists. lists are interconnected with lookup column and same column. I need to display some column fron one list and some from another.. i need to join that lists by where condition like project =project..

So how can i join??

Thank You

Cannot properly join lists in SPD 2007


Ok, I have been reading all sorts of posts and such for about a week and still have no come up with any concrete answers.


I cannot properly join lists in SP 07. I say properly because I never get the option to select a field to associate one with the other. 

I select "Create new linked data source" from Data Source Library; Pane opens; Left Click "Configure Linked Data Source"; Add 4 to 5 lists from "Current Site"; Select Next; Select Join option but Next is greyed out. Is this supposed to be greyed out because they are lists? Can lists be joined? I see alot of examples on joining databases and excel pages but not too many on lists.

If I select next from the last point, open the linked sources and select my newly created linked source and click to show data, I can see all of the information in there. Problem is I don't believe the lists are really joined by anything since I never received an option for it. 

I also have been reading up on joined subviews but I never receive an option for  joined subview. Is the joined subview the way to go for lists? If so how?


I know these boards are fairly active and any information or help would be greatly appreciated.

insert into in combination with inner join


Hi All,

I have my data split in 3 tables for cascading dropdownlists.

How can I make an update statement of this select statement?

SELECT distinct BIER.Naam as naam1, BIER_L2.L2_Naam as naam2, BIER_L3.L3_Naam as naam3
	from BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID 



Join 2 flat file data flows - retain unmatched rows

I have two data flows from two separate flat files. They may contain matching IDs (account number), in this case specific data from each flow should be used to create one row. When there is no match, the rows would stand on their own. At the end of the flow, I need both flows combined into one flow, with one record for each key record (account number). If I were able to use a look-up, I could easily union the no-match data flow back into the match data flow and have the desired result. I cannot use a look-up, since the source is flat files, but this is exactly the functionality I am trying to achieve. Solutions I want to avoid: staging tables, and cache transformations. Any ideas are appreciated.

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

problem with inner join

Hi, I'm trying to create an sql query with multiple inner joins, but I can't manage this. I have 3 tables (bookings, accountplan, costs), where the data come from. In accountplan are hold the accounts, in costs the cost codes and their names, in bookings the booked events. Based on a booking event, I need the account name, the name of the cost code and all data from bookings (I need the cred account name too, but I can't figure out yet, how I can get this) I tried this: "SELECT bookings.*, accountplan.accountname, costs.costname FROM bookings INNER JOIN accountplan ON bookings.debaccount=accountplan.account INNER JOIN costs ON bookings.costcode=costs.costcode" It is here a where clause too, but I tested it, it's ok. The above query results no rows. If I remove the costs table and the inner join with it, it works. What am I missing?

Need help on populating data on a datagrid using SQL Join

I have a table called Leaves. Fields are,LeaveIDPersonIDActingPersonIDIn Person's table, I have,PersonalIDEmployeeCodePersonNameNow, in the datagrid, I need to show,PersonEmployeeCodePersonNameActingPersonEmployeeCodeActingPersonNameNow, I know how to write the SQL Join to get the data populated. But, the problem is, in some cases, there won't be any acting person. In which case, I need to display data, PersonEmployeeCode = 0001 PersonName = John ActingPersonEmployeeCode = - ActingPersonName = No acting Person Now, I am stuck here. How do I do a conditional join statement?Please help! RegardsKush
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend