Return results from SQL JOIN even if criteria not met in all tables

Question

New to SQL. Building my first contact manager project. Current schema can be seen below.

Issue:

• I'm working on a view that shows all contact types (phone, email, socials, address) for a given contact.
• I put together the following attempt, but it only returns results if the contactGUID is in every contact_type table.

--Problem: Only returns records if contactGUID is present in every contact_type table
SELECT
c.contGUID, c.contType, 
i.indTitle, i.indFirstName, i.indMidName, i.indLastName, 
e.emailAddress, e.emailDomain,
p.phnCountryCode, p.phnAreaCode, p.phnNumber,
s.socURL, s.socHandle,
a.addrCountry, a.addrCountryCode, a.addrCountrySubdivision, a.addrCountrySubName, a.addrSecondarySubdiv, a.addrMunicipality, a.addrMuniSubdivision, a.addrStreetNumber, a.addrStreetName, a.addrPostalCode, a.addrLatitude, a.addrLongitude
FROM
CONTACT c
JOIN INDIVIDUALS i
ON c.contGUID = i.indGUID
JOIN CONTACT_EMAIL ce
ON c.contGUID = ce.contactGUID
JOIN EMAILS e
ON ce.emailGUID = e.emailGUID
JOIN CONTACT_PHONE cp
ON c.contGUID = cp.contactGUID
JOIN PHONES p
ON cp.phnGUID = p.phnGUID
JOIN CONTACT_SOCIAL cs
ON c.contGUID = cs.contactGUID
JOIN SOCIALS s
ON cs.socGUID = s.socGUID
JOIN CONTACT_PHYS_ADDRESS cpa
ON c.contGUID = cpa.contactGUID
JOIN PHYS_ADDRESSES a
ON cpa.addrGUID = a.addrGUID

Question:

• How do I return all other contact types (email, social, address) if say, CONTACT_PHONE record is not present for a given contactGUID?

Answer 1

As Akina mentioned, you need to use an OUTER JOIN, specifically LEFT OUTER JOIN or more simply written as LEFT JOIN to take all the records from the left-hand side of the join clause, join where they match the right-hand side of the clause, and just NULL out the fields where they don't match in the right-hand side. This tutorial from W3Schools gives a brief intro with visuals to the different join types. Here's another article with further information on different join types. (Note these articles are database-agnostic and there may be other available join types for your specific system as well, but these are generally all one ever needs.)

What you were previously doing by just specifying JOIN is using the shorthand syntax for the INNER JOIN clause which only returns rows that match on both sides of the join clause.