Solved

merge join

Posted on 2010-11-17
10
588 Views
Last Modified: 2013-11-10
merge join in distinctively SSIS, not TSQL, right? how does it differ than the other kinds of join (inner, left, self etc)

thanks
0
Comment
Question by:anushahanna
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 167 total points
ID: 34161464
Its in both....but they are essentially the same idea.

Inner/Outer/Full joins are LOGICAL joins in the database engine.  Nested Loop/Merge/Hash joins are the physical representations of the logical joins.  Nested loop joins are two outputs, usually a large input and a small input and the small input loops into the large input.  Merge has to sort the two inputs and then merge them together to join them.  Works really well if the inputs are already sorted.  Hash is for the looping scenario OR for two large inputs that are not indexed.  

No, the merge in SSIS is similar to the merge join in the db engine in that it has to physically sort (preceeded by a sort task) before you can merge the results.

Clear as mud?  :)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 34161469
Correction:
No, the merge in SSIS is similar to the merge join in the db engine in that it has to physically sort (preceeded by a sort task) before you can merge the results.

...missed the w on Now.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34161893
as Champandew noted this is exactly like join types in t-sql.
there are these types there:
inner join --> like inner join in t-sql
full outer join--> like full outer join in t-sql
left outer join --> like let outer join in t-sql
there in no right outer join in merge join transform, but you can only change inputs and use left outer join to have right outer join .

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 166 total points
ID: 34161907
And note that when you want to use Merge Join Transform in SSIS , your inputs should be SORTED.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 83 total points
ID: 34162309
As indicated, MERGE JOIN is the physical method that SQL Server's optimizer may select to implement some of its JOIN implementations.   SQL MERGER JOIN is the procedural implementation of the SQL JOINS, which are themselves the implementations of the relational JOIN.

In SSIS, though there are order prerequisites that do not exists in the TSQL engine.

Hope this helps...
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34162782
thanks-

merge join in SSIS- is it possible to say 'No thanks for physical sorting - just inner join the 2 OLEDB input and give it in any sort order"
0
 
LVL 6

Assisted Solution

by:subhashpunia
subhashpunia earned 84 total points
ID: 34162825
Then use other type of joins: Either Nested Loop or HASH join.
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 166 total points
ID: 34163727
NO,
if you want to use Merge Join Transformation , so you SHOULD sort inputs.
if you don't want to sort inputs, you can use Lookup transformation ( if your inputs are oledb, and you have only ONE joining field) or use t-sql joins in OLEDB Source command if your inputs are both from single server.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 167 total points
ID: 34163742
>>merge join in SSIS- is it possible to say 'No thanks for physical sorting - just inner join the 2 OLEDB input and give it in any sort order"

Nope, you can't do a "merge join" without sorted inputs.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34165137
Thanks for the help on understanding the merge..
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
script to send html report 3 30
2016 SQL Licensing 7 40
What is the proper way to use for criteria in left join? 7 25
always on switch back after failover 2 31
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now