Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

merge join

merge join in distinctively SSIS, not TSQL, right? how does it differ than the other kinds of join (inner, left, self etc)

thanks
0
anushahanna
Asked:
anushahanna
  • 3
  • 3
  • 2
  • +2
6 Solutions
 
chapmandewCommented:
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
 
chapmandewCommented:
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
 
Reza RadCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Reza RadCommented:
And note that when you want to use Merge Join Transform in SSIS , your inputs should be SORTED.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
anushahannaAuthor Commented:
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
 
subhashpuniaCommented:
Then use other type of joins: Either Nested Loop or HASH join.
0
 
Reza RadCommented:
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
 
chapmandewCommented:
>>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
 
anushahannaAuthor Commented:
Thanks for the help on understanding the merge..
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now