null forkey ^ unmatched prikey

–simple illustration:
emplyee {employee_id, department_id, age, salary…}
department {department_id, size, average age…}

null-forkey: employee without department_id
unmatched prikey: department without employee

— null forkey — perhaps not a case for outer join

“null in forkey column”. referential integrity violated.

http://sqlzoo.net/4.htm

Q 2a: One of the MSP (member of parliment) has no party (null forkey, violating referential integrity). How do you include him in a member-and-party listing?

A: select msp.name, party.name from msp, party where msp.party=party.code
union
select msp.name, null from msp where
msp.party is null

Neither makes sense:
wrong: msp.party=party.code(+) — no effect. How can party.code (prikey) be a SUBSET of msp.party (forkey)? referential integrity violated

wrong: msp.party(+)=party.code — showing empty parties

— unmatched prikey — #1 common scenario for outer join

“forkey = subset@prikey”

Heart of every outer join is a subset relationship. If the 2 join-columns are prikey-forkey, then the smaller set has to be the forkey — referential integrity. An outer join will show ALL the rows from the prikey side, whether or not there’s a matching row.


where employee.employee_id = bonus.employee_id(+) — “+” on the forkey side

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s