Question

Please explain Left Outer Join and Right Outer Join. Show the difference with a small example.

Write a Left outer join query between AP.Vendors and AP.Invoices table and display a sample of recordset (Please don’t display all the records of the output)

Answer 1

1) Differences between Left outer join and Right outer join with an example

a) Left outer join: Essentially returns complete rows list of a table on left side of join operation. . When there is no matching for the row then result comprises of NULL in right side.

SELECT Teacher.TeacherName,
       TeacherCourse.Course_ID
FROM Teacher
LEFT OUTER JOIN TeacherCourse 
ON TeacherCourse.ErollNo = Teacher.ErollNo
ORDER BY TeacherCourse.Course_ID

b) Right Outer Join; Same as that of Left Outer Join operation, however right replaces left in the query.

SELECT Teacher.TeacherName,

TeacherCourse.Course_ID

FROM Teacher

RIGHT OUTER JOIN TeacherCourse

ON TeacherCourse.ErollNo = Teacher.ErollNo

ORDER BY TeacherCourse.Course_ID

2) Left outer join query between AP.Vendors and AP.Invoices table

SELECT AP.invoice_number, AP.vendorname

FROM AP.vendors LEFT OUTER JOIN AP.invoice

ON AP.vendors.vendor-id = AP.invoices.vendor-id

ORDER BY AP.invoice_number