The Merge Join transformation provides an output that is generated by joining two sorted data sets using a FULL, LEFT, or INNER join. The Merge Join transformation requires that both inputs be sorted and that the joined columns have matching meta-data. User cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.
USE pubs
GO
SELECT a.au_id
FROM authors a
JOIN titleauthor b ON a.au_id = b.au_id
OPTION (MERGE JOIN)
GO
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm. Because the query optimizer usually selects the best execution plan for a given select statement, it is not necessary to enforce the desirable join type, but sometimes it can be useful. User can enforce the desirable join type by using the OPTION clause.
Following query will be benefited by MERGE JOIN because authors and titleauthor both has primary key index on au_id. Due to Primary Key on au_id it is physically sorted by au_id.
This article is the in-depth analysis of the MERGE JOIN. Very informative.
Reference : Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Hey Pinal
congratulations for the MVP
now in sql server 2005
can we merge the output of 2 queries or 2 tables using the merge join option
or it exists only in SSIS
if it exists in sql server 2005 please show me a valid example
the one u showed above in giving error
Hey Pinal
Can we send mail via SQL Sever 2005 without complication? Can you show me how to attain this? Expecting your reply…
Excellent explanation!
hi
i have 2 question:
1. “If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged” -> why?
2. The Merge Join Component is different from the Lookup Component in that it does not support OLE DB
or ADO.NET connections to the reference dataset.->why?
Pls hepl me.
thanks!
I have 2 database having the same table and columns
database is expressed as follows
First Database is :
create database one
use one
create table enter
(
AreaAllotted int,
Location varchar(200),
State varchar(200)
)
create table Registration
(
RegID varchar(50),
FName varchar(50),
LName varchar(50),
Address varchar(50)
)
Second Database is :
create database two
use two
create table enter
(
AreaAllotted int,
Location varchar(200),
State varchar(200)
)
create table Registration
(
RegID varchar(50),
FName varchar(50),
LName varchar(50),
Address varchar(50)
)
I want whenever i insert into enter table of database one it should fire a trigger that will insert in the Registration table .Database named two have its own data as well.
Both of the database has its own trigger on Enter table that will insert the value into Registration table.I have done thall these tasks yet
but the actual problem is i want to merge both the database .if enter table of database one has like this
AreaAllotted Location State
125 Bhilai C.G
and Registration table
RegId FName LName Address
R1 SS PP abc
and second database has value like this
AreaAllotted Location State
189 Durgapur Calcutta
and Registration table
RegId FName LName Address
R11 tt jj xyz
after merging
so the result must be in this way
Enter table
AreaAllotted Location State
125 Bhilai C.G
189 Durgapur Calcutta
and Registration table
RegId FName LName Address
R1 SS PP abc
R11 tt jj xyz
Pls help me in this problem……………………
Hi,
I just read this article https://www.microsoft.com/en-us/download/details.aspx?id=51958 which says that merger join requires . I’m new for this and just want to clarify how it can related to Pinal’s sample code which doesn’t have WHERE.
Thanks again for you posts.
Dai
The WHERE clause is specified by the ON part. Note that this also satisfies the requirement that the WHERE clause is an equality.