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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0