can you tell me simple real-world examples for (self,left outer,right outer,full outer) join?
I tried to answer the question with "Yes" but it said my answer was too short. You were kinder than I was.
HLGEM
2010-04-22 17:08:21
With all due respect to Jeff, this is an illustration of `UNION`, `INTERSECT` and `EXCEPT` rather than that of joins.
Quassnoi
2010-04-22 17:25:09
I meant some typical real world(use cases) where this kind of join is explicitly needed.
2010-04-22 17:30:58
A:
outer join cases
usually join is done to combine info from many tables.Suppose we have employee table and department table. We can join using deptid which will be a common column in both tables and get employee name and dept location in the combined result. Now this join might omit some rows from both tables based on the join condition. Now In all cases when we need remaining rows of either(or both) of the two tables, we can use outer join.I guess my question is not very relevant(on asking real world examples on outer join),knowing what outer join does is enough.
for example here: do a left outer join to get employee names (whatever be the case) and their department location (only if they are working in a department).
self join case?
Think of queries involving self referencing relations/entities.
A manager is always an employee. An employee can be a manager.