Retrieving All records - Inner join

Question

Hi, I'm a bit of a database novice, so pardon my naivety here. I have following sql statement:

SELECT DISTINCT  dbo.tb_user.familyName,
dbo.user_email.email FROM dbo.tb_user  
INNER JOIN dbo.user_email ON (dbo.tb_user.id = dbo.user_email.userID)

This returns records where an email address exists.

What I would like to do is retrieve all the records so I can see which users do and do not have email address/es recorded in the db.

Can this be achieved?

Any hints/help much appreciated.

Answer 1

You need to use a left outer join..

SELECT DISTINCT  dbo.tb_user.familyName,dbo.user_email.email 
FROM dbo.tb_user  LEFT OUTER JOIN dbo.user_email 
ON (dbo.tb_user.id = dbo.user_email.userID)

Answer 2

what you want is called an "outer join". In an outer join the order the tables are listed matters, unlike the inner join you have listed. The second one listed is the outer one and will show NULL those cases where there is no match (at least in MySQL, the only place I've used outer joins so far.)

SELECT DISTINCT dbo.tb_user.familyName, dbo.user_email.email FROM dbo.tb_user LEFT OUTER JOIN dbo.user_email ON (dbo.tb_user.id = dbo.user_email.userID)