The Power of LEFT OUTER JOIN

In the last letter, I made a big mistake. I made a jump in technique that I should not have made. I didn’t explain when to use a LEFT JOIN. 

LEFT OUTER JOINS or LEFT JOINS for short are one of those very confusing things for many beginners. So much so I left it out of SAP Business One Essential training. However, it is a very important join that you really should know. 

So let’s take a look at this valuable part of SQL. I want to start with a bit of a side quest first. Let’s look at this query which gives information about a customer’s default bank and the Bank’s country. 

SELECT 
   T0.CardCode,
   T0.CardName,
   T0.BankCountry AS "Bank Country",
   T0.DflBankKey AS "Default Bank" 

FROM OCRD T0

WHERE 
   T0.CardType = 'C'

Run this, and you’ll see some blank spaces. 

There are two reasons those rows have blanks: SAP Business One’s terrible formatting for a zero value or a null. NULL does not mean zero. NULL means there is no data. The spot is indeed empty. I’ll replace BankCountr and DflBankKey with two CASE expressions to check for NULL. The operator IS NULL will do this. 

CASE WHEN T0.BankCountr IS NULL 

   THEN 'NULL' ELSE T0.BankCountr 

END as "Bank Country", 

CASE WHEN t0.dflBankKey IS NULL 

  THEN 'NULL' ELSE Format(t0.dflBankKey,'#') END 

AS "Default Bank"

Rerun the query. Now we can see the NULLs.  

I want the name of the country and the bank on my report, not the id as I have now. I’d assume I’d add two joins to my report.

FROM OCRD T0

INNER JOIN OCRY t2 ON t0.BankCountr = t2.code

INNER JOIN ODSC t1 ON t0.dflBankKey = t1.AbsEntry

When I Run this, I have fewer rows:

INNER JOIN finds rows in two tables with a field in that row the same on both tables. Inner joins require an equal value in both tables. If a row in OCRD.Bankcountry has the same values as a row from OCRY. Code, SQL adds a row with data from both to the result table.

What happened at Row 6 and 7 on OCRD? Those are NULL, and nothing matches on OCRY. That excludes ROws 6 and 7 in the result table.

If I want all the customers and be able to Join where I have relevant information, I need a join that will show all rows found and add the information to those that match. That’s what left joins do. WE call them left because you were to write the join on the same line.

FROM OCRD T0 LEFT JOIN ODSC t1 ON t0.dflBankKey = t1.AbsEntry

OCRD is to the left of ODSC. You’ll get all of the selected records from OCRD and the extra information necessary from ODSC. 

Try it by changing INNER to LEFT. 

LEFT JOIN ODSC t1 ON t0.dflBankKey = t1.AbsEntry

LEFT JOIN OCRY t2 ON t0.BANKCountr = t2.code

Once again, we have 14 rows.

I’ll add two more fields to the select clause to make this work

SELECT 
   T0.Cardcode,
   T0.CardName,
   CASE WHEN T0.BankCountr IS NULL 
      THEN 'NULL' ELSE T0.BankCountr 
   END as "Bank Country", 
   T2.Name,
   CASE WHEN t0.dflBankKey IS NULL 
      THEN 'NULL' ELSE Format(t0.dflBankKey,'#') 
   END AS "Default Bank", 
   T1.BankName

FROM OCRD T0
   LEFT JOIN ODSC t1 ON t0.dflBankKey = t1.AbsEntry
   LEFT JOIN OCRY t2 ON t0.BANKCountr = t2.code

WHERE 
   T0.CardType = 'C'

Running again, I get this

One more example, going back to a previous newsletter. Consider this Query for a sales order and its rows, with a pointer to Invoices:

SELECT  
   T0.DocNum,
   T0.DocDate,
   T1.LineNum + 1,
   T1.ItemCode,
   T1.TargetType,
   CASE  WHEN T1.TrgetEntry IS NULL THEN  'NULL' ELSE FORMAT(T1.TrgetEntry,'0') END as "Target Entry"
FROM ORDR T0
   INNER JOIN RDR1 T1 ON t0.docentry = t1.docentry
WHERE 
   t0.docdate >= '01/01/2020'

running ti gets me many rows.

Notice the document type. I have Delivery documents (15) INvoices (13) and Open sales order which have no target (-1). If I want a report with a column with a golden arrow to invoices, I’ll modify the Query to this, filtering for Invoices:

SELECT  
   T0.DocNum,
   T0.DocDate,
   T1.LineNum + 1,
   T1.ItemCode,
   T1.TargetType,
   CASE  WHEN T1.TrgetEntry IS NULL THEN  'NULL' ELSE FORMAT(T1.TrgetEntry,'0') END as "Invoice #",
   T2.DocEntry as "Invoice Link"
FROM ORDR T0
   INNER JOIN RDR1 T1 ON t0.docentry = t1.docentry
   INNER JOIN OINV T2 ON T1.TrgetEntry = T2.DocEntry AND T1.TargetType = 13
WHERE 
   t0.docdate >= '01/01/2020'

I’d get only the two Invoice rows connected to Sales order 1181. Everything else went through a delivery document or has no value yet. Here it is not just the null but a compound boolean expression that could be false.

Change that to a left join like this.

SELECT  
   T0.DocNum,
   T0.DocDate,
   T1.LineNum + 1,
   T1.ItemCode,
   T1.TargetType,
   CASE  WHEN T1.TrgetEntry IS NULL THEN  'NULL' ELSE FORMAT(T1.TrgetEntry,'0') END as "Invoice #",
   T2.DocEntry AS "Invoice Link"
FROM ORDR T0
   INNER JOIN RDR1 T1 ON t0.docentry = t1.docentry
   LEFT JOIN OINV T2 ON T1.TrgetEntry = T2.DocEntry AND T1.TargetType = 13
WHERE 
  t0.docdate >= '01/01/2020'

And you get

I can even add the Delivery link with a LEFT JOIN

SELECT  
   T0.DocNum,
   T0.DocDate,
   T1.LineNum + 1,
   T1.ItemCode,
   T1.TargetType,
   CASE  WHEN T1.TrgetEntry IS NULL THEN  'NULL' ELSE FORMAT(T1.TrgetEntry,'0') END as "Invoice #",
   T2.DocEntry AS "Invoice Link",
   T3.DocEntry AS "Delivery Doc Link"
FROM ORDR T0
   INNER JOIN RDR1 T1 ON t0.docentry = t1.docentry
   LEFT JOIN OINV T2 ON T1.TrgetEntry = T2.DocEntry AND T1.TargetType = 13
   LEFT JOIN ODLN T3 ON T1.TrgetEntry = T3.DocEntry AND T1.TargetType = 15
WHERE 
  t0.docdate >= '01/01/2020'

and get

Left Joins are handy when you want to see all rows of the first table, but there is no one-to-one correspondence in the second table of a join. There are plenty of examples, but these two should give you the idea.