title: sql query only displays rows that have a match in the join
body: <p>IN this SQL, it is only returning rows that had data in the col from the join table
  (subst_Instructions). But we want it to display the row from main table even if there is not data in the join. Now, its' because of the where SUBST_INSTRUCTIONS is not null presumably.
  but without that, it will not show the data from that column. is there a way to say where SUBST_INSTRUCTIONS is not null and null?</p>
  
  <pre><code>SELECT * FROM
  (
       SELECT ID_KEY, [BATCH] AS column1, [IMPORTDATE], [DATEBILLED], [RX], 
       [DATEDISPENSED], [DAYSUPPLY], [PAYTYPE], [NPI],
       [PHYSICIAN], [COST], [QUANTITY], [MEDICATION], A.[NDC], [PATIENTNAME],   
       [ROUTEOFADMIN], [INVOICECAT], [COPAY], [BRAND], [TIER], [SKILLLEVEL], 
       [STAT] STATUS, [LASTTASKDATE],SEQNO,B.[SUBST_INSTRUCTIONS], 
       ROW_NUMBER() OVER(PARTITION BY ID_KEY ORDER BY ID_KEY) rn 
     FROM [PBM].[T_CHARGES] A 
       LEFT OUTER JOIN [OGEN].[NDC_M_FORMULARY] B 
         ON A.[NDC] = B.[NDC]  
     WHERE [STAT] NOT IN (3, 4) 
       AND [TIER] &lt;&gt; 'T1'  
   ) a 
   WHERE SUBST_INSTRUCTIONS IS NOT NULL -- rn = 1
  </code></pre>