1

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;

enter image description here

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';

enter image description here

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

Reed Richards
  • 4,178
  • 8
  • 41
  • 55
Andrey
  • 1,629
  • 13
  • 37
  • 65
  • Possible duplicate of [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 May 25 '19 at 17:33
  • Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – philipxy May 25 '19 at 17:34
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. – philipxy May 25 '19 at 17:38

1 Answers1

11

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 ..

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107