SQL Right Joins vs Left Joins

Question

Given that the total number of rows returned by this query below must equal the number of rows in the Invoices table

SELECT VendorName, InvoiceNumber
FROM Invoices
LEFT JOIN Vendors ON Invoices.VendorsID = Vendors.VendorID

Why doesn't the total number of rows returned by this query equal the number of rows in the Vendors table when you replace LEFT keyword with RIGHT keyword?

Answer 1

Your vendors table is the dependent table in a 1-to-Many relationship, so when you execute a RIGHT join on Invoices and Vendors, you will get 1 row for every Invoice + Vendor combination in the relationship, plus 1 row for every Vendor that does not have an invoice record in the Invoices table.

So, Let's say you have three vendors, and among them there are three invoices.

Vendor 1 has 2 invoices
Vendor 2 has 1 invoice
Vendor 3 has 0 invoices

With this data, a RIGHT JOIN will return 4 rows: two rows for Vendor 1, one row for Vendor 2, and 1 row for Vendor 3.

This fiddle provides an example of what I've described above.

Answer 2

The simple answer is because it would mean every record on the "right" side (in this case Vendors) would be represented in the results.

So if you have a vendor which does not have any invoices, it will show in the RIGHT JOIN, but will not show in the LEFT JOIN (because there is no match on the "left" side table).

Answer 3

The right join ensures that all the rows from the right table are present in the results.

But if there are 3 invoices for the same vendor than you still get 3 rows in the result set, not 1. That's how joins work :)