Infering the Join Type

[techytechy]

Hi guys,

I know that I shud use Left outer if the requirement says that I need to get all the records of the left Table(matching or nonmatching). But when such requirement is not given, then how would one infer that Left outer shud be performed? Does the table structures or cordinality or anything else would give the clue for Join Type?

Thanks!

 

[bborissov]

Nom
ALL depends of the requirements of the query.
If there is NO such, then check the requirements of the report your want to give, and if that didn't helps I'll stay with INNER join, until somebody say that that is not expected result.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

If you know that the child table will not always be populated for a given parent record, then use a left join. For example suppose you had a customer table and an email table but not all customers provide an email (It is not required by your application), then you woudl always need to do a left join when joining to the email table unless you are specifically creating an email list to send out emails.

Another way to tell is to try it as an inner join and a left join, if the number of records returned is the same, then use an inner join, if it is not, you may need a left join, but it depends on what you are asking for. This also assumes you haven't done anything dumb like put a condition in the where clause for the table you are left joining to (which changes it to an inner join unless you are specifcally saying where myleftjointable.id is null which would return record inthe first table but not the second).

"NOTHING is more important in a database than integrity." ESquared