Professional Web Applications Themes

What is a merge join? - Microsoft SQL / MS SQL Server

I know this is obvious but what is a merge join and how does it differ from a normal join? Is this just jargon I have never come across before. And how does sorting take place in the two....

  1. #1

    Default What is a merge join?

    I know this is obvious but what is a merge join and how does it differ
    from a normal join? Is this just jargon I have never come across
    before. And how does sorting take place in the two.
    Chris Guest

  2. #2

    Default Re: What is a merge join?

    The merge join is a physical operator used by SQL Server and will usually be
    shown in the execution plans. This operator performs the actual join logical
    operation (like inner join, outer join etc).
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "Chris Kennedy" <co.uk> wrote in message
    news:google.com... 


    SriSamp Guest

  3. #3

    Default Re: What is a merge join?

    Here is an explanation from BOL

    Understanding Merge Joins
    The merge join requires that both inputs be sorted on the merge columns,
    which are defined by the equality (WHERE) clauses of the join predicate. The
    query optimizer typically scans an index, if one exists on the proper set of
    columns, or places a sort operator below the merge join. In rare cases,
    there may be multiple equality clauses, but the merge columns are taken from
    only some of the available equality clauses.
    Because each input is sorted, the Merge Join operator gets a row from each
    input and compares them. For example, for inner join operations, the rows
    are returned if they are equal. If they are not equal, whichever row has the
    lower value is discarded and another row is obtained from that input. This
    process repeats until all rows have been processed.
    The merge join operation may be either a regular or a many-to-many
    operation. A many-to-many merge join uses a temporary table to store rows.
    If there are duplicate values from each input, one of the inputs will have
    to rewind to the start of the duplicates as each duplicate from the other
    input is processed.
    If a residual predicate is present, all rows that satisfy the merge
    predicate will evaluate the residual predicate, and only those rows that
    satisfy it will be returned.
    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.


    You will also see in Query plans
    HASH JOIN and LOOP JOIN

    In Kalen's book she has a discussion on them (Inside SQL Server 2000)

    Merge Join 840-841, 909
    Hash Join 841-43,842,843,909
    Loop Join 909
    nested loop joins 840




    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    www.SQLDTS.com
    I support PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org
    Allan Guest

Similar Threads

  1. Full Outer Join, Merge Result
    By Whit in forum MySQL
    Replies: 5
    Last Post: February 18th, 09:45 PM
  2. Merge/Join multiple Paths
    By ebeard in forum Macromedia Freehand
    Replies: 12
    Last Post: March 11th, 04:01 AM
  3. Photoshop 7 Merge Linked vs. Merge Down
    By Seth_Thompson@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 0
    Last Post: February 25th, 08:14 PM
  4. Merge wont Merge Gradients?
    By Jason_Howard@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 1
    Last Post: February 13th, 07:52 PM
  5. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139