0

I'm wondering why this returns no values but if I place the "and r2.ref_nm = 'memberPrograStatusType" in the join clause it returns values. The 'memberPrograStatusType is null. 

`select mp.mbr_id
,r1.ref_desc as program_name
,r2.ref_desc as program_status
,mp.nom_dt
,mp.enrl_dt
,mp.end_dt
from icue.mbr_pgm mp
  left join icue.ref r1 on mp.pgm_typ_id = r1.ref_cd
  left join icue.ref r2 on mp.mbr_pgm_sts_typ_id = r2.ref_cd  
where '15-JAN-17' between mp.enrl_dt and nvl(mp.end_dt,sysdate)
    and mp.mbr_id = 46714641
    and r1.ref_nm = 'programType'
    and r2.ref_nm = 'memberPrograStatusType'
'
  • What do you mean by "The 'memberPrograStatusType is null"? You're using it as a constant string, not a variable. – Josh Eller Dec 04 '17 at 20:10
  • Whenever I want to filter a column from a left joined table I always put the filter with the JOIN clause. I don't see any reason to put it in the WHERE but that's just my opinion. – isaace Dec 04 '17 at 20:30

2 Answers2

0

The reason is because when you place a filter from a column in the left join in the where clause, you are turning it into an inner join.

However if you place the filter on the join clause then the filter applies only to the rows that have data in left join table.

isaace
  • 3,336
  • 1
  • 9
  • 22
0

The key here is order of operations

WHERE is applied after JOIN

If you put r2.ref_nm = 'x' into the JOIN predicate, it is applied against the right table, which is then joined to the left table. Because you are using a LEFT JOIN, this means that the rows joined from the right table are filtered on r2.ref_nm = 'x', but all rows from the left table are still preserved in the result set.

If you put r2.ref_nm = 'x' into the WHERE clause, then it is applied to the entire result set, after the joins have occured. This essentially makes your LEFT JOIN into an INNER JOIN if your filter is looking for anything other than NULL

TLDR: if you want to filter on the right table in a LEFT JOIN, you have to do so in the join predicate, otherwise you will lose all the NULL values that are created from the LEFT JOIN

Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • Thanks. Is it a better practice to place the filter in the JOIN rather than WHERE? –  Dec 04 '17 at 20:19
  • @smattiko Nah, I wouldn't say that. Basically you HAVE to filter in the join predicate on a `LEFT JOIN` so you don't lose your `NULL` rows. Beyond that, and a few other similar rules (`RIGHT JOIN` is the same, for example), it won't matter where you filter. It's probably easier to read in the `WHERE` clause, so I'd stick with that. – Aaron Dietz Dec 04 '17 at 20:21
  • @smattiko If you take a basic `INNER JOIN` and put a filter in the `JOIN` then the `WHERE` and compare the two, they will almost definitely execute exactly the same. – Aaron Dietz Dec 04 '17 at 20:23
  • So stick with WHERE unless I return no values? –  Dec 04 '17 at 20:24
  • @smattiko I think this quote sums it up "The on clause is used when the join is looking for matching rows. The where clause is used to filter rows after all the joining is done." Here is some more explanation, https://stackoverflow.com/questions/15706112/why-and-when-a-left-join-with-condition-in-where-clause-is-not-equivalent-to-the – Aaron Dietz Dec 04 '17 at 20:37
  • @smattiko - if you really need an outer join, and you have clauses that restrict rows from the "right" table (in a left outer join), they should be in the ON condition in most cases - otherwise just use an inner join, since that is what you end up with. The most common exception is when you left outer join tables A and B, and then you have a `where` condition like `b.col is NULL` - this will identify the rows in A that did **not** have a match in B. This is now superseded by `NOT EXISTS` conditions (anti-join), but that's when you will see things like that. –  Dec 04 '17 at 22:21