How to apply left join for blank or null value of first table?

I have a table as TABLE 1

BankId    |CompanyId| 23        | 2       | 24        | 2       | 31        | 2       | NULL      | 2       | 83        | 1       |           | 2       |            | 2       |  29        | 7       |  

I have a table as TABLE 2

   BankId      |CompanyId|AccountId|         23        | 2       | 21         24        | 4       | 22         31        | 2       | 23         45        | 2       | 24         83        | 1       |         12        | 2       | 20         43        | 2       | 2         29        | 7       |  

Now here bankid and company id common among the two tables. Now I want to fetch all records from the first table based on company id and apply left join. But the problem is if i apply left join then it skips entry from the first table whose bankid is null.

Please tell me what should be the left join query ?

Sql query

select table1.*,table2.AccountId from table1 LEFT JOIN table2   ON table1.Bankld=table2.BankId WHERE table1.CompanyId=table2.CompanyId    AND table1.CompanyId=2; 
Add Comment
3 Answer(s)

WHERE table1.CompanyId=table2.CompanyId turns the result into an Inner Join, logically the ON is processed first and then the WHERE filter.

The rule of thumb is: Conditions on the Outer Table are placed in WHERE, conditions on the Inner Table are placed in ON:

select table1.*,table2.AccountId from table1 LEFT JOIN table2   ON table1.Bankld=table2.BankId  AND table1.CompanyId=table2.CompanyId -- condition on Inner Table WHERE table1.CompanyId=2;              -- condition on Outer Table 
Answered on September 1, 2020.
Add Comment

is this your desired output for provided source data.

output

bankid|companyid|accountid| ------|---------|---------| 23    |2        |21       | 31    |2        |23       | 24    |2        |         | NULL  |2        |         |       |2        |         |       |2        |         | 

Query

select table1.*,table2.accountid  from table1 LEFT JOIN table2  ON table1.bankid =table2.bankid and table1.companyid =table2.companyid WHERE  table1.companyid =2; 
Answered on September 1, 2020.
Add Comment

join is broken when using table1 LEFT JOIN table2 ON table1.Bankld=table2.BankId Because use column NULL in Join

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.