Is it necessary to write OUTER JOINs multiple levels deep?-CodePudding

The database I'm working against has a top level table for Customers that then has child tables for related customer information, things like: addresses, phone numbers, emails etc. For some reason the way they designed the schema was to have many-to-many's for all these relationships that they've resolved with junction tables, for example, for address information:

CustomerId	CustomerName
1	Freedy Krueger
2	Jason Voorhees
3	Michael Myers

CustomerId	AddressId
1	10
1	11
3	30

AddressId	Address1
10	123 Elm St, Springwood, OH
11	456 Elm St, Springwood, OH
30	666 Main St, Haddonfield, IL

If I want to join Customer to all its children while still including records that do not have a child (e.g. CustomerId=2 has no address in this example) is it sufficient to LEFT OUTER JOIN on CustomerAddress? Or do I need to continue the LEFT OUTER JOINS as deep as the hierarchy goes (in this case also having a LEFT OUTER JOIN from CustomerAddress to Address as well)?

Hope that makes sense, thank you.

CodePudding user response：

SELECT
    CA.CUSTOMERID, CA.ADDRESSID
FROM CUSTOMERADDRESS AS CA
LEFT OUTER JOIN ADDRESS AS A
    ON CA.ADDRESSID = A.ADDRESSID
WHERE A.ADDRESSID IS NULL

This should tell you if there are any misses. If you don't have any I would assume moving forward you would be safe with the inner join from customer address to address.