Post reply

How do real programmers do an outer join

  • February 21, 2006 at 5:35 pm

    #90836

    table Address                                     Table Customers

    A_ID , CustomerID,   Street1                 CustomerID, LastName

    1102     9001        1 Lovers Lane           9001      Spencer

                                                           9002      Smith

     

                

    So a join by default will be inner (inner join) returning only:

    select Customers.LastName, Address.Street1

    from customers c JOIN address A

    on c.CustomerID = A.customerID

    resluts:

    Spencer, 1 Lovers Lane

    ----------------------------------------------------------------

    but how do i make it return the Customers LastName even

    if the customer has no address record?

    I am trying to learn to use outer join.

     My books are just confusing me more!

  • February 21, 2006 at 5:46 pm

    #622480

    A LEFT JOIN will produce all the records from the "left" table and only the records that match from the "right" table.

    Example:

    select c.LastName, a.Street1

    from Customers c LEFT JOIN Address a

    on c.CustomerID=a.CustomerID

     

    HTH

    elliott

  • February 21, 2006 at 5:50 pm

    #622483

    supper thanks!

  • February 21, 2006 at 6:04 pm

    #622484

    Say you have two tables, TableR and TableL, then the OUTER join can be expressed in four ways, two of which are as follows:

    TableL RIGHT OUTER JOIN TableR

    OR

    TableL LEFT OUTER JOIN TableR

    Note that in the two examples above, TableL is written LEFT of the [OUTER JOIN] statement and TableR is written RIGHT of the [OUTER JOIN] statement.

    So, if you want to see ALL the data from TableR then use the RIGHT OUTER JOIN (first example). Alternatively, if you want to see ALL the data from TableL then use the LEFT OUTER JOIN (second example).

    Hope this is not confusing you even further!

  • February 21, 2006 at 7:12 pm

    #622491

    perfect, so a left join and left outer join is the same thing ie, outer is implied when you just type left join?

  • February 21, 2006 at 11:03 pm

    #622518

    Yes, that is correct.

  • February 23, 2006 at 5:45 am

    #622876

    I would recommend always to use OUTER for clarity. As a general rule, never abbreviate commands even when the parser allows it. You never know who will have to maintain your code in the future.

  • February 23, 2006 at 10:39 am

    #623012

    Also, don't forget about FULL OUTER JOIN.  A discussion on outer joins would not be complete without that. 

    - Paul

    http://paulpaivasql.blogspot.com/

  • February 24, 2006 at 11:39 am

    #623295

    thanks all for your help!!!

Viewing 9 posts - 1 through 8 (of 8 total)

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