Search arguments in a join

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  January 9, 2019 at 4:50 am

  #2018396

  The where in query 3 is applied to this query:
  select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b
  which returns these results:
  id_a    id_b    fname
1    1    b1
2    2    b2
3    3    b3
4    NULL    NULL
  This query will return what you were expecting:
  select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b and b.lname = 'B3'
where (b.lname = 'B3' or b.id_b is null )

• allinadazework

  SSCarpal Tunnel

  Points: 4507

  More actions

  January 9, 2019 at 5:15 am

  #2018399

  You've created a set of all the records in table temp_1
  left joined it to Temp_2 using the ID
  You've then applied a where clause to this set that filters this set to the ones where the lname in table 2 is B3 or the id is NULL
  The Where clause is applied to the resulting set so is removing records that do not match your where clause.
  In your first query you are not applying a where clause you are filtering in the join.

  This will return what you were expecting a bit like the first query.
  select a.id_a, b.id_b, b.fname
  from tmp_1 a
  left join tmp_2 b on a.id_a = b.id_b 
  and ( b.lname = 'B3' or b.id_b is null )