0

The condition is I know the right table does not have all matching records with the left table.

but I am still not able to get data from left table with null from right table

select a.sales, b.profit 
from T1 a 
  left join T2 b on a.id = b.id
where b.category = 'office'
  and b.code = '245'

because of the where condition of right table, the right table does not have matching records, without where condition I got the records.

My question is will left table be affected with where condition of right table although using left join to retain the left table records.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Raja
  • 21
  • 4
  • If there are no matches in `T2`, then `b.code = '245'` cannot be true, because `b.code IS NULL`. Move the `WHERE` condition to the join condition – HoneyBadger Nov 17 '21 at 09:02
  • yes it will be affected. WHERE on the right table converts LEFT JOIN to INNER. I guess you can try to rewrite your query as SELECT A.SALES, B.PROFIT FROM T1 A LEFT JOIN (SELECT X.ID,X.PROFIT FROM T2 X WHERE X.CATEGORY='OFFICE' AND X.CODE='245')B ON A.ID=B.ID – Sergey Nov 17 '21 at 09:03
  • Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Nov 17 '21 at 10:00

1 Answers1

3

Your WHERE clause forces the query to only return rows which b.category and b.code match the required values and so are non-NULL; this effectively turns your JOIN condition into an INNER JOIN.

You want to put the filters in the join condition:

select a.sales,
       b.profit 
from   T1 a 
       left join T2 b
       on (   a.id = b.id
          AND b.category = 'office'
          AND b.code = '245')

Or to pre-filter T2 in a sub-query:

select a.sales,
       b.profit 
from   T1 a 
       left join (
         SELECT *
         FROM   T2
         WHERE  category = 'office'
         AND    code = '245'
       ) b
       on a.id = b.id
MT0
  • 143,790
  • 11
  • 59
  • 117