How do I understand 'merge join without sort'?

  • Execution plan showed me merge join without sort.

    Join was FULL JOIN with no equality in ON clause.

    I can't understand this.

    SQL Version is "Microsoft SQL Server 2008 (RTM) - 10.0.1600.22"

    Recall script is as below.

    CREATE TABLE tblA ( aa INT )

    ;

    INSERT INTO tblA( aa )

    SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY object_id, column_id )

    FROM sys.all_columns

    ;

    CREATE TABLE tblB ( aa INT, bb INT )

    ;

    DECLARE @ii INT = 1;

    BEGIN TRAN

    WHILE @ii <= 50

    BEGIN

    INSERT INTO tblB( aa, bb )

    SELECT TOP 2000 @ii, ROW_NUMBER() OVER ( ORDER BY object_id, column_id )

    FROM sys.all_columns

    ;

    SET @ii = @ii + 1;

    END

    COMMIT TRAN

    SELECT tblA.aa, tblb.bb

    FROM tblA FULL JOIN tblB ON tblA.aa < tblB.aa

    ;

  • Take a look at the properties on the other operations. I'll bet you they're ordered operations. This is all good. An ordered seek or scan with a merge can be the fastest way to move large amounts of data within a query.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • It's an Ordered False operation. The thing is, that's actually a cross join. There's no join predicate at all. All rows qualify for that predicate so there's no ordering necessary, every row joins to every row.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • First of all, thank you Fritchey.

    I recheck execution plan but I can't find any kind of sorting operations.

    As you can see, table scan is used because there is no clustered index and sorting operator isn't be shown.

    Isn't it weird?

  • Not weird at all. There's no need to sort as all rows match all rows with that join you've specified. Look at the join columns in the merge join () = ()

    This is essentially what your query boils down to:

    SELECT tblA.aa, tblb.bb

    FROM tblA FULL OUTER JOIN tblB ON 1=1

    For a merge join, the two inputs need to be sorted on the join column, except there is no join column, so any order whatsoever will do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you GilaMonster

    You makes me clear.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply