none
What is difference between inner join and left outer join?

    Question

  • hi

    Any one explain difference between inner join and left outer join?

    Thanks

    CMK

     

     

    Friday, April 30, 2010 11:16 PM

Answers

  • Take a look here http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

    (Taken from this discussion http://forum.lessthandot.com/viewtopic.php?f=17&t=8658 )

    In addition, once you checked the first link, you may find my blog http://beyondrelational.com/blogs/naomi/archive/2010/03/22/why-left-join-doesn-t-bring-all-records-from-the-left-table.aspx helpful as well.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 12:04 AM
    Moderator
  • The quick answer is that when you use INNER join the result set contains only the rows (from both tables) that match the join conditions; the OUTER join returns all rows from the preserver table (in LEFT OUTER join it is the left side table) and only matching rows from the non-preserved table (the attributes for non-matched rows are NULL).

    For very detailed and excellent explanation of joins you can read Itzik's T-SQL Fundamentals book. The sample chapter provided is on joins:

    http://www.solidq.com/insidetsql/books/tsqlfund2008/sample_chapters.htm

    Here are more details on how outer joins are processed and common misunderstandings of outer joins:

    http://pratchev.blogspot.com/2009/06/outer-joins.html


    Plamen Ratchev
    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 2:01 AM
    Moderator
  • hi

    Any one explain difference between inner join and left outer join?

    Thanks

    CMK

     

     

    Simple to understand.

    Inner Join - Return the matched record (Like Intersection in Maths)

    Outer Join - Return the All record (specify with LEFT OUTER JOIN and RIGHT OUTER JOIN)

    where LEFT OUTER JOIN returns all all record from Left table and vice versa for RIGHT OUTER JOIN similar to UNION in maths.)


    Who Care! Who Regret!
    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 3:26 AM
  • Imagine the Sales Manager of AdventureWorks Cycles comes to you and asks for a sales report for 2004-02-03. So you write the following query and email the results to him/her as Excel attachement (53 row(s) affected) :

    USE AdventureWorks2008; 
    
    SELECT ProductName = Name, 
     SoldQty = SUM(OrderQty), 
     SoldAmount = CONVERT(DECIMAL(10,0),SUM(LineTotal)) 
    FROM Production.Product p 
     INNER JOIN Sales.SalesOrderDetail sod 
     ON p.ProductID = sod.ProductID 
     AND SalesOrderID IN (SELECT SalesOrderID 
       FROM Sales.SalesOrderHeader soh 
       WHERE OrderDate = '20040203') 
    WHERE ListPrice > 0.0 
    GROUP BY Name 
    ORDER BY SoldAmount DESC, 
     ProductName 
    /*
    ProductName	SoldQty	SoldAmount
    Mountain-200 Silver, 46	5	11600
    Mountain-200 Silver, 42	3	6960
    Touring-1000 Yellow, 50	2	4768
    .....
    Road Tire Tube	4	16
    Racing Socks, L	1	9
    Racing Socks, M	1	9
    */

    You get an email back, he/she wants to see all products with a ListPrice > 0, even if there were no sale on that day. So you change the INNER JOIN to a LEFT JOIN to include all entries from the Production.Product table with WHERE filter predicate ListPrice > 0.0 (304 row(s) affected):

     

    USE AdventureWorks2008; 
    
    SELECT ProductName = Name, 
     SoldQty = SUM(OrderQty), 
     SoldAmount = CONVERT(DECIMAL(10,0),SUM(LineTotal)) 
    FROM Production.Product p 
     LEFT JOIN Sales.SalesOrderDetail sod 
     ON p.ProductID = sod.ProductID
     AND SalesOrderID IN (SELECT SalesOrderID
     FROM Sales.SalesOrderHeader soh 
     WHERE OrderDate = '20040203')
     WHERE ListPrice > 0.0 
    
    GROUP BY Name 
    ORDER BY SoldAmount DESC, ProductName 
    /*
    ProductName	SoldQty	SoldAmount
    Mountain-200 Silver, 46	5	11600
    Mountain-200 Silver, 42	3	6960
    Touring-1000 Yellow, 50	2	4768
    .....
    Touring Tire Tube	4	20
    Road Tire Tube	4	16
    Racing Socks, L	1	9
    Racing Socks, M	1	9
    All-Purpose Bike Stand	NULL	NULL
    Cable Lock	NULL	NULL
    Chain	NULL	NULL
    Classic Vest, L	NULL	NULL
    .....
    */

    The report looks ugly with all those NULLs so you use the ISNULL function to replace NULL with '0'.

    USE AdventureWorks2008; 
    
    SELECT  ProductName = Name, 
         SoldQty = ISNULL(SUM(OrderQty),0), 
         SoldAmount = ISNULL(CONVERT(DECIMAL(10,0),SUM(LineTotal)),0) 
    FROM   Production.Product p 
         LEFT JOIN Sales.SalesOrderDetail sod 
          ON p.ProductID = sod.ProductID
         AND SalesOrderID IN (SELECT SalesOrderID
         FROM Sales.SalesOrderHeader soh 
          WHERE OrderDate = '20040203')
     WHERE ListPrice > 0.0 
    
    GROUP BY Name 
    ORDER BY SoldAmount DESC, ProductName 
    /*
    ProductName	SoldQty	SoldAmount
    Mountain-200 Silver, 46	5	11600
    Mountain-200 Silver, 42	3	6960
    Touring-1000 Yellow, 50	2	4768
    Touring-1000 Yellow, 54	2	4768
    
    LL Road Tire	1	21
    Touring Tire Tube	4	20
    Road Tire Tube	4	16
    Racing Socks, L	1	9
    Racing Socks, M	1	9
    All-Purpose Bike Stand	0	0
    Cable Lock	0	0
    Chain	0	0
    Classic Vest, L	0	0
    Classic Vest, M	0	0
    Front Brakes	0	0
    Front Derailleur	0	0
    Full-Finger Gloves, L	0	0
    .....
    */

    You copy and paste the results into an Excel worksheet and email it back to the Sales Manager.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com

    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 5:09 AM
    Moderator
  • Hi CMK

    Please check this link I think you clear your doubt about joins

    http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

    And for difference between them Please check this

    http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join

     

    Thanks

    Rathod

     

    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 10:05 AM

All replies

  • Take a look here http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

    (Taken from this discussion http://forum.lessthandot.com/viewtopic.php?f=17&t=8658 )

    In addition, once you checked the first link, you may find my blog http://beyondrelational.com/blogs/naomi/archive/2010/03/22/why-left-join-doesn-t-bring-all-records-from-the-left-table.aspx helpful as well.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 12:04 AM
    Moderator
  • The quick answer is that when you use INNER join the result set contains only the rows (from both tables) that match the join conditions; the OUTER join returns all rows from the preserver table (in LEFT OUTER join it is the left side table) and only matching rows from the non-preserved table (the attributes for non-matched rows are NULL).

    For very detailed and excellent explanation of joins you can read Itzik's T-SQL Fundamentals book. The sample chapter provided is on joins:

    http://www.solidq.com/insidetsql/books/tsqlfund2008/sample_chapters.htm

    Here are more details on how outer joins are processed and common misunderstandings of outer joins:

    http://pratchev.blogspot.com/2009/06/outer-joins.html


    Plamen Ratchev
    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 2:01 AM
    Moderator
  • hi

    Any one explain difference between inner join and left outer join?

    Thanks

    CMK

     

     

    Simple to understand.

    Inner Join - Return the matched record (Like Intersection in Maths)

    Outer Join - Return the All record (specify with LEFT OUTER JOIN and RIGHT OUTER JOIN)

    where LEFT OUTER JOIN returns all all record from Left table and vice versa for RIGHT OUTER JOIN similar to UNION in maths.)


    Who Care! Who Regret!
    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 3:26 AM
  • Imagine the Sales Manager of AdventureWorks Cycles comes to you and asks for a sales report for 2004-02-03. So you write the following query and email the results to him/her as Excel attachement (53 row(s) affected) :

    USE AdventureWorks2008; 
    
    SELECT ProductName = Name, 
     SoldQty = SUM(OrderQty), 
     SoldAmount = CONVERT(DECIMAL(10,0),SUM(LineTotal)) 
    FROM Production.Product p 
     INNER JOIN Sales.SalesOrderDetail sod 
     ON p.ProductID = sod.ProductID 
     AND SalesOrderID IN (SELECT SalesOrderID 
       FROM Sales.SalesOrderHeader soh 
       WHERE OrderDate = '20040203') 
    WHERE ListPrice > 0.0 
    GROUP BY Name 
    ORDER BY SoldAmount DESC, 
     ProductName 
    /*
    ProductName	SoldQty	SoldAmount
    Mountain-200 Silver, 46	5	11600
    Mountain-200 Silver, 42	3	6960
    Touring-1000 Yellow, 50	2	4768
    .....
    Road Tire Tube	4	16
    Racing Socks, L	1	9
    Racing Socks, M	1	9
    */

    You get an email back, he/she wants to see all products with a ListPrice > 0, even if there were no sale on that day. So you change the INNER JOIN to a LEFT JOIN to include all entries from the Production.Product table with WHERE filter predicate ListPrice > 0.0 (304 row(s) affected):

     

    USE AdventureWorks2008; 
    
    SELECT ProductName = Name, 
     SoldQty = SUM(OrderQty), 
     SoldAmount = CONVERT(DECIMAL(10,0),SUM(LineTotal)) 
    FROM Production.Product p 
     LEFT JOIN Sales.SalesOrderDetail sod 
     ON p.ProductID = sod.ProductID
     AND SalesOrderID IN (SELECT SalesOrderID
     FROM Sales.SalesOrderHeader soh 
     WHERE OrderDate = '20040203')
     WHERE ListPrice > 0.0 
    
    GROUP BY Name 
    ORDER BY SoldAmount DESC, ProductName 
    /*
    ProductName	SoldQty	SoldAmount
    Mountain-200 Silver, 46	5	11600
    Mountain-200 Silver, 42	3	6960
    Touring-1000 Yellow, 50	2	4768
    .....
    Touring Tire Tube	4	20
    Road Tire Tube	4	16
    Racing Socks, L	1	9
    Racing Socks, M	1	9
    All-Purpose Bike Stand	NULL	NULL
    Cable Lock	NULL	NULL
    Chain	NULL	NULL
    Classic Vest, L	NULL	NULL
    .....
    */

    The report looks ugly with all those NULLs so you use the ISNULL function to replace NULL with '0'.

    USE AdventureWorks2008; 
    
    SELECT  ProductName = Name, 
         SoldQty = ISNULL(SUM(OrderQty),0), 
         SoldAmount = ISNULL(CONVERT(DECIMAL(10,0),SUM(LineTotal)),0) 
    FROM   Production.Product p 
         LEFT JOIN Sales.SalesOrderDetail sod 
          ON p.ProductID = sod.ProductID
         AND SalesOrderID IN (SELECT SalesOrderID
         FROM Sales.SalesOrderHeader soh 
          WHERE OrderDate = '20040203')
     WHERE ListPrice > 0.0 
    
    GROUP BY Name 
    ORDER BY SoldAmount DESC, ProductName 
    /*
    ProductName	SoldQty	SoldAmount
    Mountain-200 Silver, 46	5	11600
    Mountain-200 Silver, 42	3	6960
    Touring-1000 Yellow, 50	2	4768
    Touring-1000 Yellow, 54	2	4768
    
    LL Road Tire	1	21
    Touring Tire Tube	4	20
    Road Tire Tube	4	16
    Racing Socks, L	1	9
    Racing Socks, M	1	9
    All-Purpose Bike Stand	0	0
    Cable Lock	0	0
    Chain	0	0
    Classic Vest, L	0	0
    Classic Vest, M	0	0
    Front Brakes	0	0
    Front Derailleur	0	0
    Full-Finger Gloves, L	0	0
    .....
    */

    You copy and paste the results into an Excel worksheet and email it back to the Sales Manager.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com

    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 5:09 AM
    Moderator
  • Hi CMK

    Please check this link I think you clear your doubt about joins

    http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

    And for difference between them Please check this

    http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join

     

    Thanks

    Rathod

     

    • Marked as answer by KJian_ Thursday, May 06, 2010 9:59 AM
    Saturday, May 01, 2010 10:05 AM