left join and where condition in joining condition

I am using Ingres 11.0 DB not sure if it will have same behavior on other DB engines ,but here is it

 SELECT * 
 FROM table1 t1
 LEFT JOIN table2 t2
   ON t1.id = t2.fk_id 
 WHERE t1.code = 'CODE1' AND t2.id_number = 12174;

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

if i am moving one criteria from where clause to join condition its starting return me exactly i am expecting

SELECT *
FROM TABLE1 t1
LEFT JOIN TABLE2 t2
  ON  t1.id = t2.fk_id 
  AND t2.id_number = 12174
WHERE t1.code = 'CODE1'; 

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?

I think i know know answer , because I eliminating all possible variation in t2 on join step ,but nowt sure

Answer

You should not use column related to left table in where condition (this work as a INNER JOIN) move the condition for left join in the related ON clause

 select *  
 FROM   table1 t1
 left join table2 t2
          ON t1.id = t2.fk_id AND t2.id_number = 12174
 WHERE  t1.code = 'CODE1' ;

The where condition is the equivalent part of the INNER JOIN clause this is the reason that you have this behavior..

adding the condition to the on clause mean that also the added condition work as an outer join ..