You must Sign In to post a response.
  • Category: SQL Server

    Multi join execution

    How multi joins work in a sql query? What is precedence of join execution?
  • #765581
    Hello Arthi,

    Multi joins work to get data from more than one tables.

    For ex :

    Single Join :
    SELECT a.EmpID, b.Name FROM EmpMaster a
    INNER JOIN EmpDetails b
    ON a.ID = b.ID

    Multi Join :
    Method 1 :
    SELECT a.EmpID, b.Name, c.Designation FROM EmpMaster a
    INNER JOIN EmpDetails b
    ON a.ID = b.ID
    INNER JOIN EmpStatus c
    ON a.EmpID = b.EmpID

    Method 2 :
    SELECT main.EmpID, main.Name, c.Designation
    FROM
    (
    SELECT a.EmpID, b.Name FROM EmpMaster a
    INNER JOIN EmpDetails b
    ON a.ID = b.ID
    ) main
    INNER JOIN EmpStatus c
    ON a.EmpID = b.EmpID

    In above scenario of Method i am just executing first inner query and taking it's output to outer query and than joining with third table.

    Mostly this method is helpful when you are generating dynamic reports.

    Hope this will be helpful for you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #765583
    Hi Arti,

    If you want to join more than 2 tables then we are call it as multi join, i.e. either in inner join / outer join / any type of join.

    ex:

    select a.col1, b.col2 ,c.col3
    from table1 a,
    table2 b,
    table3 c
    where a.id=b.id and b.id=c.id


    This is the sample, you can build your query as you want.

    Hope this will helpful to you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #765592
    Initially, it might seem that every table that is joined to this query is joined to a previous table; the ON clause suggests this with its references to columns in previous tables.

    ON-clause-Multiple-Joins-Work-just-like-Single-Joins

    But this is not what actually happens in a multi-join query, and so looking at things in this way will lead to head-scratching.

    So, what does a multi-join query actually do? It actually does something very simple. It performs a series of incremental, single joins between two tables at a time (while this article refers only to tables for simplicity sake, joins can be between tables, views, table valued functions, CTEs, and derived table subqueries). Each single join produces a single derived table (DT) that is then joined to the next table and so on. Like this:

    multiple-joins-work-like-single-joins


    select *
    from HumanResources.Employee e
    INNER JOIN Person.COntact c
    ON c.ContactID = e.ContactID
    LEFT JOIN HumanResources.JobCandidate jc
    ON jc.EmployeeID = e.EmployeeID
    INNER JOIN Sales.SalesPerson sp
    ON sp.SalesPersonID = e.EmployeeID
    LEFT JOIN Sales.SalesOrderHeader soh
    ON soh.SalesPersonID = sp.SalesPersonID
    LEFT JOIN Sales.SalesTerritory st
    ON st.TerritoryID = sp.TerritoryID

    JOIN 1: Inner join between Employee and Contact resulting in a derived table, DT1. Because this is an inner join, rows in Employee are excluded if they don't match any rows in Contact, and vice-versa.

    JOIN 2: Outer join between DT1 and JobCandidate resulting in a derived table, DT2. Because this is a left outer join, all rows in DT1 are preserved.

    JOIN 3: Inner join between DT2 and SalesPerson resulting in a derived table, DT3. Because this is an inner join, rows in DT2 are excluded if they don't match any rows in SalesPerson, and vice-versa.

    JOIN 4: Outer join between DT3 and SalesOrderHeader resulting in a derived table, DT4. Because this is a left outer join, all rows in DT3 are preserved.

    JOIN 5: Outer join between DT4 and SalesTerritory resulting in a derived table, DT5. Because this is a left outer join, all rows in DT4 are preserved. DT5 is the final result of the query.

    So, what about that confusion arising from the ON clause? With this new way of looking at multiple-join queries, we can now see that the proper way to read an ON clause is not that it joins the new table to a single table that came before it in the query! The only join that does that is the first one; all subsequent ones join a new table to the derived table that is a result of all the joins before it. If an ON clause includes a table alias, that is only to identify the column properly to the query. Table aliases are only required when there is ambiguity—when two or more columns have the same name in the derived table that precedes the current join because they came from different tables.


Sign In to post your comments