Inner and Outer Joins 1

[mfulton]

Hi, all I have a basic understanding of Crystal Reports, but haven't mastered the logic of inner and outer joins. Can someone explain the difference and when you might use each? I may be asked such question in an upcoming interview and that's one thing I'm struggling with.

Thank you!

 

[checkai]

Example:

1 Table has Employees E
1 Table has Timesheets T

select e.name, sum(t.hours) from employee e
inner join Timesheet t on e.eid = t.eid
group by e.name

With an inner join you will get all employees that have a timesheet record.

select e.name, sum(t.hours) from employee e
left outer join Timesheet t on e.eid = t.eid
group by e.name

With a left outer join you will get all employees regardless of them having a timesheet or not. the sum will be a null value.

HOPE THIS HELPS!!

 

[synapsevampire]

Here's a reasonable explanation of joins (inner, outer, full) and the difference between join semantics of old as compared to the newer ANSI 92 form.

http://www.nyoug.org/200212king.pdf

This is based on Oracle, but the theory is applicable across the board.

-k

 

[mfulton]

Thanks, you guys I always find help at tek-tips!

Michelle