understanding outer join

[dennisbekkering]

Hello everybody,

I have to following query

SELECT DISTINCT A0.*
FROM Mail A0
INNER JOIN MailTo2Contact A1 ON A0.id=A1.mailid
INNER JOIN Contact A2 ON A1.contactId=A2.id
LEFT OUTER JOIN MailCc2Contact A3 ON A0.id=A3.mailid
LEFT OUTER JOIN Contact A4 ON A3.contactId=A4.id
LEFT OUTER JOIN MailBcc2Contact A5 ON A0.id=A5.mailid
LEFT OUTER JOIN Contact A6 ON A5.contactId=A6.id

WHERE ( A2.companyid = ? OR (A4.companyid = ?)
OR (A6.companyid = ?)) AND (A0.flag = 'sent') ORDER BY 6 DESC

it gives me all the mail (to,cc and bcc) from all contacts belonging to a certain company. When i make all the joins inner joins then i dont get any results. The to,cc&bcc joins are then AND-ed istead of OR-ed. I dont't understand why. Thanks in advance for reactions.

bye,
Dennis

 

[jimbopalmer]

Outer joins return nulls in the rows if the columns do not match, inner joins do not return anything if the columns do not match.

As an example, if you have to, CC and BCC but no contact info for the Certain Company, outer joins will return a row with contact null, but inner joins will not show any info for the Certain Company

Outer joins are needed when some table's information is optional, if it is required, inner joins are the ideal.

I tried to remain child-like, all I acheived was childish.

 

[dennisbekkering]

thank you for the explanation. Does that mean that the result of the first join is passed to the next. What is happening with the null rows , what good are they?

 

[PruSQLer]

It doesn't really return null rows in the results, the outer join just used the existence of NULL to not return data from the Outer Joined table.

So TableA Left Outer Joined to TableB on TableA.ID = TableB.ID
would return ALL rows in TableA and only those rows on TableB which match TableA.

Actually there is a use for the nulls - when you want to find what rows exist on TableA but not on TableB you would say:

Select *
From TableA Left outer join TableB on TableA.ID = TableB.ID

Where TableB.ID is null