Forcing to return null values for the left join

Question

I have three tables with the following structure

CUSTOMER
OwnerID    | ProfileID (PK)| Name    | Surname    | Address
Owner1       Profile1       John      Brown        some address
Owner1       Profile2       John      Brown        some address
Owner1       Profile3       John      Brown        some address
Owner2       Profile4       Charles   Snow         some address
Owner2       Profile5       Charles   Snow         some address
Owner2       Profile6       Charles   Snow         some address
Owner3       Profile7       Edward    Glork        some address
Owner3       Profile8       Edward    Glork        some address

LOANS
LoanID (PK)| ProfileID    | Amount    | Duration
Loan1       Profile1       5000        31
Loan2       Profile2       1000        31
Loan3       Profile4       4000        31
Loan4       Profile4       10000       31
Loan5       Profile5       600         31
Loan6       Profile6       800         31

PAYMENTS
PaymentID (PK)| LoanID    | Amount    | Date
Payment1        Loan1       100         01-12-2017
Payment2        Loan1       100         01-14-2017
Payment3        Loan2       200         02-12-2017
Payment4        Loan2       300         02-15-2017
Payment5        Loan1       175         03-04-2017
Payment6        Loan2       235         03-04-2017
Payment7        Loan1       345         04-05-2017

Here goes the SQL-statement

SELECT 
c.OwnerID as ID,
c.ProfileID as Prof,
c.Name,
c.Surname,
l.Amount as Credit_Amount,
p.Amount as Debit_Amount,
p.Date as Debit_Date
from CUSTOMER c
left join LOANS l on l.ProfileID = c.ProfileID
left join PAYMENTS p on p.LoanID = l.LoanID
where p.Amount > 199

What I expect as an output is

ID    | Prof    | Name    | Surname    | Credit_Amount    | Debit_Amount    |Debit_Date
Owner1  Profile1  John      Brown        5000               null             null
Owner1  Profile1  John      Brown        5000               null             null
Owner1  Profile2  John      Brown        1000               200             02-12-2017
Owner1  Profile2  John      Brown        1000               300             02-15-2017
Owner1  Profile1  John      Brown        5000               null             null
Owner1  Profile2  John      Brown        1000               235             03-04-2017
Owner1  Profile1  John      Brown        5000               345             04-05-2017
Owner2  Profile4  Charles   Snow         4000               null             null
Owner2  Profile4  Charles   Snow         10000              null             null
Owner2  Profile6  Charles   Snow         800                null             null
Owner3  Profile7  Edward    Glork         null               null             null
Owner3  Profile8  Edward    Glork         null               null             null

The output I get is

ID    | Prof    | Name    | Surname    | Credit_Amount    | Debit_Amount    |Debit_Date
Owner1  Profile2  John      Brown        1000               200             02-12-2017
Owner1  Profile2  John      Brown        1000               300             02-15-2017
Owner1  Profile2  John      Brown        1000               235             03-04-2017
Owner1  Profile1  John      Brown        5000               345             04-05-2017

So, what exactly do I misunderstand in the logic of SQL? Aint null values have to be filled rightwards when we use left join? How do I correct the SQL-statement so that I get all the records for the CUSTOMERS table and null values for the rest of the fields in case they don't match WHERE clause?

Your inclusion of a column from P in your where clause turns the left join to Payments into an inner join. Move that condition into your join clause with Payments. — SMor, Feb 26 '19 at 15:59

score 5 · Accepted Answer · answered Feb 26 '19 at 16:00

Move your where p.Amount > 199 to your ON:

SELECT c.OwnerID AS ID,
       c.ProfileID AS Prof,
       c.Name,
       c.Surname,
       l.Amount AS Credit_Amount,
       p.Amount AS Debit_Amount,
       p.Date AS Debit_Date
FROM CUSTOMER c
     LEFT JOIN LOANS l ON l.ProfileID = c.ProfileID
     LEFT JOIN PAYMENTS p ON p.LoanID = l.LoanID
                         AND p.Amount > 199;

When referencing a column from a table with a LEFT JOIN, unless you handle nulls then it turns the LEFT JOIN into an implicit INNER JOIN. Instead, it's therefore easier to handle clauses like that in the ON, as it filters the results there, but still data from the "right" table, is no appropriate rows are found.

Forcing to return null values for the left join

1 Answers1