Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 2000

SQL Server 7.0: Nested-Loop Joins

By Alexander Chigrik


SQL Server 7.0 supports three types of join operations:

  • Nested-Loop joins
  • Merge joins
  • Hash joins
  • In this article I want to tell you about Nested-Loop joins, what kinds of Nested-Loop joins exist, and when SQL Server will choose this kind of join operation.

    If you join two tables, and SQL Server optimizer choose Nested-Loop join, then one table will be selected as outer table and the other as inner table. SQL Server will scan the outer table row by row and for each row in the outer table, it will scan the inner table, looking for matching rows.

    Nested-Loop join will be chosen if one of the tables is small and the other table has an index on the column that joins the tables.

    This is the example:

    USE pubs
    GO
    SET SHOWPLAN_TEXT ON
    GO
    SELECT a.au_id FROM authors a JOIN titleauthor b
       ON a.au_id = b.au_id
    GO
    SET SHOWPLAN_TEXT OFF
    GO
    

    These are the results:

    StmtText                                                                     
    ---------------------------------------------------------------------------- 
    SELECT a.au_id FROM authors a JOIN titleauthor b
       ON a.au_id = b.au_id
    
    (1 row(s) affected)
    
    StmtText                                                                                                            
    ------------------------------------------------------------------------------------------------------------------- 
      |--Nested Loops(Inner Join)
           |--Clustered Index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]))
           |--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [b]), SEEK:([b].[au_id]=[a].[au_id]) ORDERED)
    
    (3 row(s) affected)
    

    Because the query optimizer usually selects the best execution plan for a given select statement, it is not necessary to change the kind of join, but sometimes it can be useful. You can enforce the desirable join type with OPTION clause.

    This is the example to enforce Nested-Loop join:

    USE pubs
    GO
    SET SHOWPLAN_TEXT ON
    GO
    SELECT s.qty, st.stor_name FROM sales s JOIN stores st 
      ON s.stor_id=st.stor_id OPTION (LOOP JOIN)
    GO
    SET SHOWPLAN_TEXT OFF
    GO
    

    There are three variants of Nested-Loop join:

  • Naive Nested-loop join - a search that scans an entire table or index

  • Index Nested-loop join - a search that performs lookups in an index to fetch rows

  • Temporary index nested-loop join - a search that uses temporary index (index, which was created by query optimizer and was destroyed when the query was completed)


  • » See All Articles by Columnist Alexander Chigrik




    MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM