Outer joins
Introduction to Oracle SQL
Sara Billen
Instructor
Inner Join
Returns matched rows only
Left Outer Join
Returns matched rows and left table's rows
Right Outer Join
Returns matched rows and right table's rows
Full Outer Join
Returns matched rows and both tables' rows
Track and invoice tables
- Every invoice refers to at least one track
- Not all tracks are in an invoice
Left outer join
SELECT t.TrackId, t.Name, i.InvoiceId, i.Quantity
FROM Track t LEFT OUTER JOIN InvoiceLine i
USING (TrackId)
Customer and employee tables
- Every customer has a support rep
- Not every employee is a support rep
Right outer join
SELECT c.CustomerId, c.SupportRepId, e.FirstName, e.LastName, e.Title
FROM Customer c RIGHT OUTER JOIN Employee e
ON c.SupportRepId = EmployeeId
Customer and employee tables
- Not every customer has a support rep
- Not every employee is a support rep
Full outer join
SELECT c.CustomerId, c.SupportRepId,e.FirstName, e.LastName, e.Title
FROM Customer c FULL OUTER JOIN Employee e
ON c.SupportRepId = EmployeeId
