SQL SERVER – Explanation SQL SERVER Merge Join

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)

,
Previous Post
SQL SERVER – Restrictions of Views – T SQL View Limitations
Next Post
SQL SERVER – Replication Keywords Explanation and Basic Terms

Related Posts

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

    Reply
  • Hey Pinal
    Can we send mail via SQL Sever 2005 without complication? Can you show me how to attain this? Expecting your reply…

    Reply
  • Prof. Peter Fischer
    December 25, 2008 10:32 pm

    Excellent explanation!

    Reply
  • 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!

    Reply
  • 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……………………

    Reply
  • 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

    Reply
    • The WHERE clause is specified by the ON part. Note that this also satisfies the requirement that the WHERE clause is an equality.

      Reply

Leave a Reply

Menu