SQL join when there is a corresponding post or not

I'm trying to join a table that may or may not have a relevant post and want to see even those ones where there isn't. Short example:

Select * from customer cu join customerphone cp on cu.customer_id = cp.customer_id

This far no problems because I only want customers who have a phone number registered but how do I join an another table where not all the customers have a row and I want in end result also those without a row to be shown. Is this even possible to do or should I then afterwards just check whether there is rel开发者_开发百科evant data or not?

You use a left (outer) join. Example:

select c.CustomerId, p.PhoneNumber, cc.Complaint
from Customer c
inner join CustomerPhone p on p.CustomerId = c.CustomerId
left join CustomerComplaint cc on cc.CustomerId = c.CustomerId

The data that you get where there is no matching record will be null, i.e. where there is no complaint, cc.Complaint will return null.

Use a LEFT OUTER JOIN

Select * from customer cu left outer join customerphone cp on cu.customer_id = cp.customer_id

This will result in a NULL being shown when there is not a corresponding record.

If you want to provide a default value you can use ISNULL in TSQL or COALESCE in MySQL

You want to do an OUTER JOIN.

This is a good link: http://www.oracle-base.com/articles/9i/ANSIISOSQLSupport.php#outer_join

Hope that helps,

-Mark