#AcumaticaTnT LEFT JOIN vs. INNER JOIN

There are 5 main join types when joining tables together in SQL:

1. LEFT JOIN
2. RIGHT JOIN
3. INNER JOIN
4. FULL JOIN
5. CROSS JOIN

I personally pretty much always use either LEFT JOIN or INNER JOIN so I'm just going to talk about these 2 join types in this post.

But what's the difference between LEFT JOIN and INNER JOIN?

First, as a rule, I like to start with the table that is the most detailed, then I join to additional tables to get additional information about a record in the first table.

For example, if we're looking at Customers and we want to get the description of their default payment method, we would start with the Customers table, then join to the PaymentMethod table.

Let's start with this query to see a list of Customers and the ID of each customer's default payment method:

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID, C.DefPaymentMethodID
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2
ORDER BY B.AcctCD

Notice in the previous screenshot that there are 126 customers and 2 of the customers in the screenshot have an empty DefPaymentMethodID.

Let's make sure that those are the only 2 customers with an empty DefPaymentMethodID value with this query where we check for a NULL (empty) DefPaymentMethodID:

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID, C.DefPaymentMethodID
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
WHERE C.CompanyID=2 AND C.DefPaymentMethodID IS NULL
ORDER BY B.AcctCD

Yep, looks like there are only 2 customers with a NULL (empty) DefPaymentMethodID.

Let's check the first customer (ABARTENDE) to make sure that it really doesn't have a default payment method:

Yep, no default payment method.

Ok, now, back to LEFT JOIN vs. INNER JOIN.

If we do a LEFT JOIN to the PaymentMethod table so we can get the Payment Method description from the PaymentMethod.Descr field, then we'll still get all 126 customer records, but we'll get NULL (empty) values in the PaymentMethod.Descr field for customers that don't have a default payment method.

Note the results in the following query. There are still 126 records and those same 2 customers don't have values in the PaymentMethod.Descr field.

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID,
	C.DefPaymentMethodID, P.Descr
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
LEFT JOIN PaymentMethod P
	ON C.CompanyID=P.CompanyID
	AND C.DefPaymentMethodID=P.PaymentMethodID
WHERE C.CompanyID=2
ORDER BY B.AcctCD

But, if we change LEFT JOIN to INNER JOIN when joining to PaymentMethod like this:

SELECT C.CompanyID, B.AcctCD, B.AcctName,
	C.BAccountID, C.CustomerClassID,
	C.DefPaymentMethodID, P.Descr
FROM Customer C
LEFT JOIN BAccount B
	ON C.CompanyID=B.CompanyID
	AND C.BAccountID=B.BAccountID
INNER JOIN PaymentMethod P
	ON C.CompanyID=P.CompanyID
	AND C.DefPaymentMethodID=P.PaymentMethodID
WHERE C.CompanyID=2
ORDER BY B.AcctCD

Then we can see that only 124 records are returned and the 2 customers that don't have a default payment method aren't included in the results, that's why there are only 124 records (126 minus 2).

So, what's the difference between LEFT JOIN and INNER JOIN?

LEFT JOIN return all records from the first table, regardless of whether or not they have a match in the second table.

INNER JOIN only returns records that exist in both the first table and the second table.