title: left join and where condition in joining condition
body: <p>I am using  Ingres 11.0  DB not sure if it will have same behavior on other DB engines ,but here is it</p>
  <pre><code> SELECT * 
   FROM table1 t1
   LEFT JOIN table2 t2
     ON t1.id = t2.fk_id 
   WHERE t1.code = 'CODE1' AND t2.id_number = 12174;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/VXDdF.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VXDdF.jpg" alt="enter image description here" /></a></p>
  <p>does not return all records from table 1 but  i am using left  join, which  should return aa records from T1 and only  maching  row from t2
  ,returns only 1 record</p>
  <p>if i am  moving one criteria from where clause to join condition its starting  return me exactly i am expecting</p>
  <pre><code>SELECT *
  FROM TABLE1 t1
  LEFT JOIN TABLE2 t2
    ON  t1.id = t2.fk_id 
    AND t2.id_number = 12174
  WHERE t1.code = 'CODE1'; 
  </code></pre>
  <p><a href="https://i.stack.imgur.com/TXbwo.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TXbwo.jpg" alt="enter image description here" /></a></p>
  <p>Question is why it doesn't work where all search  conditions in where clause  but working when I move t2.id_number from where to join conditions?</p>
  <p>I think i know know answer , because I eliminating  all possible variation in t2 on join step ,but  nowt sure</p>